Node use ObjectionJS and Knex, 1 to many, return first related row from the many table

Question

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

Conversation

id	status
1	open
2	open

Message

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
  )

Sounds like your question is *"How to use `limit` in knex?"* — Tomalak, Oct 17 '21 at 06:46
That just limits the query to return one Conversation when it should be all where `status = open` — qaakmnd, Oct 17 '21 at 07:24
And which message from each conversation do you want to return, exactly? — Tomalak, Oct 17 '21 at 07:30
It should be every `Conversation` where `status = open` returned with the `Message.text` of most recent date from the `Message.date` column — qaakmnd, Oct 17 '21 at 07:34

score 2 · Accepted Answer · answered Oct 17 '21 at 07:46

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)

Node use ObjectionJS and Knex, 1 to many, return first related row from the many table

Conversation

Message

1 Answers1