So my database setup is rather simple.

I've got a forum_cat table (a forum category) and forum_post table.

The forum_post includes a area fk_forum_cat_id which ties each forum publish to some category.

Each forum_post also offers a area fk_parent_forum_post_id which essentially states it goes for an original publish.

Additionally, there's a date_added and date_edited area in forum_publish.

Now, I'm attempting to create the top of the page for my forum. I wish to show a listing of forum groups. Each one of these category must have a publish count and also the latest publish. Could someone produce some direction having a query that does that all-in-one. I'd rather not run 20 separate queries!

Basically read your question properly, you are searching for the course, the count of posts within the category, and also the latest publish for the reason that category. Possibly this simplification of laurent-rpnet's answer will have the desired effect...

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;

If forum_post primary secret is auto-incremented (ought to be but we don't know...), this can return the thing 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 needed to guess some area names:

  • forum_cat_id = forum _cat primary key
  • forum_post_id = forum_post primary key
  • post_title = publish title or begining of publish text in forum_post (is dependent on what you would like to exhibit).
  • The COUNT(p.fk_forum_cat_id) column will retain the publish count in category

Additionally as to the you requested, you're going to get the date from the lastest publish within the category as I think you will require it if it's a great forum ).

Obs: I did not test drive it to take some debugging. If you have troubles, tell me.

You are able to adapt this situation for 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;

Queries to dynamically count things often get slow very rapidly and overeat of cpu. Despite good indexes, MySQL needs to perform a large amount of work each and every time for you to count all individuals rows.

An alternative choice to a question like this is in summary the counts of posts within the forum_cat table. Produce a column named something similar to posts_count. Whenever a publish is produced, it is not difficult enough to operate a question increment or decrement the count.

UPDATE forum_cat SET posts_count=posts_count+1

When you attend produce the top of the page, your query becomes less complicated, and performant.