CODEWITHSUNDEEP
javascriptsequelize.js
ryanwaite28
ryanwaite28

Reputation: 2038

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_name like ?", '%' + request.body.query + '%'] })
  .then(function(assets){
    return response.json({
      msg: 'search results',
      assets: assets
    });
  })
  .catch(function(error){
    console.log(error);
  });

but I get the following error:

   { error: operator does not exist: character varying @> unknown
       at Connection.parseE (/home/travellr/safe-star.com/SafeStar/node_modules/pg/lib/connection.js:554:11)
       at Connection.parseMessage (/home/travellr/safe-star.com/SafeStar/node_modules/pg/lib/connection.js:381:17)
       at Socket.<anonymous> (/home/travellr/safe-star.com/SafeStar/node_modules/pg/lib/connection.js:117:22)
       at emitOne (events.js:96:13)
       at Socket.emit (events.js:188:7)
       at readableAddChunk (_stream_readable.js:176:18)
       at Socket.Readable.push (_stream_readable.js:134:10)
       at TCP.onread (net.js:548:20)
     name: 'error',
     length: 209,
     severity: 'ERROR',
     code: '42883',
     detail: undefined,
     hint: 'No operator matches the given name and argument type(s). You might need to add explicit type casts.',
     position: '246',
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'parse_oper.c',
     line: '722',
     routine: 'op_error',
     sql: 'SELECT "id", "asset_name", "asset_code", "asset_icon", "asset_background", "asset_add_view", "asset_add_script", "asset_add_id_regex", "date_created", "uniqueValue", "createdAt", "updatedAt" FROM "assets" AS "assets" WHERE "assets"."asset_name" @> \'%a%\' LIMIT 10;' },
  sql: 'SELECT "id", "asset_name", "asset_code", "asset_icon", "asset_background", "asset_add_view", "asset_add_script", "asset_add_id_regex", "date_created", "uniqueValue", "createdAt", "updatedAt" FROM "assets" AS "assets" WHERE "assets"."asset_name" @> \'%a%\' LIMIT 10;' }

How do you use a contains query in sequelize?

Upvotes: 27

Views: 69872

Answers (4)

Rilwan Aribidesi
Rilwan Aribidesi

Reputation: 11

const sequelize = require('sequelize');

const keyword = "John";
User.findAll({
    where: {
        firstName: { [Op.regexp]: keyword };
    }
})

Upvotes: 1

Ajay Prajapati
Ajay Prajapati

Reputation: 192

// 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() + '%')
        }
    })

Upvotes: 5

Marko Balažic
Marko Balažic

Reputation: 556

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

Upvotes: 35

piotrbienias
piotrbienias

Reputation: 7401

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.

Upvotes: 49

Related Questions