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?
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
}
);