To make things simple, I have two tables for a chatbox: Conversation and Message
| id | status |
|---|---|
| 1 | open |
| 2 | open |
| idConversation | text | date |
|---|---|---|
| 1 | 'ffff' | (random date) |
| 1 | 'asdf' | (random date) |
| 1 | '3123123123' | (random date) |
| 2 | 'asdfasdff' | (random date) |
| 2 | 'asdfasdfcas' | (random date) |
| 2 | 'asdfasdfasdf' | (random date) |
I can select all the Conversation easily enough by doing:
await Conversation.query().where({
status: 'open',
})
But I am trying to join these together into one query to get 1 message per conversation. This is the query I have for it right now:
await Conversation.query()
.where({
status: 'open',
})
.innerJoin('Message', 'Message.idConversation', 'Conversation.id')
.distinct('Message.idConversation')
.select('Message.idConversation', 'Message.text')
But this is yielding a result as:
[
{
idConversation: 1,
text: 'ffff'
},
{
idConversation: 1,
text: 'asdf'
},
{
idConversation: 1,
text: '3123123123'
},
....
]
I would just like one message per conversation id such as:
{
idConversation: 1,
text: 'ffff'
},
{
idConversation: 2,
text: 'asdfasdff'
},
How could I fix this query?
This is it raw:
SELECT u.id, p.text
FROM Conversation AS u
INNER JOIN Message AS p ON p.id = (
SELECT id
FROM Message AS p2
WHERE p2.idConversation = u.id
LIMIT 1
)
You're trying to solve the "max per group" problem.
One way to do this is by correlated subquery, i.e. "select the message where the date equals (the latest date of all messages in this conversation)". This works as long as it's guaranteed by your application that no two messages in the same conversation can have the same date.
In knex this
knex({c: 'Conversation'})
.innerJoin({m: 'Message'}, 'm.idConversation', 'c.id')
.where({
'c.status': 'open',
'm.date': knex('message')
.where('idConversation', '=', knex.raw('c.id'))
.max('date')
})
.select('m.idConversation', 'm.text')
results in SQL like this (note the table aliases)
select
m.idConversation,
m.text
from
Conversation as c
inner join Message as m on m.idConversation = c.id
where
c.status = 'open'
and m.date = (select max(date) from message where idConversation = c.id)