I want do something, like what I can do in Mongoose:

Customers.find({
            $or: [
                {firstName: {$in: RegExpArray}},
                {lastName: {$in: RegExpArray}},
                {email: {$in: RegExpArray}}
            ]
        }).limit(50);

I tried:

Customers.findAll({
            where: {
                $or: [
                    {firstName: {$iLike: {$in: RegExpArray}}},
                    {lastName: {$iLike: {$in: RegExpArray}}},
                    {email: {$iLike: {$in: RegExpArray}}}
                ]
            },
            limit: 50});

Also I tried use $regex instead of $iLike, or pass array [ '%someString%', '%someString%', '%someString%'...] instead of RegExpArray what should work, but non of this worked. Is it any way to use regex in query?

I want do something, like what I can do in Mongoose:

Customers.find({
            $or: [
                {firstName: {$in: RegExpArray}},
                {lastName: {$in: RegExpArray}},
                {email: {$in: RegExpArray}}
            ]
        }).limit(50);

I tried:

Customers.findAll({
            where: {
                $or: [
                    {firstName: {$iLike: {$in: RegExpArray}}},
                    {lastName: {$iLike: {$in: RegExpArray}}},
                    {email: {$iLike: {$in: RegExpArray}}}
                ]
            },
            limit: 50});

Also I tried use $regex instead of $iLike, or pass array [ '%someString%', '%someString%', '%someString%'...] instead of RegExpArray what should work, but non of this worked. Is it any way to use regex in query?

• javascript
• sql
• mongoose
• sequelize.js

Share Improve this question Follow edited Oct 4, 2016 at 0:25 Sarkis Arutiunian asked Oct 3, 2016 at 19:32 Sarkis ArutiunianSarkis Arutiunian 1,29133 gold badges1717 silver badges3636 bronze badges 8

• Can you give me a example of how the sql query would look like? – user3254198 Commented Oct 3, 2016 at 23:07
• actually I can't. I'm new in SQL, always have been working with noSQL, where you can easily use regex in query like in first example. – Sarkis Arutiunian Commented Oct 3, 2016 at 23:15
• inside your sequelize config where you have your database info add logging: true. That should print the sql statements that are being run. – user3254198 Commented Oct 3, 2016 at 23:25
• forgot to mention. post us the sql statements that are being run use gist.github. to avoid the clutter in ments. – user3254198 Commented Oct 3, 2016 at 23:52
• 1 Can you show me the regular expression? Hopefully we can translate that into a LIKE statement instead. Out of the box sql supports a LIKE attribute which is similar to a regex. – user3254198 Commented Oct 4, 2016 at 0:25

 |  Show 3 more ments

1 Answer 1

Sorted by: Reset to default 5

Try this instead:

let searchFor = [ '%someString%', '%someString%', '%someString%'...]

// basically turning the items into objects with "$iLike" as the key
searchFor = searchFor.map((item) => {
    return {$iLike: item};
});

Customer.findAll({
            where: {
                $or: [
                    {firstName: {$or: searchFor}},
                    {lastName: {$or: searchFor}},
                    {email: {$or: searchFor}}
                ]
            },
            order: [['createdAt', 'DESC']],
            limit: 50
        })