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