Using MYSQL Left Join to Order by Results of a SubQuery

[ShoeLace1291]

I am trying to create a custom forum with CodeIgniter.  I have 3 primary tables in this scenario:  boards, threads, and messages.  I want to get all the threads in the board that a user is viewing.  If I use a plain old select query with ORDER BY thread_id DESC, all that does is obviously orders them by when they were posted in descending order.  I want to order the threads by when their last message was posted.  For example, if a user posts a thread called "Thread A", and after that a user posts "Thread B", in the original query would display Thread B followed by Thread A.  But if someone posts a reply to Thread A before a reply is posted to Thread B, then Thread A should be at the top.  I hope this makes sense. 

 

Below is the query I am currently working with.
 

 

$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 ON m.thread_id = t.thread_id
            WHERE t.board_id = ".$board_id."
            ORDER BY q.date_posted DESC
            LIMIT ".$starting.", ".$this->user['results_per_page'];

 

This is returning the error Unknown column 't.thread_id' in 'where clause'

 

 

CREATE TABLE IF NOT EXISTS `forum_messages` (
  `message_id` int(15) NOT NULL AUTO_INCREMENT,
  `thread_id` int(15) NOT NULL,
  `author_id` int(15) NOT NULL,
  `content` text NOT NULL,
  `date_posted` text NOT NULL,
  PRIMARY KEY (`message_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
 
CREATE TABLE IF NOT EXISTS `forum_threads` (
  `thread_id` int(15) NOT NULL AUTO_INCREMENT,
  `board_id` int(15) NOT NULL,
  `author_id` int(15) NOT NULL,
  `title` text NOT NULL,
  `date_posted` text NOT NULL,
  `views` int(15) NOT NULL,
  PRIMARY KEY (`thread_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
 
CREATE TABLE IF NOT EXISTS `forum_boards` (
  `board_id` int(15) NOT NULL AUTO_INCREMENT,
  `category_id` int(15) NOT NULL,
  `position` tinyint(1) NOT NULL,
  `title` text NOT NULL,
  `description` text NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`board_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

 

I have been trying to find a solution to this problem for the last week or so... It has been really frustrating to find it.  Any help would be greatly appreciated!

[abrahamgarcia27]

I would do something simple like date modified row on the actual thread table and when there is a post/comment just update that field and just order by date modified.

[kicken]

Your date_posted columns in both the forum_messages and forum_threads tables need to be a DATETIME or TIMESTAMP type, not TEXT. Once you've fixed that issue, you just need to have your sub-query return the thread id, and MAX(date_posted) in order to get the newest post, then order by that in the outer query. Also, you don't want to LIMIT the sub-query as that would cause it to return only a single row, not a single row per thread which is what you need

 

SELECT
	t.board_id, t.thread_id
FROM forum_threads AS t
LEFT JOIN (
	SELECT m.thread_id, MAX(m.date_posted) as lastPost
	FROM forum_messages AS m
	GROUP BY m.thread_id
) AS q ON t.thread_id = q.thread_id
WHERE t.board_id = $ID
ORDER BY q.lastPost DESC
LIMIT $X,$Y

Make sure you have an index on forum_messages(thread_id,date_posted). Then the sub-query will run nearly instantly.

[Barand]

I'd make a couple of changes to that query.

 

In the subquery, have IFNULL(MAX(m.date_posted), '0000-00-00') as lastPost

 

and then order the whole query by ORDER BY MAX(t.date_posted, q.lastPost) so it sorts by the lastest message (if exist) or the thread  date, whichever is latest

Edited June 24, 2013 by Barand

[ShoeLace1291]

Will changing date_posted to DATETIME or TIMESTAMP work with a unix timestamp?  E.G. what's produced with php's time() function.

[Barand]

time

[ShoeLace1291]

Thank you, kicken!  Your query worked absolutely beautifully!