Order topics by latest reply time

[kgenly]

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.

[kgenly]

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!

[fenway]

If you want a great read, see here; but that's a lot to digest.

 

Ignoring efficiency for a moment, first start by writing a simple query -- with a gROUP BY -- to get the most recent uid for each topic, by date.

[kgenly]

Thanks, great read!

 

For your suggestion, what am I supposed to  be grouping the topics by?

[fenway]

By forum.

[kgenly]

I only want results of one forum at a time. Wouldn't a WHERE be more accurate?

[fenway]

Let's start again -- you want the most recent for each forum, correct?

[kgenly]

Nope. The most recent for only one given forum at a time. Sorry for the confusion.

[jdavidbakr]

I think you'll want to group by topic.

[kgenly]

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.

[kgenly]

And I just figured out how to mark a topic as solved, so I'm going to do that.

 

Thanks guys!