SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL + Order By

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

    If I put a limit on that join then it messes up.

    Any help would be much appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by goldkiller07 View Post
    I think it has something to do with ...
    If I put a limit on that join then it messes up.
    it's already messed up enough as it is !!

    Code:
    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
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your help.

    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by goldkiller07 View Post
    As the post id (pid) isn't being selected I thought the only thing I could use was the time field?
    could you show your query please? i'll see if i can fix it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The code does seem to work, but it just seems wrong to join something that isn't a unique key.

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

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

  7. #7
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    does the posts table have the forum id?

    forum id would be redundant in the posts table, because posts has topic id, and topics has forum id...

    but it would be useful here, because then the subquery to get post count per forum would not require a join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There isn't a forum id field in the post table, but I'm at a stage where I can add one.

    I thought this might be possible by simply using joins or would it take too long to execute?

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

  11. #11
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Thanks helping.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by goldkiller07 View Post
    I tried using the above query and got the following error
    [sigh]

    copy/paste in haste, recode at leisure
    Code:
    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 ) AS
              WHERE forum_topics.`delete` = 1
             GROUP
                 BY forum_topics.fid ) AS top
        ON top.fid = child.fid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •