SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ORDER then GROUP?

    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.

    Code:
    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

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hrm, so if I udnerstand you correctly you are saying it should be like:
    Code:
    SELECT MAX(post_id) FROM POSTS JOIN POSTS USING (post_id) JOIN THREAD USING(thread_id) JOIN FORUM USING (forum_id)
    ?

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
    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)

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Urgh.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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..

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by L4suicide View Post
    so I went for a beer instead
    my new sig
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    could you show your query please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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;

  12. #12
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  13. #13
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Until now I was using single statements SELECT, WHERE, GROUP/ORDER..

    I'll start using something more readable now xD Thanks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •