CODEWITHSUNDEEP
node.jssequelize.jssql-like
Wai Yan Hein
Wai Yan Hein

Reputation: 14771

Node JS Sequelize WHERE LIKE operator in raw query is throwing error

I am building a Node JS application. I am using Sequelize for the database operations. Now, I am having a problem with using the LIKE operator with raw queries.

I have the query as follow.

let offset = (page > 1)? (page - 1) * recordPerPage: 0;
    let where = { }
    let sql = 'SELECT "Users"."id", "Users"."name", "Users"."email"';
    sql = sql + ' FROM "Users"';
    sql = sql + ' WHERE "Users"."id" != :myUserId';
    where.myUserId = myUserId;
    if (keyword) {
      where.keyword = keyword;
      sql = sql + ' AND ("Users"."email" LIKE "%:keyword%" OR "Users"."name" LIKE "%:keyword%")'
    }
    if (parseInt(roleType) > 0) {
      // get the role is because role is the type
      let role = await Role.findOne({
        where: {
          type: {
            [Op.eq]: roleType
          }
        }
      })
      where.role = role.id;
      sql = sql + ' AND "Users"."id" IN (SELECT "UserRoles"."user_id" FROM "UserRoles" WHERE "UserRoles"."role_id" =:role)';
    }
    sql = sql + ' ORDER BY "Users"."name" ASC, "Users"."id" ASC';

    where.offset = offset;
    where.limit = recordPerPage;
    sql = sql + ' LIMIT :limit OFFSET :offset';

    let users = await database.sequelize.query(sql, {
      replacements: where,
      type: QueryTypes.SELECT
    })

When I pass the keyword that is used with the LIKE operator, I am getting the following error.

`column "%'TEST'%" does not exist`

What is wrong with my code and how can I fix it?

Upvotes: 2

Views: 3227

Answers (1)

germanio
germanio

Reputation: 859

Please take a look at the last example in here:
there is no " for the placeholder in the sql, but also, the keyword should include the % inside, as part of its content.

Here's the example from the docs:

const { QueryTypes } = require('sequelize');

await sequelize.query(
  'SELECT * FROM users WHERE name LIKE :search_name',
  {
    replacements: { search_name: 'ben%' },
    type: QueryTypes.SELECT
  }
);

Upvotes: 3

Related Questions