Creating a custom forum, help with building a query that takes into account posts in categories, etc.

Question

So my database setup is pretty simple.

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

forum_post has a fk_forum_cat_id field that associates each forum post with a category.

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

In addition, forum_post has a date_added and date_edited .

Now I am trying to create the first page for my forum. I want to show a list of forum categories. Each category should have the number of posts and the last post. Can someone give me some direction with a request that does all in one. I do not want to run 20 separate requests!

Answer 1

If I read your question correctly, you are looking for a category, the number of posts in this category and the last post in this 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; 

Answer 2

If forum_post primary key automatically increases (it should be, but we never know ...), this will return you 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 the field names:

• forum_cat_id = forum _cat primary key
• forum_post_id = forum_post primary key
• post_title = post name or the beginning of the text message in forum_post (depends on what you want to show).
• The COUNT(p.fk_forum_cat_id) column COUNT(p.fk_forum_cat_id) will indicate the number of posts in the category

In addition to what you requested, you will get the date of the last post in the category, as I think you will need it if this is a good forum;).

Obs: I have not tested it, so you may need debugging. If you have problems, let me know.

Answer 3

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 4

Queries for dynamically counting things tend to slow down very quickly and consume a lot of processor. Even with good indexes, MySQL has to do a lot of work every time to count all these rows.

An alternative to a query like this would be to summarize the number of posts in the forum_cat table. Create a column called posts_count . Each time a message is created, simply run the query increment or decrease the count.

UPDATE forum_cat SET posts_count = posts_count + 1;

When you start creating the first page, your query becomes much simpler and more effective.