Multiple reporters

[xtrasmal]

I have a page where all posts are shown and I have a page that should only show posts that are unviewed by the currentUser. All is handled by storing postId and userId into a database table called PostUser.
Can I have multiple reporters?

[jasonhinkle]

You sure can have as many reporters as you need. You just have to write the support code to go along with it.

Another option is you can create a MySQL "view" which Phreeze treats about the same as a table, but is read-only. If you haven't used views before, basically they are just a query that you write and MySQL saves it and treats it kinda like a table. The main advantage to using a view is that Phreeze Builder will generate the plumbing code for you.

[xtrasmal]

That's nice. I will read up on Views.

[xtrasmal]

Ok I have been playing around with MySQl and tried to get this View thing in my head.
These are the tables for the internal messaging system

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;

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;

These are the Views to show latest messages and the total new messages for the currentUser

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 ) + '%'

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

Will Phreeze understand the way I created the views?

[xtrasmal]

Yes it works really well

[jasonhinkle]

awesome!