[PHP & MySQL] Sorting Threads by Last Post

[punky_munky]

Hey guys,

More of a theory question rather than wanting some code written for me. The project I'm working on at the moment has a small threaded discussion page. I've got most of that working no problems, the bit that's really got me stuck though is sorting the threads by the time of the last post. I've included a bit of SQL to show the fields I have for the related tables so far.

CREATE TABLE `posts` (
  `id` int(11) NOT NULL auto_increment,
  `post` text NOT NULL,
  `date_time` datetime NOT NULL,
  `user_id` int(11) NOT NULL,
  `thread_id` int(11) NOT NULL,

CREATE TABLE `threads` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(256) NOT NULL default '',
  `date_time` datetime NOT NULL,
  `user_id` int(11) NOT NULL,
  `forum_id` int(11) NOT NULL,

In my head I can think of two options:

a) Write a complex SQL query which I can't get my head around

b) Add a 'last_post_id' field to the thread table and update it whenever a post is made. That wouldn't be difficult at all but it seems a bit hacky (although I'm not that fussy).

Opinions?

 

[.Andy]

Shouldnt be too difficult to achieve in SQL.

SELECT
    threads.id,
    posts.date_time
FROM
    threads
    LEFT OUTER JOIN posts ON threads.id = posts.thread_id
GROUP BY 
    threads.id
ORDER BY 
    posts.date_time DESC

 

[.Andy]

Should have added that obviously include threads with no posts and orders the most recent at the top.

HTH

 

[punky_munky]

That selects the first post from the thread though, not the last. Thanks for your input though

 

[punky_munky]

Nevermind, MAX(posts.date_time) AS last_post seemed to fix it

 

[.Andy]

That selects the first post from the thread though, not the last. Thanks for your input though

Very true Just whack a Max() around posts.date_time. That seems to sort it out

i.e:

SELECT
    threads.id,
    max(posts.date_time) as date_time
FROM
    threads
    LEFT OUTER JOIN posts ON threads.id = posts.thread_id
GROUP BY 
    threads.id
ORDER BY 
    posts.date_time DESC

EDIT: beaten to it - glad you got it sorted!

 

[punky_munky]

Thanks your help. It seems so obvious now.