从相应的SQL继承嵌套where子句

Question

给定以下SQL查询

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

如何使用后缀ORM生成相同的查询？我使用的是续写版本6

Answer 1

这样很容易使用Op.and、Op.or和Sequelize.fn：

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