I have a somewhat complicated query that I am using in my custom forum application that I am building. I still don't fully understand subqueries and I don't know if they are causing issues with the rest of the query. I am trying to fetch a list of threads within a certain board which contains the thread's author info, it's latest post as well as the latest post's author info. The list should also contain the number of replies that the thread has. The reply count, however, should not include the very first message of the thread.
Here is the full error message that I am getting:
Expression #10 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'commandcenter.lm.message_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Here is the full compiled query:
SELECT
`t`.`thread_id`, `t`.`board_id`, `t`.`author_id`, `t`.`subject`, `t`.`date_posted`,
`a`.`member_id` AS `author_id`, `a`.`display_name` as `author_display_name`,
`r`.`thread_id` as `r_thread_id`, COUNT(r.message_id) as total_replies,
`lm`.`message_id`, `lm`.`author_id` as `lm_author_id`, `lm`.`date_posted` as `lm_date_posted`
FROM `forum_threads` as `t`
LEFT JOIN `members` as `a` ON `t`.`author_id` = `a`.`member_id`
LEFT JOIN `forum_messages` as `lm` ON `lm`.`message_id` = (SELECT MAX(message_id) FROM forum_messages WHERE thread_id = t.thread_id)
LEFT JOIN `members` as `lma` ON `lm`.`author_id` = `lma`.`member_id`
LEFT JOIN `forum_messages` as `r` ON `t`.`thread_id` = `r`.`thread_id` AND `r`.`message_id` != (SELECT MIN(message_id) FROM forum_messages WHERE thread_id = t.thread_id)
GROUP BY `t`.`thread_id`
Anyone have any ideas?
Note: I am using CodeIgniter's query builder class which doesn't add backticks to subqueries.
