CODEWITHSUNDEEP
javascriptmysqlsequelize.js
Gabriel
Gabriel

Reputation: 59

Convert MySQL "where" to Sequelizejs "where"

I was trying to write "WHERE (CASE ... THEN ... ELSE ... END) > 0" to sequelize v3.33 but couldn't find the solution yet.

Had tried sequelize.literal('...') but didn't work. Using "HAVING" is one of the solutions but it's no good for performance-wise for large data extraction and it's twice as slow.

This is just an example of MySQL code but pretty much close to what I want to achieve.

SELECT
(CASE `a`.`fee` IS NULL 
  THEN `a.b`.`fee` 
  ELSE `a`.`fee`
END) AS `_fee`
FROM `a`
WHERE
(CASE `a`.`fee` IS NULL 
  THEN `a.b`.`fee` 
  ELSE `a`.`fee`
END) > 0 AND 
(created_at > currentDate 
  AND 
created_at < futureDate)

I want to convert this to sequelize. Below is as far as I can go, I don't know how to add that case closure.

models.a.findAll({
  ...
  where: {
    created_at: { $gt: startDate, $lt: endDate }
  }
})

*** Don't mind about created_at, it's just an example.

Upvotes: 1

Views: 50

Answers (1)

Vivek Doshi
Vivek Doshi

Reputation: 58543

You can use sequelize.where and sequelize.literal for that :

where:{
    $and : [
        { created_at: { $gt: startDate, $lt: endDate } },
        sequelize.where(sequelize.literal('(CASE `a`.`fee` IS NULL THEN `a.b`.`fee` ELSE `a`.`fee` END)') , { $gt : 0 } )
    ]
}

Note : this might not work as alias a. of the table might be diff, you can debug and change as per your query

Upvotes: 1

Related Questions