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

Question

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?

Do you also get a sql syntax error when running the generated sql against your db? If so it sounds like a bug and I’d suggest to create an issue on the sequelize github page. — Catfish
– Catfish, Commented May 13, 2019 at 0:27
Yes, the generated SQL does throw the same error, and I've already reported it as a bug. I'm hoping someone else has run into this and figured out either how to work around the issue or another way to solve the same problem. — Stephen R. Smith
– Stephen R. Smith, Commented May 13, 2019 at 0:37
I could, but I decided to use Sequelize on this project to avoid having to hand code SQL for everything. When I was doing WebObjects development, the cardinal rule was "don't fight the framework", which basically meant "don't circumvent the framework". If something was hard it was generally because I was doing something wrong. I'd prefer not to start writing around Sequelize right out of the gate. Performance optimization when everything is finished - sure - but not this early, and not with something that seems supported based on the docs. — Stephen R. Smith
– Stephen R. Smith, Commented May 13, 2019 at 0:56
That said, I haven't had much useful feedback on questions, and I haven't found much in the way of working code examples that do more than scratch the surface, so if I can't find a way to crack into Sequelize and develop a better practical working knowledge of it I may have to jettison it and move to something else, which will be frustrating because on the surface it looks to be exactly what I'd like to be able to use going forward. — Stephen R. Smith
– Stephen R. Smith, Commented May 13, 2019 at 0:58

Shahar Hadas · Accepted Answer · 2019-05-14 00:51:24Z

2

Possible workaround for now might be to use RegEx

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

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

answered May 14, 2019 at 0:51

Shahar Hadas

2,8872 gold badges31 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related