Jump to content

Order topics by latest reply time


kgenly

Recommended Posts

I'm certain that this is a very common problem, but a great deal of googling has so far only provided me with a partial solution.

 

I have a forum that is broken up into three tables: forums, topics, and replies.

 

I am trying to list the topics in a given forum, based on when the most recent reply occurred to that topic. Or, if there are no replies, then the time that the original topic was created.

 

So far I have this:

 

"SELECT t.ID, t.subject, t.date, t.byUser, t.byChar, t.locked, t.sticky FROM rp_forumtopics as t LEFT JOIN rp_forumposts as r ON t.ID = r.topic AND r.date = (SELECT MAX(date) FROM rp_forumposts WHERE t.ID = r.topic) WHERE t.forum='$forumID' ORDER BY COALESCE(r.date, t.date) DESC LIMIT $start_from, 20"

 

The problem is that the MAX(date) seems to select only the most recent post made in the entire forum, not the most recent for every topic.

 

Can anyone help? Thank you very much in advance. :)

Link to comment
https://forums.phpfreaks.com/topic/210156-order-topics-by-latest-reply-time/
Share on other sites

I think I may have solved this, I am now using:

 

SELECT t.ID, t.subject, t.date, t.byUser, t.byChar, t.locked, t.sticky FROM rp_forumtopics as t LEFT JOIN (SELECT topic, MAX(date) as maxdate FROM rp_forumposts GROUP BY topic) as r ON t.ID = r.topic WHERE t.forum='$forumID' ORDER BY COALESCE(maxdate, t.date) DESC LIMIT $start_from, 20

 

This may be horribly inefficient, but it's a step in the right direction as it does what I want. I'm open to better ways of implementing this if anyone knows one, though!

Okay, I think there's a lot of confusion in the air here.. ;) Fenway is telling me to select topics, and you're telling me to group by topic which I could only do if I switched tables completely and selected from the post table instead of the topic table. There's three different tables at play here.

 

The solution I'd come up with earlier was:

 

$result = mysql_query("SELECT t.ID, t.subject, t.date, t.byUser, t.byChar, t.locked, t.sticky FROM rp_forumtopics as t 
LEFT JOIN (SELECT topic, MAX(date) as maxdate FROM rp_forumposts GROUP BY topic) as r ON t.ID = r.topic 
WHERE t.forum='$forumID' ORDER BY t.sticky DESC, COALESCE(maxdate, t.date) DESC LIMIT $start_from, 20") or die(mysql_error());

 

Which is working very well for me and does everything I want, including putting sticky posts at the top and then ordering by most recent posts beneath that, and I can read it and understand it well enough to modify it because, well, I wrote it.

 

So again, if there is a more efficient way to do this or one of you is spotting a glaring error that will jump out and bite me sometime in the future, I'm all ears. On the other hand if we're currently engaged in guessing how to make it work or teaching me to build a query I understand, thank you sincerely for your concern and your time but we can probably mark this one as solved. :)

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.