So my database setup is rather simple.
I've got a
forum_cat table (a forum category) and
forum_post includes a area
fk_forum_cat_id which ties each forum publish to some category.
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_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;
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 _catprimary key
post_title= publish title or begining of publish text in
forum_post(is dependent on what you would like to exhibit).
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.