I’m trying to code myself a simple forum and I’ve hit a problem when trying to take the latest post information and forum information in one go.
SELECT * FROM POSTS JOIN THREADS USING(thread_id) JOIN FORUMS USING(forum_id) ORDER BY POSTS.post_time DESC GROUP BY FORUMS forum_id
The other way round (group by, then order) doesn’t work as it takes the first entry from the posts tables so i dont get the latest post.
But I don’t think you can group once you have ordered a table either as I get an error with this statement
#1064 - 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 'GROUP BY forum_forum_id
Is there another solution to this so I can get a list of the forums in one statement, with the latest thread/post details joined?
Otherwise I will have to save the latest post every time someone posts or something… makes no sense to me
Yes, but you’ll need a more complicated query than this to do so…
You need to get the MAX(post ID) from each forum (GROUP BY forum ID). Then you need another join on the post table (so you’ll be joining this same table twice with different aliases) to get the details associated with that ID you got.
I don’t seem to understand. The above causes not unique errors.
I tried the same statement doubled, with a WHERE post_id IN (SELECT MAX(post_id) FROM… JOIN…) added at the end. It seems to be working, but i’m not entirely sure its the most efficient way of dealing with this
SELECT * FROM POSTS JOIN THREADS USING(thread_id) JOIN FORUMS USING(forum_id) WHERE post_id IN (SELECT MAX(post_id) FROM POSTS JOIN THREADS USING(thread_id) JOIN FORUMS USING(forum_id) GROUP BY FORUMS forum_id)
however, i would not do it that way any more, i prefer the derived table approach (subquery in the FROM clause) over the correlated subquery
SELECT forums.id AS forum_id
, forums.title AS forum_title
, threads.id AS thread_id
, threads.title AS thread_title
, posts.title AS post_title
, posts.post_date
, posts.userid
FROM forums
LEFT OUTER
JOIN threads
ON threads.forum_id = forums.id
LEFT OUTER
JOIN ( SELECT thread_id
, MAX(post_date) AS max_date
FROM posts
GROUP
BY thread_id ) AS last_posts
ON last_posts.thread_id = threads.id
LEFT OUTER
JOIN posts
ON posts.thread_id = threads.id
AND posts.post_date = last_posts.max_date
note that forums to threads is LEFT OUTER JOIN, to handle the case when a forum has no threads yet
also, threads to the last_posts derived table as well as threads to the posts table are both LEFT OUTER JOINs too, for threads that have no posts yet
Sorry… I had to go one further!
I have some fields for hidden threads and hidden posts (ie. deleted, i just want to keep for admin sake). Where would I put the clauses for these?
If I put the clauses on the FORUM query (at the end) it again removes forums if they have no threads, or threads if they have no posts. I tried adding to the MAX() query, but the same thing happened?
Ok I modified it a bit:
SELECT * FROM FORUM_FORUMS LEFT OUTER JOIN FORUM_THREADS USING(forum_forum_id) WHERE forum_thread_hidden<>‘1’ LEFT OUTER JOIN (SELECT forum_thread_id,MAX(forum_post_order) AS forum_post_order_max FROM FORUM_POSTS WHERE forum_post_hidden<>‘1’ GROUP BY forum_thread_id) AS FORUM_LAST_POSTS USING (forum_thread_id) LEFT OUTER JOIN FORUM_POSTS ON FORUM_POSTS.forum_thread_id=FORUM_THREADS.forum_thread_id AND FORUM_LAST_POSTS.forum_post_order_max=FORUM_POSTS.forum_post_order GROUP BY forum_forum_id ORDER BY forum_forum_id ASC
It is fine for hidden posts (without bolded section) but not for hidden threads. I get an SQL error on this code.
I tried
LEFT OUTER JOIN (SELECT * FORUM_THREADS WHERE forum_thread_hidden<>‘1’) USING(forum_forum_id)
but this also gives an error #1064 - You have an error in your SQL syntax;
You can’t put a WHERE clause in the middle of the table list. It either comes after all the JOINs, or becomes part of the ON clause that defines the join.
SELECT * FROM FORUM_FORUMS
LEFT OUTER JOIN (SELECT * FORUM_THREADS WHERE forum_thread_hidden<>‘1’) USING(forum_forum_id) LEFT OUTER JOIN (SELECT forum_thread_id,MAX(forum_post_order) AS forum_post_order_max FROM FORUM_POSTS WHERE forum_post_hidden<>‘1’ GROUP BY forum_thread_id) AS FORUM_LAST_POSTS USING (forum_thread_id) LEFT OUTER JOIN FORUM_POSTS ON FORUM_POSTS.forum_thread_id=FORUM_THREADS.forum_thread_id AND FORUM_LAST_POSTS.forum_post_order_max=FORUM_POSTS.forum_post_order GROUP BY forum_forum_id ORDER BY forum_forum_id ASC
I wanted to just have a WHERE clause at the end but as cases arise where thread_hidden or post_hidden may be NULL it doesn’t seem to work. Is there something else I can say instead of <>‘1’ that will include the case of NULL or 0 results?
you don’t honestly write your SQL in one humoungously monolithic single line, do you?
learn some formatting, i.e. line breaks and indenting – you will thank me later
SELECT something
, anything
, just_not_the_dreaded_evil_select_star
FROM forum_forums
LEFT OUTER
JOIN forum_threads
ON forum_threads.forum_forum_id = forum_forums.forum_id
AND forum_threads.forum_thread_hidden <> 1
LEFT OUTER
JOIN ( SELECT forum_thread_id
, MAX(forum_post_order) AS forum_post_order_max
FROM forum_posts
WHERE forum_post_hidden <> '1'
GROUP
BY forum_thread_id ) AS forum_last_posts
ON forum_last_posts.forum_thread_id = forum_threads.forum_thread_id
LEFT OUTER
JOIN forum_posts
ON forum_posts.forum_thread_id = forum_threads.forum_thread_id
AND forum_posts.forum_post_order = FORUM_LAST_POSTS.forum_post_order_max
ORDER
BY forum_forums.forum_id ASC
