COUNT, GROUP BY, INNER JOIN, php, mysql

Question

I am currently (trying to) develop a forum. Now I've run into a small obstacle. I've four tables in my database; "categories", "forums", "topics" and "replies". They all have an id, a parent id and a name.

My problem is now how I should store and get the amount of topics and replies for each category and forum. At first I thought of saving all the topics and replies in every category and forum, as well as save all the replies in every topic.

But that is something I'd rather try to avoid seeing as I have to do 1x insert and 3x update for every reply made; 1 to category, 1 to forum, 1 to topic and finally the reply itself, an insert.

So instead I thought if it somehow was possible (and if so how) to make a query with COUNT, GROUP BY and INNER JOIN

Something along the line

SELECT * 
  FROM `categories`
  JOIN (COUNT ( * )
         FROM `topics`
     GROUP BY `parent_id`
        WHERE `parent_id` = `categories`.`id`) as `amount_of_topics`
  JOIN (COUNT ( * )
         FROM `topics`
     GROUP BY `parent_id`
        WHERE `parent_id` = `topics`.`id`) as `amount_of_replies`

Just that I don't get it work the way I want to at.

So to fetch all replies and count them for a specific forum, the connection replies and forum got is that parent_id of the reply belongs to a topic whos parent_id belongs to a forum.

Your question is ambiguous. Could you rephrase it either as a data structure question (as per the second paragraph) or as a querying question (as per the subsequent paragraphs)? — Gordon Linoff, Jul 23 '12 at 14:12
There are two questions in there so that is perhaps why; my apologizes for not being cleaer enough. a) Is a query like I pasted there possible to do. If so how? I want to join the amount of topics and replies for each forum. b) If it isn't possible is then the solution to store the topics and replies as integers in the categories and forums tables? — Emz, Jul 23 '12 at 14:25

score 3 · Accepted Answer · answered Jul 23 '12 at 14:26

I'm not sure if I understand your question correctly but if you just want the count of topics and replies for a specific forum (provided the categories belong to a forum and not the other way around) you would go with a query along this line:

SELECT  forums.ID as forum_id
,       forums.name as forum_name
,       Count(topics.ID) AS count_of_topics
,       Count(replies.ID) AS count_of replies
FROM forums 
    LEFT JOIN categories ON forums.ID = categories.parent_id 
    LEFT JOIN topics ON categories.ID = topics.parent_id 
    LEFT JOIN replies ON topics.ID = replies.parent_id
GROUP BY forums.ID, forums.name, categories.ID, categories.name;

If you are looking for the count of topics & replies by categories, use:

SELECT  categories.ID as category_id
,       categories.name as category_name
,       Count(topics.ID) AS count_of_topics
,       Count(replies.ID) AS count_of replies
FROM categories
    LEFT JOIN topics ON categories.ID = topics.parent_id 
    LEFT JOIN replies ON topics.ID = replies.parent_id
GROUP BY forums.ID, forums.name, categories.ID, categories.name;

Hope that helps but if that's not what you are trying to achieve, please add precisions to your question.

score 0 · Answer 2 · answered Jul 23 '12 at 14:48

I think this is the query you want:

SELECT categories.*, `amount_of_topics`, `amount_of_replies`
FROM `categories` JOIN
      (select `parent_id`, COUNT ( * ) as `amount_of_topics` 
       FROM `topics`
       GROUP BY `parent_id`
     )  tp
     on tp.`parent_id` = `categories`.`id`) JOIN
     (select `topics`.`id`) , COUNT ( * ) as `amount_of_replies`
      FROM `topics` 
      GROUP BY `topics`.`id`
    ) ti
    on  tp.`parent_id` = ti.`id`

The idea is to create separate subqueries for each of the topic groups you want, and then to join them together.

COUNT, GROUP BY, INNER JOIN, php, mysql

2 Answers2