CODEWITHSUNDEEP
sqlnode.jssequelize.jssql-like
James Rudolf
James Rudolf

Reputation: 99

Sequelize multiple values in LIKE statement

I am trying to replicate this query in Sequelize:

SELECT * FROM Employee WHERE name LIKE 'john%' or name LIKE 'jane%'

Currently, I have this:

where: {
  name: Sequelize.where(
    Sequelize.fn('LOWER', Sequelize.col(employee.name)),
    'LIKE',
    'john%'
  )
}

But how would to handle multiple LIKE statements in Sequelize? I tried turning the 3rd parameter of Sequelize.where to an array like so: 

where: {
  name: Sequelize.where(
    Sequelize.fn('LOWER', Sequelize.col(employee.name)),
    'LIKE',
    ['john%', 'jane%']
  )
}

But I get this error: SequelizeDatabaseError: Invalid usage of the option NEXT in the FETCH statement

Upvotes: 0

Views: 8898

Answers (2)

Farhan Asif
Farhan Asif

Reputation: 158

The reply is quite late for you but this could help others through your question and my answer hopefully. :) You can simply pass an array of objects like:

let employeesName = [{ name: 'james' }, { name: 'JOHN' }, { name: 'JANE' }]);

 await Employee.findAll({
      where: {
          [Op.or]: employeesName,
      },
    })
    .then((result) => {
      console.log(result);
     })
     .catch((error) => {
       console.log(error);
     })

Upvotes: 1

Lin Du
Lin Du

Reputation: 102207

Here is an example using "sequelize": "^5.21.3":

import { sequelize } from '../../db';
import Sequelize, { Model, DataTypes, Op } from 'sequelize';

class Employee extends Model {}
Employee.init(
  {
    name: DataTypes.STRING,
  },
  { sequelize, modelName: 'Employee' },
);

(async function test() {
  try {
    await sequelize.sync({ force: true });
    // seed
    await Employee.bulkCreate([{ name: 'james' }, { name: 'JOHN' }, { name: 'JANE' }]);
    // test
    const result = await Employee.findAll({
      where: {
        name: {
          [Op.or]: [
            Sequelize.where(Sequelize.fn('LOWER', Sequelize.col('name')), 'LIKE', 'john%'),
            Sequelize.where(Sequelize.fn('LOWER', Sequelize.col('name')), 'LIKE', 'jane%'),
          ],
        },
      },
      raw: true,
    });
    console.log(result);
  } catch (error) {
    console.log(error);
  } finally {
    await sequelize.close();
  }
})();

The execution results:

Executing (default): DROP TABLE IF EXISTS "Employee" CASCADE;
Executing (default): DROP TABLE IF EXISTS "Employee" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "Employee" ("id"   SERIAL , "name" VARCHAR(255), PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'Employee' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "Employee" ("id","name") VALUES (DEFAULT,'james'),(DEFAULT,'JOHN'),(DEFAULT,'JANE') RETURNING *;
Executing (default): SELECT "id", "name" FROM "Employee" AS "Employee" WHERE (LOWER("name") LIKE 'john%' OR LOWER("name") LIKE 'jane%');
[ { id: 2, name: 'JOHN' }, { id: 3, name: 'JANE' } ]

data records in the database:

=# select * from "Employee";
 id | name
----+-------
  1 | james
  2 | JOHN
  3 | JANE
(3 rows)

Upvotes: 3

Related Questions