How do I use MySQL Join to sort forum threads by their last reply?

Question

I'm having a bit of trouble with writing a query for this. I think I have the basic logic down, although I might not. What I want to do is get all the threads based on the board that the user is viewing, and then sort those threads based on the time of their last reply. The query doesn't return any error, it only gets the most recently updated thread.

Here's my query:

$query = "
            SELECT
            t.child_id, t.thread_id,
            m.thread_id, m.message_id, m.date_posted
            FROM forum_threads AS t
            LEFT JOIN forum_messages AS m ON t.thread_id = m.thread_id
            WHERE t.child_id = ".$board_id."
            ORDER BY m.date_posted DESC
            LIMIT ".$starting.", ".$this->user['results_per_page'];

This is the query that is being returned:

            SELECT t.child_id, t.thread_id, m.thread_id, m.message_id, m.date_posted   
            FROM forum_threads AS t   
            LEFT JOIN forum_messages AS m   
            ON t.thread_id = m.thread_id   
            WHERE t.child_id = 2   
            ORDER BY m.date_posted DESC LIMIT 0, 15 

Update

I have attempted to go with the idea suggested by ethrbunny, although I am completely lost with what is causing it to return an error.

$query = "
            SELECT
            t.board_id, t.thread_id
            FROM forum_threads AS t
            LEFT JOIN (
            SELECT m.thread_id, m.message_id
            FROM forum_messages AS m
            WHERE m.thread_id = t.thread_id
            ORDER BY m.message_id DESC
            LIMIT 1
            ) AS q
            WHERE t.board_id = ".$board_id."
            ORDER BY q.date_posted DESC
            LIMIT ".$starting.", ".$this->user['results_per_page'];

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 t.board_id = 4 ORDER BY q.date_posted DESC LIMIT' at line 11

Answer 1

SELECT ... , MAX(m.date_posted) AS latest_reply ...

GROUP BY t.thread_id
ORDER BY latest_reply DESC ...

But why is date_posted a TEXT? Shouldnt it be a datetime, or maybe a int (if a timestamp)

Because will never be able to optimize running MAX on a text column, would suggest using

MAX(m.message_id) AS latest_reply

instead, which as messages are probably inserted in date order, should be equivient.

---

Edited to add: The query written out in full...

$query = "
SELECT t.thread_id, title, 
    MAX(m.message_id) AS latest_reply 
FROM forum_threads AS t
    LEFT JOIN forum_messages AS m ON t.thread_id = m.thread_id
WHERE t.child_id = ".$board_id."
GROUP BY t.thread_id
ORDER BY latest_reply DESC 
LIMIT ".$starting.", ".$this->user['results_per_page'];

Answer 2

(SQLFiddle is fighting me this morning - so this is mostly a guess)

select <columns> from forum_threads t0
  left join
  ( select <columns> from forum_messages m0 where m0.thread_id = t0.thread_id 
    order by m0.date_posted desc limit 1) as q0
  order by q0.date_posted desc;

I'm curious why your date field is a text. This will make it tough to sort on.

Answer 3

Yo could use a SELECT statement in the ORDER BY to select the latest date of the replies of a thread. An example can be found here:

SQL FIDDLE

With your tables, would be something like this:

SELECT * FROM forum_threads ft ORDER BY (SELECT date_posted FROM forum_messages fm WHERE fm.thread_id = ft.thread_id ORDER BY date DESC LIMIT 1) DESC;

But look at the provided example. First query orders by thread's date, second by latest reply date.