Cory
Cory

Reputation: 742

Sort Forum by Last Post Date

I'm working on a custom forum and I notice I was doing something wrong. I was sorting threads based on the thread date. I need to organize the threads based on the date of the last post made in the thread.

I tried the following but it has issues. It lists entries multiple times.

$thread_sql = "SELECT forum_threads.thread_id AS thread_id,
                      forum_threads.thread_title AS thread_title,
                      forum_threads.thread_description AS thread_description,
                      forum_threads.forum_id AS forum_id,
                      forum_threads.thread_postdate AS thread_date,
                      forum_threads.thread_icon AS thread_icon,
                      forum_threads.thread_views AS thread_views,
                      forum_threads.user_id AS user_id,
                      users.user_firstname AS user_firstname,
                      users.user_lastname AS user_lastname
                 FROM forum_threads
           INNER JOIN users ON forum_threads.user_id = users.user_id
           INNER JOIN forum_posts ON forum_threads.thread_id = forum_posts.thread_id
                WHERE forum_threads.forum_id='$_GET[f]'
             ORDER BY forum_posts.post_date DESC ";

Any ides as to how I would order the threads based on the date of the last reply to the threads?

Upvotes: 0

Views: 344

Answers (1)

OmerGertel
OmerGertel

Reputation: 2583

First, I strongly suggest adding the last_post_date as a column on forum_thread table, which is updated every time a post is added to the thread. Otherwise you'll performance issues as you get more threads in the forum, because of the join.

Anyway, an answer to your question:

$thread_sql = "SELECT forum_threads.thread_id AS thread_id,
                  forum_threads.thread_title AS thread_title,
                  forum_threads.thread_description AS thread_description,
                  forum_threads.forum_id AS forum_id,
                  forum_threads.thread_postdate AS thread_date,
                  forum_threads.thread_icon AS thread_icon,
                  forum_threads.thread_views AS thread_views,
                  forum_threads.user_id AS user_id,
                  users.user_firstname AS user_firstname,
                  users.user_lastname AS user_lastname, 
                  max(forum_posts.post_date) last_post_date
             FROM forum_threads
       INNER JOIN users ON forum_threads.user_id = users.user_id
       INNER JOIN forum_posts ON forum_threads.thread_id = forum_posts.thread_id
            WHERE forum_threads.forum_id='$_GET[f]'
            group by 
                  forum_threads.thread_id,
                  forum_threads.thread_title,
                  forum_threads.thread_description,
                  forum_threads.forum_id,
                  forum_threads.thread_postdate,
                  forum_threads.thread_icon,
                  forum_threads.thread_views,
                  forum_threads.user_id,
                  users.user_firstname,
                  users.user_lastname 
         ORDER BY max(forum_posts.post_date) DESC ";

Also, I think something like this may work faster:

SELECT forum_threads.thread_id AS thread_id,
       forum_threads.thread_title AS thread_title,
       forum_threads.thread_description AS thread_description,
       forum_threads.forum_id AS forum_id,
       forum_threads.thread_postdate AS thread_date,
       forum_threads.thread_icon AS thread_icon,
       forum_threads.thread_views AS thread_views,
       forum_threads.user_id AS user_id,
       users.user_firstname AS user_firstname,
       users.user_lastname AS user_lastname
      (SELECT MAX(post_date) 
       FROM forum_posts
       WHERE thread_id = forum_threads.thread_id) last_post_date
   FROM forum_threads
   INNER JOIN users ON forum_threads.user_id = users.user_id
   WHERE forum_threads.forum_id='$_GET[f]'
   ORDER BY last_post_date DESC;

Upvotes: 1

Related Questions