MySQL + Order By

**goldkiller07** · Mar 21, 2012 12:06

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.

**r937** · Mar 21, 2012 13:59

Originally Posted by goldkiller07

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

**goldkiller07** · Mar 21, 2012 16:33

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?

**r937** · Mar 21, 2012 17:46

Originally Posted by goldkiller07

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

**goldkiller07** · Mar 21, 2012 18:12

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;

**r937** · Mar 21, 2012 18:31

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

**goldkiller07** · Mar 22, 2012 10:57

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;

**r937** · Mar 24, 2012 08:54

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

**goldkiller07** · Mar 24, 2012 16:31

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?

**r937** · Mar 24, 2012 17:12

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

**goldkiller07** · Mar 25, 2012 05:56

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.

**r937** · Mar 25, 2012 06:07

Originally Posted by goldkiller07

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

User Tag List

Thread: MySQL + Order By

Thread Tools

Display

MySQL + Order By

Bookmarks

Bookmarks

Posting Permissions