I have two tables, one for topics, and one for messages.
In terms of relationship, messages are posted within a topic, so each topic has many messages.
I am trying to make a query that would:
– list all the topics
– get the count of messages for each topic
– get the latest message posted for each topic (time and content)
I basically tried to do a query similar to:
SELECT topic.*, COUNT(msg.id), MAX(msg.time), msg.content FROM topics LEFT JOIN ( SELECT msg.id, msg.time, msg.content, msg.topic_id FROM messages AS msg ORDER BY time DESC ) AS msg ON msg.topic_id=topic.id GROUP BY topic.id
However, this results in an error
Expression #… (closed) of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘msg.time’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
I understand that I could just toggle the
only_full_group_by setting, but my understanding is that I then have no guarantee that the
msg.content will return me the latest message as expected.
I found many
greatest n per group questions online, but most seem to only deal with a single table with the same table being queried in the query and subquery.
Is there any reliable way to achieve this with MySQL?
SELECT t.*, m4.count, m4.time, m4.content FROM topics t LEFT JOIN ( SELECT m1.topic_id, m3.count, m3.time, m1.content FROM messages m1 INNER JOIN ( SELECT m2.topic_id, COUNT(m2.topic_id) count, MAX(m2.time) time FROM messages m2 GROUP BY m2.topic_id ) m3 ON m1.topic_id = m3.topic_id AND m1.time = m3.time ) m4 ON t.id = m4.topic_id
I assume that
messages (topic_id, time) is unique.