CODEWITHSUNDEEP
sqlsequelize.js
Alexei
Alexei

Reputation: 97

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...

Upvotes: 1

Views: 3942

Answers (2)

shepherdly
shepherdly

Reputation: 1

Writing a sql query may not always be very simple with sequelize functions. Sometimes I recommend to run your plain sql query by combining it with this function.

const { QueryTypes } = require('sequelize');

async message_page (req,res) {  
      const messagePage = await db.query("SELECT * FROM", { type: QueryTypes.SELECT });
      return messagePage;},

Upvotes: 0

Anatoly
Anatoly

Reputation: 22758

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)')
    )
  }]
})

Upvotes: 2

Related Questions