Creating a custom forum, help with building a query that counts posts in a categories etc.

Question

So my database setup is fairly simple.

I have a forum_cat table (a forum category) and forum_post table.

The forum_post has a field fk_forum_cat_id which ties each forum post to a category.

Each forum_post also has a field fk_parent_forum_post_id which basically says it belongs to an original post.

Further more, there is a date_added and date_edited field in forum_post.

Now, I am trying to generate the front page for my forum. I want to show a list of forum categories. Each one category should have a post count and the latest post. Could someone give me some direction with a query that does that all in one. I don't want to run 20 separate queries!

Answer 1

You can adapt this example to your problem:

SELECT * 
FROM  `test_post` AS p3
JOIN (

SELECT MAX( id ) AS id
FROM  `test_post` AS p1
JOIN (

SELECT MAX(  `test_post`.date ) AS DATE, cat
FROM  `test_post` 
GROUP BY cat
) AS p2 ON p1.date = p2.date
AND p1.cat = p2.cat
GROUP BY p1.cat
) AS p4 ON p3.id = p4.id;

Answer 2

If forum_post primary key is auto-incremented (should be but we never know...), this will return what you need:

SELECT c.forum_cat_id, COUNT(p.fk_forum_cat_id), MAX(p.date_added), 
    (SELECT p2.post_title FROM forum_post AS p2 
        WHERE p2.forum_post_id = (SELECT MAX(p3.forum_post_id) FROM forum_post AS p3 
            WHERE p3.fk_forum_cat_id = p2.fk_forum_cat_id) AND p2.fk_forum_cat_id = c.forum_cat_id) 
FROM forum_cat AS c INNER JOIN 
    forum_post AS p ON p.fk_forum_cat_id = c.forum_cat_id
GROUP BY c.forum_cat_id;

I had to guess some field names:

• forum_cat_id = forum _cat primary key
• forum_post_id = forum_post primary key
• post_title = post title or begining of post text in forum_post (depends on what you want to show).
• The COUNT(p.fk_forum_cat_id) column will contain the post count in category

In addition to what you asked, you will get the date of the lastest post in the category as I think you'll need it if it is a good forum ;).

Obs: I didn't test it so you may need some debugging. If you have problems, let me know.

Answer 3

Queries to dynamically count things tend to get slow very quickly and consume a lot of cpu. Even with good indexes, MySQL has to do a lot of work every single time to count all those rows.

An alternative to a query like this would be to summarize the counts of posts in the forum_cat table. Create a column named something like posts_count. Every time a post is created, it's easy enough to run a query increment or decrement the count.

UPDATE forum_cat SET posts_count=posts_count+1;

When you go to create the front page, your query becomes much more simple, and performant.

Answer 4

If I read your question correctly, you are seeking the category, the count of posts in the category, and the latest post in that category. Perhaps this simplification of laurent-rpnet's answer will do the trick...

SELECT c.forum_cat_id,
       COUNT(p.fk_forum_cat_id),
       MAX(p.date_added), 
      (SELECT p2.post_title
           FROM forum_post AS p2 
           WHERE p2.forum_cat_id = c.forum_cat_id
           ORDER BY date_added DESC
           LIMIT 1)
    FROM forum_cat AS c INNER JOIN 
               forum_post AS p ON p.fk_forum_cat_id = c.forum_cat_id
    GROUP BY c.forum_cat_id;