You Can Use Arrays In Field Equality Checks Within A Sequelize WHERE Clause In Node.js
I'm still quite new to the Sequelize library, which is an ORM (Object-Relational Mapping) tool for Node.js applications. As such, there's still a lot for me to learn. But, the other day, I was reading through code written by my InVision teammate Casey Flynn when I saw a Sequelize construct that I had not seen in the documentation. Casey was using an array of values in a field equality check within a Sequelize WHERE clause. Traditionally, I've been using the special "$in" operator for such conditions. But, using an array of values directly in the field comparison would make the whole filtering condition much more straightforward.
Even now, when I look in the Sequelize documentation, I cannot find any reference to being able to use an array of values for such an operation. Perhaps I'm not looking in the right place; or, perhaps this feature was simply omitted; or, maybe it's just obvious to everyone else and doesn't need documenting? But, since it wasn't obvious to me, I think it's worth sharing.
To see this in action, let's create a .findAll() query in which we pass an Array of ID values to both a direct field comparison and an "$in" operator:
// Require the core node modules.
var chalk = require( "chalk" );
// Require the application modules.
var FriendModel = require( "./FriendModel" );
// ----------------------------------------------------------------------------------- //
// ----------------------------------------------------------------------------------- //
FriendModel
.findAll({
where: {
$or: [
// To demonstrate that we can use an array of values when querying
// against a specific field, we'll create a compound WHERE clause that
// includes both a direct field comparisons as well as the special $in
// operator. This way, we can compare the generated SQL.
{
id: [ 1, 2, 3 ] // Direct field comparison.
},
{
id: [] // Direct field comparison.
},
{
id: {
$in: [ 4, 5, 6 ]
}
}
]
}
})
.then(
function handleResolve( friends ) {
console.log( chalk.bold( "Friends:" ) );
console.log( unwrap( friends ) );
}
)
;
// ----------------------------------------------------------------------------------- //
// ----------------------------------------------------------------------------------- //
// I convert an array of Sequelize instances into an array of plain-old objects.
function unwrap( records ) {
var plainRecords = records.map(
function operator( record ) {
return( record.get({ plain: true }) );
}
);
return( plainRecords );
}
As you can see, I'm constructing a compound WHERE clause in which I'm using both a populated and an unpopulated array of values in a place where I would normally use a simple value comparison. However, when we run this code through Node.js, we get the following terminal output:
It worked like a charm! And, as you can see from the Sequelize logging, using an Array of values in a direct field comparison generates the same kind of filter clause that the $in operator generates. This is awesome! This means that we can skip creating the nested object and write Sequelize filtering clauses that are much easier to read.
Want to use code from this post? Check out the license.
Reader Comments
Discovering this was a God send!
@Justin,
Glad you like -- I also thought this was awesome to come across!
@Ben,
Check this out...my handle under Github is knoxcard.
https://github.com/sequelize/sequelize/issues/9195
How to properly setup a multiple LIKE condition, this was driving me nuts.
where: {
[Sequelize.Op.and]: [
{
name: {
[Sequelize.Op.like]: ['%tree%']
}
}, {
name: {
[Sequelize.Op.like]: ['%root%']
}
}
]
}
@Justin,
Very interesting. I've never seen the "[Sequelize.Op.X]" notation before. That must be part of a newer ES6-based API. I haven't actually used Sequelize in a bit, as I have moved to another team within the company that is not using it. Thanks for the tip!