How can I:
- list the latest
rooms_messageon room - with discussions_messages.text (from
discussions_messages) - and discussions_replies.replies (belongsTo discussions_messages) (from
discussions_replies)?
I read many post (SQL select only rows with max value on a column or /a/38505618) but it’s not the same problem : I can get the latest but I d’ont have the good matching id on latest. I explain it :
I have 3 tables :
rooms_messages
| id | room_uuid | discussion_message_uuid | created_at |
|---|---|---|---|
| 1 | 10 | 101 | 2024-07-16 12:30:45 |
| 2 | 20 | 102 | 2024-07-16 12:30:50 |
| 3 | 10 | 103 | 2024-07-16 12:32:45 |
| 4 | 20 | 104 | 2024-07-16 12:34:50 |
| 5 | 20 | 105 | 2024-07-16 12:36:50 |
discussions_messages
| id | text |
|---|---|
| 101 | Hello |
| 102 | Test |
| 103 | Ok |
| 104 | Wow |
| 105 | Hello2 |
discussions_replies
| id | discussion_message_uuid | text |
|---|---|---|
| 201 | 101 | Hello |
| 202 | 101 | Test |
| 203 | 102 | Ok |
| 204 | 102 | Wow |
I use this request from /a/38505618, and it works to list the room_messages by created_at order by. But I don’t have the correct rooms_messages.uuid:
<code>select URL, max(DateVisited)
from <table>
group by URL
</code>
<code>select URL, max(DateVisited)
from <table>
group by URL
</code>
select URL, max(DateVisited)
from <table>
group by URL
My query:
<code>SELECT rm.uuid as uuid, max(rm.created_at) as created_at, rm.room_uuid as room_uuid, rm.discussion_message_uuid as discussion_message_uuid, dm.text, count(dm.uuid) as replies,
FROM rooms_messages AS rm
INNER JOIN discussions_messages AS dm ON rm.discussion_message_uuid = dm.uuid
LEFT OUTER JOIN discussions_replies AS dr ON dm.uuid = dr.discussion_message_uuid
GROUP BY room_uuid
</code>
<code>SELECT rm.uuid as uuid, max(rm.created_at) as created_at, rm.room_uuid as room_uuid, rm.discussion_message_uuid as discussion_message_uuid, dm.text, count(dm.uuid) as replies,
FROM rooms_messages AS rm
INNER JOIN discussions_messages AS dm ON rm.discussion_message_uuid = dm.uuid
LEFT OUTER JOIN discussions_replies AS dr ON dm.uuid = dr.discussion_message_uuid
GROUP BY room_uuid
</code>
SELECT rm.uuid as uuid, max(rm.created_at) as created_at, rm.room_uuid as room_uuid, rm.discussion_message_uuid as discussion_message_uuid, dm.text, count(dm.uuid) as replies,
FROM rooms_messages AS rm
INNER JOIN discussions_messages AS dm ON rm.discussion_message_uuid = dm.uuid
LEFT OUTER JOIN discussions_replies AS dr ON dm.uuid = dr.discussion_message_uuid
GROUP BY room_uuid
But I have 3 problems :
- The rm.uuid isn’t correct : it’s not the latest
- Consequence: the dm.text isn’t the latest because the rm.uuid isn’t the last
- Consequence: I don’t load the replies
1
Your current query is invalid, and would not even run on MySQL with the ONLY_FULL_GROUP_BY flag enabled (which it should be). We can use ROW_NUMBER() here for a proper solution:
<code>WITH cte AS (
SELECT rm.uuid as uuid, rm.created_at, rm.room_uuid,
rm.discussion_message_uuid, dm.text,
ROW_NUMBER() OVER (PARTITION BY rm.room_uuid ORDER BY rm.created_at DESC) rn
FROM rooms_messages AS rm
INNER JOIN discussions_messages AS dm ON rm.discussion_message_uuid = dm.uuid
LEFT JOIN discussions_replies AS dr ON dm.uuid = dr.discussion_message_uuid
)
SELECT uuid, created_at, room_uuid, discussion_message_uuid, text
FROM cte
WHERE rn = 1;
</code>
<code>WITH cte AS (
SELECT rm.uuid as uuid, rm.created_at, rm.room_uuid,
rm.discussion_message_uuid, dm.text,
ROW_NUMBER() OVER (PARTITION BY rm.room_uuid ORDER BY rm.created_at DESC) rn
FROM rooms_messages AS rm
INNER JOIN discussions_messages AS dm ON rm.discussion_message_uuid = dm.uuid
LEFT JOIN discussions_replies AS dr ON dm.uuid = dr.discussion_message_uuid
)
SELECT uuid, created_at, room_uuid, discussion_message_uuid, text
FROM cte
WHERE rn = 1;
</code>
WITH cte AS (
SELECT rm.uuid as uuid, rm.created_at, rm.room_uuid,
rm.discussion_message_uuid, dm.text,
ROW_NUMBER() OVER (PARTITION BY rm.room_uuid ORDER BY rm.created_at DESC) rn
FROM rooms_messages AS rm
INNER JOIN discussions_messages AS dm ON rm.discussion_message_uuid = dm.uuid
LEFT JOIN discussions_replies AS dr ON dm.uuid = dr.discussion_message_uuid
)
SELECT uuid, created_at, room_uuid, discussion_message_uuid, text
FROM cte
WHERE rn = 1;
2