l4suicide — 2010-03-21T00:57:03-04:00 — #1
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
dan_grossman — 2010-03-21T02:30:17-04:00 — #2
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.
l4suicide — 2010-03-21T07:13:19-04:00 — #3
Hrm, so if I udnerstand you correctly you are saying it should be like:
SELECT MAX(post_id) FROM POSTS JOIN POSTS USING (post_id) JOIN THREAD USING(thread_id) JOIN FORUM USING (forum_id)
l4suicide — 2010-03-21T07:26:03-04:00 — #4
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)
l4suicide — 2010-03-21T08:14:42-04:00 — #5
Also this method causes problems if there are no threads in the forum, as there is no MAX(post_id) and thus no results for that forum at all.
r937 — 2010-03-21T08:52:47-04:00 — #6
you've made a couple of decent attempts there
your idea about WHERE post_id IN (SELECT MAX(post_id) FROM ... is pretty close to the method used in this old thread -- http://www.sitepoint.com/forums/showthread.php?t=457759
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
ON threads.forum_id = forums.id
JOIN ( SELECT thread_id
, MAX(post_date) AS max_date
BY thread_id ) AS last_posts
ON last_posts.thread_id = threads.id
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
l4suicide — 2010-03-21T12:24:10-04:00 — #7
Thanks! I was just starting to play around with some LEFT OUTER JOIN. But it had taken me all day so I went for a beer instead
Few months back i'd have been doing something very silly with php as well to try and get this stuff..
r937 — 2010-03-21T15:16:43-04:00 — #8
my new sig
l4suicide — 2010-03-22T02:07:16-04:00 — #9
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?
r937 — 2010-03-22T08:37:19-04:00 — #10
could you show your query please
l4suicide — 2010-03-22T21:07:02-04:00 — #11
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.
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;
dan_grossman — 2010-03-22T21:08:33-04:00 — #12
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.
l4suicide — 2010-03-22T21:38:36-04:00 — #13
So should this not work?
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?
r937 — 2010-03-22T22:14:19-04:00 — #14
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
ON forum_threads.forum_forum_id = forum_forums.forum_id
AND forum_threads.forum_thread_hidden <> 1
JOIN ( SELECT forum_thread_id
, MAX(forum_post_order) AS forum_post_order_max
WHERE forum_post_hidden <> '1'
BY forum_thread_id ) AS forum_last_posts
ON forum_last_posts.forum_thread_id = forum_threads.forum_thread_id
ON forum_posts.forum_thread_id = forum_threads.forum_thread_id
AND forum_posts.forum_post_order = FORUM_LAST_POSTS.forum_post_order_max
BY forum_forums.forum_id ASC
l4suicide — 2010-03-22T22:34:50-04:00 — #15
Until now I was using single statements SELECT, WHERE, GROUP/ORDER..
I'll start using something more readable now xD Thanks