I'm currently struggling to find out the correct query for what I want to achieve and was wondering if anyone has some bright ideas. Been at a lost here for quite some days now and just can't find the correct query to get what I need.
Desired result:
I currently have fully working forum, with multiple categories, due to the fact that there's multiple categories I would like to make an overview page basically stating the last thread, last post, by whom and at what time per category.
Here is how my table is structured
Name: forums Fields: id, name
Name: forum_threads Fields: id, f_id, subject, author, lastpost
Name: forum_posts Fields: id, t_id, post, author, time
The actual problem
I have gotten the query thus far that it displays everything as I want except for one thing, it doesn't get the latest thread subject, everything else seems to be displaying correctly (Note I've disabled forums.id 9 and everything above 12, I don't want to show these results.
SELECT forums.id, t.id AS thread_id,
t.subject,
t.forum_id,
t.author, t.lastpost,
u1.name AS author_name, u1.avatar AS author_avatar,
u2.name AS lastpost_name, u.avatar AS lastpost_avatar
COUNT(p.id) AS postscount, COUNT(DISTINCT (t.id)) AS threadscount,
forums.name,
max(p.time) last_post_date
FROM forum_threads t
INNER JOIN users u1 ON u1.id = t.author
INNER JOIN users u2 ON u2.id = t.lastpost
INNER JOIN forum_posts p ON p.t_id = t.id
INNER JOIN forums ON forums.id = t.forum_id
WHERE forums.id != 9 AND forums.id > 12
GROUP BY forums.id
ORDER BY forums.id ASC, p.id DESC, last_post_date DESC
Anyone has any ideas?
Well, if the thread_id
you're retrieving is correct, and the subject
column on the forum_threads
table contains the subject you're expecting, then there is a problem in your display level code rather than your query. (Since you're pulling thread_id
from the same row as t.subject
, if one is correct as you expect, the other should be, too.)
Besides that, and this is slightly off topic, I'd caution you against using constructs such as WHERE forums.id != 9 AND forums.id > 12
. Instead, you should have a column in the database for disabled
, and query against that. Otherwise you have an unmaintainable mess.
In response to the comments, try adding this block of SQL to your JOIN
clauses:
INNER JOIN (SELECT t2.forum_id as forum_id, MAX(lastpost) as lastpost
FROM forum_threads t2
GROUP BY t2.forum_id) as latest_post_per_forum
ON latest_post_per_forum.forum_id = t.forum_id
and this to your WHERE
clause:
AND t.lastpost = latest_post_per_forum.lastpost
Unfortunately I don't have a convenient MySQL instance at this time to test my syntax, but the gist should be obvious: pull the last post ID out on a per-forum basis and then compare each thread's last post ID to that.