Node Sequelize find where $like wildcard

user_3935218 0
Viewed 50566

I am trying to add a where like clause to a Node Sequelize findAll, to behave like the sql query select * from myData where name like '%Bob%' with the below code

let data: Array<any> = await MyDataSequelizeAccess.findAll({
  where: {
    name: {
      $like: `%Bob%`
    }
  }
});

Which is returning the below error

Invalid value { '$like': '%Bob%' }

How can I perform that type of where wildcard or where like on my sequelize object?

let data: Array<any> = await MyDataSequelizeAccess.findAll({
  where: {
    name: `Bob`
  }
});

This works as expected, but I cannot get the wildcard to work.

updated still no dice - per https://sequelize.readthedocs.io/en/latest/docs/querying/#operators my syntax looks correct

I'm also trying (and failing) to do the same with $not as in

let data: Array<any> = await MyDataSequelizeAccess.findAll({
  where: {
    name: {
      $not: `Bob`
    }
  }
});

and getting the same error as above Invalid value { '$not': '%Bob%' }

sequelize.js
4 Answers

String operators (e.g. $operator) is legacy in V5 of Sequelize [1]. You can still execute queries with operators the legacy way, but the connection has to be setup with aliases for operators (there will be a deprecation warning about this). [2]

const Op = Sequelize.Op;
const operatorsAliases = {
  $like: Op.like,
  $not: Op.not
}
const connection = new Sequelize(db, user, pass, { operatorsAliases })

[Op.like]:  '%Bob%' // LIKE '%Bob%'
$like: '%Bob%' // same as using Op.like (LIKE '%Bob%')

The updated documentation for Sequelize.js provides operators in the Sequelize.Op module as Symbol operators. [3]

Sequelize uses Symbol operators by default to minimize the risk of operators injected in the query; it's advised to use Symbol operators going forward. [4]

const Sequelize = require('sequelize');
const Op = Sequelize.Op;

let data: Array<any> = await MyDataSequelizeAccess.findAll({
  where: {
    name: {
      [Op.like]: '%Bob%'
    }
  }
});
user_51898110

Express sequelize first import express and defile Op like so:

const Sequelize = require("sequelize");
const Op = Sequelize.Op;

const { search } = await req.body;

The do like so:

const users = await User.findAll({
    where: {
      username: { [Op.like]: `%${search}%` },
    },
    include: [{ model: Tweet, as: "Tweets" }],
    raw: true,
  }).catch(errorHandler);
user_115488850

We can use operator [Op.substring] instead of [Op.like], so we don't have to append "%" symbols.

await Model.findAll({
    where: {
        name: { [Op.substring]: "bob" }
    }
});
user_40570020

If somebody is looking for example use with express.js

GET request body

 {
    "searchString": "search",
    "count": 0,
    "limit": 20
}

express.js with sequelize

app.get("/api/saloon", function (req, res) {
Saloon.findAll({
    where: {
        name: {[Op.iLike]: `%${req.body.searchString}%`}
    },
    offset: req.body.count,
    limit: req.body.limit,
})
user_106017080
Related