CODEWITHSUNDEEP
sequelize.js
Gerald Allay
Gerald Allay

Reputation: 25

Sequelize nested where clause from corresponding SQL

Given the following SQL query

SELECT *
FROM `table_name`
WHERE 
    (`year` > YEAR('2021-01-01') OR 
     (`year` = YEAR('2021-01-01') AND 
     `weekNumber` >= WEEK('2021-01-01'))
    )
    AND (`year` < YEAR('2021-10-11') OR 
         (`year` = YEAR('2021-10-11') AND 
         `weekNumber` <= WEEK('2021-10-11')))

How would you generate the same query using Sequelize ORM? I am using Sequelize V.6

Upvotes: 0

Views: 609

Answers (1)

Anatoly
Anatoly

Reputation: 22758

That's easy using Op.and, Op.or and Sequelize.fn like this:

const rows = await Model.findAll({
  where: {
   [Op.and]: [
    {
    [Op.or]: [{
       year: {
         [Op.gt]: Sequelize.fn('YEAR', '2021-01-01')
       }
    }, {
       year: Sequelize.fn('YEAR', '2021-01-01'),
       weekNumber: {
         [Op.gte]: Sequelize.fn('WEEK', '2021-01-01')
       }
    }
    ]
   },
    {
    [Op.or]: [{
       year: {
         [Op.lt]: Sequelize.fn('YEAR', '2021-10-11')
       }
    }, {
       year: Sequelize.fn('YEAR', '2021-10-11'),
       weekNumber: {
         [Op.lte]: Sequelize.fn('WEEK', '2021-10-11')
       }
    }
    ]
   }
   ]
  }
})

Upvotes: 1

Related Questions