Retrieve conversation by latest message with Sequelize

[guillaumeboussion]

Hi,

First, thank you for providing and maintaining this package..!

I've been trying for 3 days now to find a solution for my project. I'm building an app having a chat feature. To display latest conversations on screen, I need to order conversations by their latest message, and get the latest message (imagine a classic chat app such as Instagram, Facebook, Whatsapp..).

So, here's the architecture used to build this :

Room

• id
  -messages : HasMany(() => Messages)
• profiles : BelongsToMany(() => Profile, () => ProfileRoom)

Profile

• id
• rooms : BelongsToMany(() => Room, () => ProfileRoom)

ProfileRoom

• profiles: @BelongsTo(() => Profile)
• messages: @BelongsTo(() => Message)

In this, I need to get all Room associated to Profile (I'd like to paginate, to avoid performance issues on client side), and query the latest Message associated to Room. But I can't limit my association query without having it being executed in a separate query, thus.. not ordering final query by latest message (and pagination doesn't allow me to order it afterwards).

What would be the simplest way to achieve this result ?

Here's what I tried

public async list(profileId: string): Promise<Room[]> {
    const roomIds = await this.profileRoomService.findConversationProfiles(profileId);
    
    return this.room.findAll({
      include: [
        {
          model: Profile,
        },
        {
          model: Message,
          limit: 1,
          subQuery: false,
          order: [["created_at", "DESC"]],
        },
      ],
      where: {
        id: [roomIds.map((room) => room.roomId)],
      },
      limit: 20,
      logging: true,
    });
  }

[guillaumeboussion]

Ok so I achieved what I wanted by doing this, not sure it's the most performant way to process it, but still works.. Is there a better query to get what I want ?

const roomIds = await this.profileRoomsService.findConversationProfiles(profileId);

const rooms = await this.room.findAll({
      attributes: [
        "id",
        "createdAt",
        "updatedAt",
        [
          sequelize.literal(`(
            SELECT
              MAX(created_at)
            FROM
              messages AS message
            WHERE
              message.room_id = Room.id
          )`),
          "latestMessage",
        ],
      ],

      include: [
        {
          model: Profile,
          attributes: ["id"],
          include: [
            { model: Picture, order: [["position", "ASC"]], limit: 1 },
            { model: User, attributes: ["id", "displayName"] },
          ],
        },
        {
          model: Message,
          limit: 1,
          order: [["createdAt", "DESC"]],
        },
      ],
      where: {
        id: [roomIds.map((room) => room.roomId)],
      },
      limit: 20,
      order: [[sequelize.literal("latestMessage"), "DESC"]],
    });