How can I convert SQL-query to Sequelize?

I have this SQL query:

SELECT * 
FROM chats 
JOIN (SELECT * 
      FROM messages 
      WHERE messages.id 
      IN (SELECT MAX(messages.id) 
         FROM messages 
         GROUP BY messages.chatId)) 
      AS lastMessage 
      ON chats.id = lastMessage.chatId
WHERE chatGroupId = 1
ORDER BY lastMessage.createdAt DESC

It returns the last message in the given chat.

But I don’t understand at all how to execute this moment IN (SELECT MAX(messages.id) FROM messages GROUP BY messages.chatId)) AS lastMessage ON chats.id = lastMessage.chatId in Sequelize…

Answer

You can use Sequelize.literal like this:

const message = await Chats.findAll({
  where: {
    chatGroupId: 1
  },
  include: [{
    model: Messages,
    required: true,
    where: Sequelize.where(
      Seqeulize.col('messages.id'),
      Op.in,
      Sequelize.literal('(SELECT MAX(m2.id) FROM messages as m2 WHERE m2.chatId = chat.id)')
    )
  }]
})