1//Sequelize provides several operators.
2
3const { Op } = require("sequelize");
4Post.findAll({
5 where: {
6 [Op.and]: [{ a: 5 }, { b: 6 }], // (a = 5) AND (b = 6)
7 [Op.or]: [{ a: 5 }, { b: 6 }], // (a = 5) OR (b = 6)
8 someAttribute: {
9 // Basics
10 [Op.eq]: 3, // = 3
11 [Op.ne]: 20, // != 20
12 [Op.is]: null, // IS NULL
13 [Op.not]: true, // IS NOT TRUE
14 [Op.or]: [5, 6], // (someAttribute = 5) OR (someAttribute = 6)
15
16 // Using dialect specific column identifiers (PG in the following example):
17 [Op.col]: 'user.organization_id', // = "user"."organization_id"
18
19 // Number comparisons
20 [Op.gt]: 6, // > 6
21 [Op.gte]: 6, // >= 6
22 [Op.lt]: 10, // < 10
23 [Op.lte]: 10, // <= 10
24 [Op.between]: [6, 10], // BETWEEN 6 AND 10
25 [Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
26
27 // Other operators
28
29 [Op.all]: sequelize.literal('SELECT 1'), // > ALL (SELECT 1)
30
31 [Op.in]: [1, 2], // IN [1, 2]
32 [Op.notIn]: [1, 2], // NOT IN [1, 2]
33
34 [Op.like]: '%hat', // LIKE '%hat'
35 [Op.notLike]: '%hat', // NOT LIKE '%hat'
36 [Op.startsWith]: 'hat', // LIKE 'hat%'
37 [Op.endsWith]: 'hat', // LIKE '%hat'
38 [Op.substring]: 'hat', // LIKE '%hat%'
39 [Op.iLike]: '%hat', // ILIKE '%hat' (case insensitive) (PG only)
40 [Op.notILike]: '%hat', // NOT ILIKE '%hat' (PG only)
41 [Op.regexp]: '^[h|a|t]', // REGEXP/~ '^[h|a|t]' (MySQL/PG only)
42 [Op.notRegexp]: '^[h|a|t]', // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)
43 [Op.iRegexp]: '^[h|a|t]', // ~* '^[h|a|t]' (PG only)
44 [Op.notIRegexp]: '^[h|a|t]', // !~* '^[h|a|t]' (PG only)
45
46 [Op.any]: [2, 3], // ANY ARRAY[2, 3]::INTEGER (PG only)
47
48 // In Postgres, Op.like/Op.iLike/Op.notLike can be combined to Op.any:
49 [Op.like]: { [Op.any]: ['cat', 'hat'] } // LIKE ANY ARRAY['cat', 'hat']
50
51 // There are more postgres-only range operators, see below
52 }
53 }
54});
1// Example
2const Tokens = db.define('tokens', {
3 guid: {
4 type: sequelize.STRING
5 }
6});
7// The basics of Sequelize Get Where
8Tokens.findAll({
9 where: { guid: 'guid12345' }
10 }).then(tokens => {
11 console.log(tokens);
12 }).catch(err => console.log('error: ' + err));;
13// is equal to >> SELECT * FROM Tokens WHERE guid = 'guid12345'
1await Tag.findAll({
2 where: {
3 id: {
4 [Sequelize.Op.in]: [1, 2, 3, 4]
5 }
6 }
7});
8