Para simplificar las cosas, tengo dos tablas para un chatbox: Conversation y Message
| identificación | estado |
|---|---|
| 1 | abierto |
| 2 | abierto |
| idConversación | texto | fecha |
|---|---|---|
| 1 | 'ffff' | (fecha aleatoria) |
| 1 | 'asf' | (fecha aleatoria) |
| 1 | '3123123123' | (fecha aleatoria) |
| 2 | 'asdfasdff' | (fecha aleatoria) |
| 2 | 'asdfasdfcas' | (fecha aleatoria) |
| 2 | 'asdfasdfasdf' | (fecha aleatoria) |
Puedo seleccionar toda la Conversation con bastante facilidad haciendo:
await Conversation.query().where({ status: 'open', })Pero estoy tratando de unirlos en una sola consulta para obtener 1 mensaje por conversación. Esta es la consulta que tengo ahora mismo:
await Conversation.query() .where({ status: 'open', }) .innerJoin('Message', 'Message.idConversation', 'Conversation.id') .distinct('Message.idConversation') .select('Message.idConversation', 'Message.text')Pero esto está dando un resultado como:
[ { idConversation: 1, text: 'ffff' }, { idConversation: 1, text: 'asdf' }, { idConversation: 1, text: '3123123123' }, .... ]Solo me gustaría un mensaje por identificación de conversación, como:
{ idConversation: 1, text: 'ffff' }, { idConversation: 2, text: 'asdfasdff' },¿Cómo podría solucionar esta consulta?
Esto es crudo:
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 )Está tratando de resolver el problema del "máximo por grupo".
Una forma de hacerlo es mediante una subconsulta correlacionada, es decir, "seleccione el mensaje donde la fecha es igual a (la fecha más reciente de todos los mensajes en esta conversación)" . Esto funciona siempre que su aplicación garantice que dos mensajes en la misma conversación no pueden tener la misma fecha.
En knex esto
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')da como resultado un SQL como este (tenga en cuenta los alias de la tabla)
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)