Sequelize: Find All That Match Contains (Case Insensitive)
Assets.findAll({
limit: 10,
where: {
asset_name: {
[Op.like]: '%' + request.body.query + '%'
}
}
}).then(function(assets){
return response.json({
msg: 'search results',
assets: assets
});
}).catch(function(error){
console.log(error);
});
EDIT
In order to make it case insensitive, you could use the LOWER
sql function, but previously you would also have to lower case your request.body.query
value. Sequelize query would then look like that
let lookupValue = request.body.query.toLowerCase();
Assets.findAll({
limit: 10,
where: {
asset_name: sequelize.where(sequelize.fn('LOWER', sequelize.col('asset_name')), 'LIKE', '%' + lookupValue + '%')
}
}).then(function(assets){
return response.json({
msg: 'message',
assets: assets
});
}).catch(function(error){
console.log(error);
});
What it does is to lower case your asset_name
value from table, as well as lower case the request.body.query
value. In such a case you compare two lower cased strings.
In order to understand better what is happening in this case I recommend you take a look at the sequelize documentation concerning sequelize.where()
, sequelize.fn()
as well as sequelize.col()
. Those functions are very useful when trying to perform some unusual queries rather than simple findAll
or findOne
.
The sequelize
in this case is of course your Sequelize instance.
// search case insensitive nodejs usnig sequelize
const sequelize = require('sequelize');
let search = "Ajay PRAJAPATI"; // what ever you right here
userModel.findAll({
where: {
firstname: sequelize.where(sequelize.fn('LOWER', sequelize.col('firstname')), 'LIKE', '%' + search.toLowerCase() + '%')
}
})
If you are using sequlize with Postgres better to use [Op.iLike]: `%${request.body.query}%`
and you can forget about the sequlize functions.