Hi,
I’ve recently coded a simple forum and now I’m trying to show the latest updated topics on the homepage. To do so I’m using the following query -
SELECT topics.title
, topics.tid AS `id`
, forums.name AS `forum_name`
, forums.fid AS `forum_id`
, forums.view_level
, lp.latest_post
, users_b.email AS `last_poster_email`
, users_b.name AS `last_poster_name`
, users_b.uid AS `last_poster_uid`
, users_b.profile_url AS `last_poster_profile`
, user_groups.prefix
, user_groups.suffix
FROM forum_topics AS topics
INNER
JOIN forum_forums AS forums
ON forums.fid = topics.fid
LEFT OUTER
JOIN ( SELECT tid
, time AS latest_post
, author AS latest_poster_id
FROM forum_posts
WHERE `active` = 1
GROUP
BY tid
ORDER BY
time
DESC) AS lp
ON lp.tid = topics.tid
INNER
JOIN users AS users_b
ON users_b.uid = lp.latest_poster_id
INNER
JOIN user_groups
ON user_groups.id = users_b.user_group_id
WHERE
forums.view_level <= '1'
ORDER BY
lp.latest_post DESC
LIMIT 5;
The problem is, when I go to PHPMyAdmin and order the posts table by date, the newest posts aren’t the ones being displayed. I think it has something to do with -
LEFT OUTER
JOIN ( SELECT tid
, time AS latest_post
, author AS latest_poster_id
FROM forum_posts
WHERE `active` = 1
GROUP
BY tid
ORDER BY
time
DESC) AS lp
ON lp.tid = topics.tid
LEFT OUTER
JOIN ( SELECT tid
, [COLOR="#FF0000"]MAX([/COLOR]time[COLOR="#FF0000"])[/COLOR] AS latest_post
FROM forum_posts
WHERE `active` = 1
GROUP
BY tid ) AS lp
ON lp.tid = topics.tid
this is an aggregate subquery which produces one row per topic from amongst all the posts for that topic
i’ve added a very necessary aggregate function (in red) to make it make sense
it now returns the time of the latest post for each topic
you will need an additional join to the posts table to get the actual post that corresponds to the latest time
I added another join in like you suggested and it all works great!
The only problem I had was trying to work out what to join the 2 post tables with. As the post id (pid) isn’t being selected I thought the only thing I could use was the time field?
The code does seem to work, but it just seems wrong to join something that isn’t a unique key.
SELECT topics.title
, topics.tid AS `id`
, forums.name AS `forum_name`
, forums.fid AS `forum_id`
, forums.view_level
, lp.latest_post
, lpo.author AS `latest_poster_id`
, users_b.email AS `last_poster_email`
, users_b.name AS `last_poster_name`
, users_b.uid AS `last_poster_uid`
, users_b.profile_url AS `last_poster_profile`
, user_groups.prefix
, user_groups.suffix
FROM forum_topics AS topics
INNER
JOIN forum_forums AS forums
ON forums.fid = topics.fid
LEFT OUTER
JOIN ( SELECT tid
, MAX(time) AS latest_post
FROM forum_posts
WHERE `active` = 1
GROUP
BY tid) AS lp
ON lp.tid = topics.tid
INNER
JOIN forum_posts AS lpo
ON lpo.time = lp.latest_post
INNER
JOIN users AS users_b
ON users_b.uid = lpo.author
INNER
JOIN user_groups
ON user_groups.id = users_b.user_group_id
WHERE
forums.view_level <= '{$user_level}'
ORDER BY
lp.latest_post DESC
LIMIT 5;
it “works” most likely because there is a very very high probability that no two posts have the same time
you should really still join based on the forum and active code as well…
SELECT topics.title
, topics.tid AS `id`
, forums.name AS `forum_name`
, forums.fid AS `forum_id`
, forums.view_level
, lp.latest_post
, lpo.author AS `latest_poster_id`
, users_b.email AS `last_poster_email`
, users_b.name AS `last_poster_name`
, users_b.uid AS `last_poster_uid`
, users_b.profile_url AS `last_poster_profile`
, user_groups.prefix
, user_groups.suffix
FROM forum_forums AS forums
INNER
JOIN forum_topics AS topics
ON topics.fid = forums.fid
INNER
JOIN ( SELECT tid
, MAX(time) AS latest_post
FROM forum_posts
WHERE `active` = 1
GROUP
BY tid ) AS lp
ON lp.tid = topics.tid
INNER
JOIN forum_posts AS lpo
[COLOR="#FF0000"]ON lpo.tid = topics.tid
AND lpo.`active` = 1
AND lpo.time = lp.latest_post[/COLOR]
INNER
JOIN users AS users_b
ON users_b.uid = lpo.author
INNER
JOIN user_groups
ON user_groups.id = users_b.user_group_id
WHERE forums.view_level <= '{$user_level}'
ORDER
BY lp.latest_post DESC
Thanks for that r937! I never knew you could join by multiple fields like that.
Instead of creating a new topic about a similar query…
I’ve moved onto doing the forum index page. On this page I have the query below which grabs the forum categories and sub forums. The query also works out how many topics there are in each forum and displays them. What I now want to do is work out how many posts there are in each forum. Would this even be possible with the following query? If so, how would I go about this?
SELECT forum.name
, forum.fid AS `id`
, forum.parent
, forum.view_level
, child.fid AS `sub_id`
, child.name AS `sub_name`
, child.description AS `sub_description`
, child.view_level AS `sub_view_level`
, child.parent AS `sub_parent`
, COALESCE(top.topics,0) AS `topics`
FROM forum_forums AS forum
LEFT OUTER
JOIN `forum_forums` AS `child`
ON `child`.`parent` = `forum`.`fid`
LEFT OUTER
JOIN ( SELECT fid,
COUNT(*) AS topics
FROM forum_topics
WHERE `delete` = 1
GROUP
BY fid ) AS top
ON top.fid = child.fid
WHERE
forum.parent = '0'
AND
forum.view_level <= $user_level
AND
child.view_level <= $user_level
ORDER
BY forum.order, child.order
ASC;
LEFT OUTER
JOIN ( SELECT fid,
COUNT(*) AS topics
FROM forum_topics
WHERE `delete` = 1
GROUP
BY fid ) AS top
ON top.fid = child.fid
to this –
LEFT OUTER
JOIN ( SELECT forum_topics.fid
, COUNT(*) AS topics
, SUM(tp.topicposts) AS forumposts
FROM forum_topics
LEFT OUTER
JOIN ( SELECT tid
, COUNT(*) AS topicposts
FROM forum_posts
GROUP
BY tid ) AS tp
ON tp.tid = forum_topics.fid ) AS
WHERE forum_topics.`delete` = 1
GROUP
BY forum_topics.fid ) AS top
ON top.fid = child.fid
I tried using the above query and got the following error -
Fatal error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE forum_topics.delete = 1 GROUP BY forum_topics.fid ’ …
I think I’ll use the suggestion of adding the forum id field to the posts table, it seems a lot less complicated.
LEFT OUTER
JOIN ( SELECT forum_topics.fid
, COUNT(*) AS topics
, SUM(tp.topicposts) AS forumposts
FROM forum_topics
LEFT OUTER
JOIN ( SELECT tid
, COUNT(*) AS topicposts
FROM forum_posts
GROUP
BY tid ) AS tp
ON tp.tid = forum_topics.fid -- removed [COLOR="#FF0000"]) AS[/COLOR]
WHERE forum_topics.`delete` = 1
GROUP
BY forum_topics.fid ) AS top
ON top.fid = child.fid
