Sequelize: Find All That Match Contains (case Insensitive)

I want to use sequelize.js to query a model for records with a contains restraint. How do I do that? This is what I have right now: Assets .findAll({ limit: 10, where: ['asset_na

Solution 1:

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.

Solution 2:

If you are using sequlize with Postgres better to use [Op.iLike]: `%${request.body.query}%` and you can forget about the sequlize functions.

Solution 3:

// search case insensitive nodejs usnig sequelizeconst 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() + '%')
        }
    })