Views

Phreeze supports the use of database views.

Example: New messages view & count

1. We will create the tables that will hold the messages

CREATE TABLE IF NOT EXISTS `tb_messages` (
  `message_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `message_from` bigint(20) unsigned NOT NULL,
  `message_to` bigint(20) unsigned NOT NULL,
  `message_title` varchar(255) NOT NULL DEFAULT '',
  `message_content` longtext NOT NULL DEFAULT '',
  `message_date` timestamp,
  PRIMARY KEY (`message_id`),
  CONSTRAINT `sender` FOREIGN KEY (`message_from`) REFERENCES `tb_accounts` (`account_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `receiver` FOREIGN KEY (`message_to`) REFERENCES `tb_accounts` (`account_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Messages that are viewed table

Every time that a user opens the SingleView of the message, you should add or update the following table:

CREATE TABLE IF NOT EXISTS `tb_messageviewed` (
  `mv_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `message_id` bigint(20) unsigned NOT NULL,
  `account_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`mv_id`),
  CONSTRAINT `mv_message` FOREIGN KEY (`message_id`) REFERENCES `tb_messages` (`message_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `mv_account` FOREIGN KEY (`account_id`) REFERENCES `tb_accounts` (`account_id`) ON DELETE CASCADE ON UPDATE CASCADE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. Now that we have our tables set up, we can create some Views

The following View show you all the messages that aren't in table 'tb_messageviewed'

CREATE OR REPLACE VIEW `view_new_msg` AS
  SELECT *
  FROM tb_messages t1
    WHERE NOT EXISTS (
    SELECT * 
    FROM tb_messageviewed t2
    WHERE t1.message_id LIKE t2.message_id ) + '%'

4. Let's create another View, that uses the last View to show the new message count.

The following View show you the count of messages per account_id table 'tb_messageviewed'

CREATE OR REPLACE VIEW `view_new_msg_total` AS
  SELECT message_to, COUNT(message_id) message_total
  FROM `view_new_msg`    
  GROUP BY message_to
  ORDER BY message_total DESC