Get the latest message from each user in a table

Say my messages table looks something like this:

content|sender_id|recipient_id|sent_at_timestamp
hello|1|2|yyyy-mm-dd # message 1
greetings|2|1|yyyy-mm-dd # message 2
bonjour|1|3|yyyy-mm-dd # message 3

content|sender_id|recipient_id|sent_at_timestamp
hello|1|2|yyyy-mm-dd # message 1
greetings|2|1|yyyy-mm-dd # message 2
bonjour|1|3|yyyy-mm-dd # message 3

This means user 1 has sent a greeting to user 2, and user 2 has responded. User 1 has written to user 3, who has not responded. This means that the latest messages in all conversations for user 1 should be messages 2 and 3. This can then grow exponentially. But what I want is to get the LATEST message in any conversation, regardless of if you are the sender or recipient. How would I write a query for this? Using plain SQL it's been suggested to use a CTE but how would I do it in Drizzle?

9 Replies

xamarotOP•16mo ago

Right now my query is

const m = (await db
        .select({
            sender: auth_user.username,
            content: messages.content,
            sentAt: messages.sentAt
        })
        .from(messages)
        .leftJoin(auth_user, eq(auth_user.id, messages.sender))
        .where(
            or(
                eq(messages.recipient, user.userId),
                eq(messages.sender, user.userId)
            )
        ).orderBy(messages.sentAt));

const m = (await db
        .select({
            sender: auth_user.username,
            content: messages.content,
            sentAt: messages.sentAt
        })
        .from(messages)
        .leftJoin(auth_user, eq(auth_user.id, messages.sender))
        .where(
            or(
                eq(messages.recipient, user.userId),
                eq(messages.sender, user.userId)
            )
        ).orderBy(messages.sentAt));

but this gives me all messages, like:

{
    sender: 'test2',
    content: "Hello",
    sentAt: 2023-08-10T07:07:20.000Z
  },
  {
    sender: 'test',
    content: 'Hola',
    sentAt: 2023-08-10T10:33:00.000Z
  },
  ... // more messages omitted

{
    sender: 'test2',
    content: "Hello",
    sentAt: 2023-08-10T07:07:20.000Z
  },
  {
    sender: 'test',
    content: 'Hola',
    sentAt: 2023-08-10T10:33:00.000Z
  },
  ... // more messages omitted

it seems to me like I should add some sort of "distinct" filter for the "sender"?

Asseater Peter•16mo ago

Do you want to get back an array of messages, or users with one message each?

xamarotOP•16mo ago

Users with one message each, ideally, but either way really

Asseater Peter•16mo ago

Oh! I think that's easier, actually. I might have some time Soon™ to take a look and see if I can get you the answer Until then, you should try db.query stuff with relations You might be able to figure it out yourself

xamarotOP•16mo ago

Yeah i tried using relational queries as well but couldn't figure it out

Asseater Peter•16mo ago

Ok so I got to the PC (with no delay whatsoever) and I made a project with your config to test. However, I need a bit more clarification. Do you want to get back an an array of users with the latest message they sent, or the latest message they received? Or the latest of both? My bad actually, you stated clearly that you want the latest option You should probably do that with extras and the magic sql operator thing

xamarotOP•16mo ago

Hmm, not f familiar with that one. I will check out the docs.

Asseater Peter•16mo ago

So, I tried doing this exact thing, but it always overcomplicates the solution so so much. I think the best solution is probably just

db.query.UserTable.findMany({
      with: {
        sent: {
          orderBy: (MessageTable, { desc }) => desc(MessageTable.timestamp),
          limit: 1,
        },
        received: {
          orderBy: (MessageTable, { desc }) => desc(MessageTable.timestamp),
          limit: 1,
        },
      },
    })

db.query.UserTable.findMany({
      with: {
        sent: {
          orderBy: (MessageTable, { desc }) => desc(MessageTable.timestamp),
          limit: 1,
        },
        received: {
          orderBy: (MessageTable, { desc }) => desc(MessageTable.timestamp),
          limit: 1,
        },
      },
    })

And I defined my relations like so:

export const MessageRelations = relations(MessageTable, ({ one }) => ({
  sender: one(UserTable, {
    fields: [MessageTable.sender_id],
    references: [UserTable.id],
    relationName: 'MessageSender',
  }),

  recipient: one(UserTable, {
    fields: [MessageTable.recipient_id],
    references: [UserTable.id],
    relationName: 'MessageReceiver',
  }),
}))

export const UserRelations = relations(UserTable, ({ many }) => ({
  sent: many(MessageTable, { relationName: 'MessageSender' }),
  received: many(MessageTable, { relationName: 'MessageReceiver' }),
}))

export const MessageRelations = relations(MessageTable, ({ one }) => ({
  sender: one(UserTable, {
    fields: [MessageTable.sender_id],
    references: [UserTable.id],
    relationName: 'MessageSender',
  }),

  recipient: one(UserTable, {
    fields: [MessageTable.recipient_id],
    references: [UserTable.id],
    relationName: 'MessageReceiver',
  }),
}))

export const UserRelations = relations(UserTable, ({ many }) => ({
  sent: many(MessageTable, { relationName: 'MessageSender' }),
  received: many(MessageTable, { relationName: 'MessageReceiver' }),
}))

Then you just have latest of each, while obviously still only executing one query (db.query always sends one query only) I tried doing something along the lines of

db.query.UserTable.findMany({
      with: {
        messages: {
          where: (MessageTable, { or, eq }) =>
            or(
              eq(MessageTable.recipient_id, UserTable),
              eq(MessageTable.sender_id, UserTable)
            ),
          orderBy: (MessageTable, { desc }) => desc(MessageTable.timestamp),
          limit: 1,
        },
      },
    })

db.query.UserTable.findMany({
      with: {
        messages: {
          where: (MessageTable, { or, eq }) =>
            or(
              eq(MessageTable.recipient_id, UserTable),
              eq(MessageTable.sender_id, UserTable)
            ),
          orderBy: (MessageTable, { desc }) => desc(MessageTable.timestamp),
          limit: 1,
        },
      },
    })

However, because messages in the user table are defined as only relating to the sender_id, this couldn't work.

xamarotOP•16mo ago

I will look at it once I get a chance. Thank you! I restructured my database a bit since it was a bitch to work with the way I wanted it to, but using relational queries I finally got it working. Thank you!

Gaming

Programming

Get the latest message from each user in a table