CODEWITHSUNDEEP
node.jsexpresssequelize.js
Stephen R. Smith
Stephen R. Smith

Reputation: 3400

[Op.like]: example from Sequelize documentation throws SQL error

I have a Node/Express/Sequelize project.

Using the following example from the documentation, it fails and throws a SQL error on MySQL

    Image.findAll({
        where: {
            image_title: {
                [Op.like]: { [Op.any]: ['cat', 'hat']}
            }
        }
    })

The above query generates the following SQL in Node/Express:

`Executing (default): SELECT `id`, `story_title`, `image_title`,
`original_filename`, `created_at` AS `createdAt`, `updated_at` 
AS `updatedAt` FROM `image` 
AS `image` 
WHERE `image`.`image_title` LIKE ANY ('cat', 'hat');`

I expected to get a list of images where the image_title contains either 'cat' or 'hat'

Instead, I get a console error in Chrome that reads:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('cat', 'hat')' at line 1"

And no images.

What I'm trying to do is pass an array of strings into a query and return all images where the image_title contains any of the strings in the array.

This example from the documentation seems to be exactly what I'm looking for, but I can't get it to work with even static strings using the documented syntax.

Is there a bug in this, am I misunderstanding how this works, or is there another way to accomplish this?

Upvotes: 1

Views: 2127

Answers (1)

Shahar Hadas
Shahar Hadas

Reputation: 2827

Possible workaround for now might be to use RegEx

let words = ['cat','hat'];

Image.findAll({
        where: {
            image_title: {
                [Op.regexp]: `*(${words.join('|')})*`
            }
        }
    })

Upvotes: 2

Related Questions