Statistical query question

Hi all,

Is there a way, using relational active record, to get the number of posts in each forum? My sql is below…




CREATE TABLE `forum_forums` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `name` varchar(256) NOT NULL,

 `description` text NOT NULL,

 `sort` int(11) DEFAULT NULL,

 `cat_id` int(11) NOT NULL,

 `create_time` datetime DEFAULT NULL,

 `update_time` datetime DEFAULT NULL,

 PRIMARY KEY (`id`),

 KEY `FK_category` (`cat_id`),

 CONSTRAINT `FK_category` FOREIGN KEY (`cat_id`) REFERENCES `forum_categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1





CREATE TABLE `forum_topics` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `name` varchar(256) DEFAULT NULL,

 `forum_id` int(11) NOT NULL,

 `num_views` int(11) DEFAULT NULL,

 `closed` tinyint(1) DEFAULT NULL,

 `sticky` tinyint(1) DEFAULT NULL,

 `first_post_time` datetime DEFAULT NULL,

 `last_post_time` datetime DEFAULT NULL,

 `first_poster_id` int(11) NOT NULL,

 `last_poster_id` int(11) NOT NULL,

 PRIMARY KEY (`id`),

 KEY `FK_forum` (`forum_id`),

 KEY `FK_first_post_user` (`first_poster_id`),

 KEY `FK_last_post_user` (`last_poster_id`),

 CONSTRAINT `FK_first_post_user` FOREIGN KEY (`first_poster_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

 CONSTRAINT `FK_forum` FOREIGN KEY (`forum_id`) REFERENCES `forum_forums` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

 CONSTRAINT `FK_last_post_user` FOREIGN KEY (`last_poster_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1




CREATE TABLE `forum_posts` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `content` text,

 `topic_id` int(11) NOT NULL,

 `user_id` int(11) NOT NULL,

 `edited` tinyint(1) DEFAULT NULL,

 `edited_by` int(11) DEFAULT NULL,

 `create_time` datetime DEFAULT NULL,

 `update_time` datetime DEFAULT NULL,

 PRIMARY KEY (`id`),

 KEY `FK_topic` (`topic_id`),

 KEY `FK_post_user` (`user_id`),

 KEY `FK_post_edited_user` (`edited_by`),

 CONSTRAINT `FK_post_edited_user` FOREIGN KEY (`edited_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

 CONSTRAINT `FK_post_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

 CONSTRAINT `FK_topic` FOREIGN KEY (`topic_id`) REFERENCES `forum_topics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

It’s easy enough to get the number of posts in each topic or the number of topics in each forum using statistical query. But I’m not sure how to get the number of posts in each forum.

My gut tells me it would be a hugely expensive query… so it may be better to denormalize a bit and keep a tally of the post count in each forum. But i’m not sure.

Thanks for any input.

Tom

You can add to table forum_posts field ‘forum_id’ to be filled with $this->topic->forum_id in Forum_posts model, when the post creating.

If I understand correct, you would have relation ‘topic’= array(self::BELONGS_TO, ‘Forum_topic’, ‘topic_id’) in your Forum_posts model? If I understand correct, all will be work.

Then you can do relation ‘posts’= array(self::HAS_MANY, ‘Forum_posts’, ‘forum_id’) in model Forum_forums and do something like $this->postsCount or count($this->posts) in your Forum_forums model.