Sequelize query - compare dates in two columns
I have a table in Sequelize with two date columns i.e. with:
var Visit = sequelize.define("Visit", {
/*...*/
scheduleEndDate: {
type: DataTypes.DATE
}
actualEndDate: {
type: DataTypes.DATE
}
/*...*/
});
I want to make a query that returns rows where actualEndDate is before schedEndDate - and cannot get the format to the right. What I tried for where
part of my request findAll
:
where: { actualEndDate: { lt: Visit.scheduleEndDate } }
- throws an error message because the visit is undefined (also tried with this.scheduleEndDate - also throws an error)
where: { actualEndDate: '< scheduleEndDate' }
- matches the string actualEndDate with the string '<scheduleEndDate
Do I need to define an instance method for date comparison / best solution?
+4
source to share
2 answers
In the latest version (v5) you need to use Sequelize.Op
. Documents
const Op = Sequelize.Op;
...
where: {
actualEndDate: {
[Op.lt]: sequelize.col('scheduleEndDate')
}
}
All operations:
Project.findAll({
where: {
id: {
[Op.and]: {a: 5}, // AND (a = 5)
[Op.or]: [{a: 5}, {a: 6}], // (a = 5 OR a = 6)
[Op.gt]: 6, // id > 6
[Op.gte]: 6, // id >= 6
[Op.lt]: 10, // id < 10
[Op.lte]: 10, // id <= 10
[Op.ne]: 20, // id != 20
[Op.between]: [6, 10], // BETWEEN 6 AND 10
[Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
[Op.in]: [1, 2], // IN [1, 2]
[Op.notIn]: [1, 2], // NOT IN [1, 2]
[Op.like]: '%hat', // LIKE '%hat'
[Op.notLike]: '%hat', // NOT LIKE '%hat'
[Op.iLike]: '%hat', // ILIKE '%hat' (case insensitive) (PG only)
[Op.notILike]: '%hat', // NOT ILIKE '%hat' (PG only)
[Op.overlap]: [1, 2], // && [1, 2] (PG array overlap operator)
[Op.contains]: [1, 2], // @> [1, 2] (PG array contains operator)
[Op.contained]: [1, 2], // <@ [1, 2] (PG array contained by operator)
[Op.any]: [2,3] // ANY ARRAY[2, 3]::INTEGER (PG only)
},
status: {
[Op.not]: false // status NOT FALSE
}
}
})
0
source to share