Reputation: 742
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
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