MySQL Query to create a "bump-system" for a forum I am making

Question

Hello, I'm having trouble forming a MySQL query that performs the following action:

Select all threadIDs from the threads table ordering by the timestamp (in descending order) of the most recent post (largest timestamp) with threadID equal to the threadID of each thread. So basically I want to go through the threads and have MySQL check the database for let's say thread 0. It then checks all of the posts that have threadID of 0 and sorts thread0 based on the largest timestamp of the posts inside of thread0. Then it repeats this for thread1, thread2, etc and sorts them accordingly.

Is this even possible? It is to create the "bump-system" effect of the forum, where the most recently active thread is bumped to the top of the list continuously until the thread dies out, then it drops to the bottom. I used to use a different implementation where I stored a lastActivity timestamp in the threads table and updated it when a new post was submitted into the thread, but this query would make things a lot more efficient.

Thanks a lot! There are two tables relevant here: threads and posts. The posts have a threadID field that stores the ID of the thread it belongs to, and it also has a timestamp field. Threads has a field threadID that corresponds to the post's threadID.

Answer 1

SELECT  *
FROM    threads t
LEFT JOIN
        posts p
ON      p.id =
        (
        SELECT  p.id
        FROM    posts pi
        WHERE   pi.threadID = t.threadID
        ORDER BY
                pi.timestamp DESC
        LIMIT 1
        )

Having an index on posts (threadID, timestamp) will greatly improve this query.

Note that unlike GROUP BY solutions, this query also selects all fields from posts and works even if you have duplicates on the latest posts.timestamp.

Answer 2

The following has worked for me in the past on MySql. If you want to include more about each thread in the query you'll have to add the columns to the SELECT and the GROUP BY.

select thread.threadID, max(comments.modifiedOn) as threadUpdated 
   from thread inner join comments on thread.threadID = comments.threadID 
   group by 1 
   order by 2 desc;

This query serves your primary request, which is a list of threads ordered my most recent comment. It will not return threads with no comments as-is, you would need to change the join to an outer join to do that.

Answer 3

SELECT t.*, p1.`timestamp`
FROM threads t 
  JOIN posts p1 ON (p1.threadid = t.id)
  LEFT JOIN posts p2 ON (p2.threadid = t.id 
    AND p1.`timestamp` < p2.`timestamp`)
WHERE p2.threadid IS NULL
ORDER BY p1.`timestamp` DESC;