CODEWITHSUNDEEP
sql
Tafadzwa Gonera
Tafadzwa Gonera

Reputation: 352

Counting forum topics and replies

I have 2 tables: posts and forum_topics. Each post (a reply) is associated with another post (a forum topic, which is then associated with the forum_topics tables).

Problem: I need to count all forum topics and replies in the posts table. This is what I have so far:

SELECT ForumTopic.id, ForumTopic.title, ForumTopic.modified, COUNT(ReplyLeftOuterJoin.id) as replies_count
FROM forum_topics AS ForumTopic
LEFT OUTER JOIN posts AS PostLeftOuterJoin
ON PostLeftOuterJoin.object_id = ForumTopic.id
    AND PostLeftOuterJoin.object_type = 'forum_topic'
    AND PostLeftOuterJoin.status = 'approved'
LEFT OUTER JOIN posts AS ReplyLeftOuterJoin
ON ReplyLeftOuterJoin.object_id = PostLeftOuterJoin.id
   AND ReplyLeftOuterJoin.object_type = 'post'
   AND ReplyLeftOuterJoin.status = 'approved'
WHERE ForumTopic.forum_category_id = 'some_id'

Edit

Currently I'm only getting a count of all replies associated with a forum_topic (post) in the posts table. I would like to get a count of forum_topics in posts table associated with a forum topic in the forum_topics table.

NB FYI, a solution to this problem should use one query only.

Here is the schema of the two tables:

DROP TABLE IF EXISTS `posts`;
CREATE TABLE `posts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `context_id` bigint(20) unsigned DEFAULT NULL,
  `context_type` enum('resource','module','kwik','user','assignment') COLLATE utf8_unicode_ci DEFAULT NULL,
  `is_private` tinyint(1) NOT NULL,
  `is_unread` tinyint(4) NOT NULL,
  `last_replied` datetime NOT NULL,
  `object_id` bigint(20) unsigned DEFAULT NULL,
  `object_type` enum('forum_topic','forum','user','post') COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` enum('approved','unapproved','disabled') COLLATE utf8_unicode_ci NOT NULL,
  `post` text COLLATE utf8_unicode_ci NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `forum_topics`;
CREATE TABLE `forum_topics` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `view_count` int(10) unsigned NOT NULL,
  `forum_category_id` bigint(20) unsigned NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Upvotes: 0

Views: 507

Answers (2)

Daniel Sparing
Daniel Sparing

Reputation: 2173

SELECT
    ForumTopic.forum_category_id,
    COUNT(DISTINCT PostLeftOuterJoin.id) as forumtopics_count,
    COUNT(ReplyLeftOuterJoin.id) as replies_count
FROM forum_topics AS ForumTopic
LEFT OUTER JOIN posts AS PostLeftOuterJoin
ON PostLeftOuterJoin.object_id = ForumTopic.id
    AND PostLeftOuterJoin.object_type = 'forum_topic'
    AND PostLeftOuterJoin.status = 'approved'
LEFT OUTER JOIN posts AS ReplyLeftOuterJoin
ON ReplyLeftOuterJoin.object_id = PostLeftOuterJoin.id
   AND ReplyLeftOuterJoin.object_type = 'post'
   AND ReplyLeftOuterJoin.status = 'approved'
WHERE ForumTopic.forum_category_id = 'some_id'
GROUP BY
    ForumTopic.forum_category_id
;

Upvotes: 2

Monah
Monah

Reputation: 6794

maybe you can get the result in two queries

the following query will give you the count of posts in each forum

select f.id,f.title,f.modified,Type='Posts',Number=count(*) 
from forum_topics as f
inner join posts p on p.forumid=f.id --u used in your query PostLeftOuterJoin.id ( is this the forum id or the posts id ?)
where p.status='approved' and p.object_type='forum_topic'
group by f.id,f.title,f.modified
union
select f.id,f.title,f.modified,Type='Replies',Number=count(*) 
from forum_topics as f
inner join posts p on p.forumid=f.id --u used in your query PostLeftOuterJoin.id ( is this the forum id or the posts id ?)
where p.status='approved' and p.object_type='posts'
group by f.id,f.title,f.modified

to distinguish between the posts and replies, i added the Type

and from your application level you can get the forum post count when Type=Posts and same as for the replies

another method

select f.id,f.title,f.modified,Posts=(select count(*) from posts p where f.id=p.object_id and p.status='approved' and p.object_type='forum_topic'),
Replies=(select count(*) from posts p on where f.id=p.object_id
and p.status='approved' and p.object_type='posts')
from forum_topics f
where f.forum_category_id='someid'

hope that this will help you

regards

Upvotes: 0

Related Questions