Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sequelize Where statement with date

I am using Sequelize as my backend ORM. Now I wish to do some WHERE operations on a Date.

More specifically, I want to get all data where a date is between now and 7 days ago.

The problem is that the documentation does not specify which operations you can do on Datatypes.DATE

Can anyone point me in the right direction?

like image 375
Marc Rasmussen Avatar asked Apr 22 '15 12:04

Marc Rasmussen


People also ask

How do you use greater than in Sequelize?

Greater than or equal Operator ( >= ) The Greater than or equal Operator same like Greater than Operator but also check same value or not. In sequelize we use Op. gte to get same or greater value in search.


2 Answers

Just like Molda says, you can use $gt, $lt, $gte or $lte with a date:

model.findAll({   where: {     start_datetime: {       $gte: moment().subtract(7, 'days').toDate()     }   } }) 

If you're using v5 of Sequelize, you've to include Op because the key was moved into Symbol

const { Op } = require('sequelize')  model.findAll({   where: {     start_datetime: {       [Op.gte]: moment().subtract(7, 'days').toDate()     }   } }) 

See more sequelize documentation here

like image 69
Evan Siroky Avatar answered Sep 30 '22 19:09

Evan Siroky


I had to import the Operators symbols from sequelize and use like so.

const { Op } = require('sequelize')  model.findAll({   where: {     start_datetime: {       [Op.gte]: moment().subtract(7, 'days').toDate()     }   } }) 

According to the docs, for security reasons this is considered best practise.

See http://docs.sequelizejs.com/manual/tutorial/querying.html for more info.

Using Sequelize without any aliases improves security. Some frameworks automatically parse user input into js objects and if you fail to sanitize your input it might be possible to inject an Object with string operators to Sequelize.

(...)

For better security it is highly advised to use Sequelize.Op and not depend on any string alias at all. You can limit alias your application will need by setting operatorsAliases option, remember to sanitize user input especially when you are directly passing them to Sequelize methods.

like image 35
James Graham Avatar answered Sep 30 '22 20:09

James Graham