ShoeLace1291 Posted June 24, 2013 Share Posted June 24, 2013 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! Quote Link to comment https://forums.phpfreaks.com/topic/279496-using-mysql-left-join-to-order-by-results-of-a-subquery/ Share on other sites More sharing options...
abrahamgarcia27 Posted June 24, 2013 Share Posted June 24, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/279496-using-mysql-left-join-to-order-by-results-of-a-subquery/#findComment-1437603 Share on other sites More sharing options...
kicken Posted June 24, 2013 Share Posted June 24, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/279496-using-mysql-left-join-to-order-by-results-of-a-subquery/#findComment-1437618 Share on other sites More sharing options...
Barand Posted June 24, 2013 Share Posted June 24, 2013 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/279496-using-mysql-left-join-to-order-by-results-of-a-subquery/#findComment-1437625 Share on other sites More sharing options...
ShoeLace1291 Posted June 25, 2013 Author Share Posted June 25, 2013 Will changing date_posted to DATETIME or TIMESTAMP work with a unix timestamp? E.G. what's produced with php's time() function. Quote Link to comment https://forums.phpfreaks.com/topic/279496-using-mysql-left-join-to-order-by-results-of-a-subquery/#findComment-1437727 Share on other sites More sharing options...
Barand Posted June 25, 2013 Share Posted June 25, 2013 time Quote Link to comment https://forums.phpfreaks.com/topic/279496-using-mysql-left-join-to-order-by-results-of-a-subquery/#findComment-1437747 Share on other sites More sharing options...
Solution ShoeLace1291 Posted July 11, 2013 Author Solution Share Posted July 11, 2013 Thank you, kicken! Your query worked absolutely beautifully! Quote Link to comment https://forums.phpfreaks.com/topic/279496-using-mysql-left-join-to-order-by-results-of-a-subquery/#findComment-1440279 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.