To make things simple, I have two tables for a chatbox: Conversation and Message

Conversation

Message

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
  )

CodePudding user response：

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)