Crafting a sequelize WHERE IN using multiple columns

Question

In MySQL (And other dbs), you can do a where in query with multiple columns like below:

SELECT * FROM trains WHERE (name, location) IN (('train1', 'us'), ... ,('train2', 'us'));

I'm trying to do this with sequelize:

const selectTrains = [
    {name: 'train1', location: 'us'},
    {name: 'train1', location: 'eu'},
    {name: 'train2', location: 'us'},
];

let dbTrains = await Trains.findAll({ where: { [Op.in]: selectTrains} });

But I get the following error:

UnhandledPromiseRejectionWarning: Error: Invalid value {name: 'train1', location: 'us'}

The documentation didn't really cover this use case. Is this possible to do with the Op.in operator?

In most languages, this ends up just having to be a concatenated string, sadly — Joe Phillips, Apr 24 '18 at 15:54

score 2 · Accepted Answer · answered Apr 25 '18 at 03:56

2

You can achieve the same thing with $or :

const selectTrains = [
    {name: 'train1', location: 'us'},
    {name: 'train1', location: 'eu'},
    {name: 'train2', location: 'us'},
];

Trains.findAll({ where: { $or : selectTrains} });

answered Apr 25 '18 at 03:56

Vivek Doshi

56,649
12
110
122

I have noticed that I can do this, but it creates a massive OR statement, which is significantly less performant (By almost a factor of 3) than a WHERE IN statement. – Blue Apr 25 '18 at 04:48
@FrankerZ , then other option is to run raw query via sequelize. – Vivek Doshi Apr 25 '18 at 04:49

score 0 · Answer 2 · answered Oct 27 '21 at 04:34

0

Instead of **$or** use **Op.or**        
const selectTrains = [
            {name: 'train1', location: 'us'},
            {name: 'train1', location: 'eu'},
            {name: 'train2', location: 'us'},
];
        
// Trains.findAll({ where: { $or : selectTrains} });
 Trains.findAll({ where: { [**Op.or**] : selectTrains} });

answered Oct 27 '21 at 04:34

saisanthosh s

1

Please add description to your question. – Sworup Shakya Oct 27 '21 at 05:10
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 27 '21 at 05:10

Crafting a sequelize WHERE IN using multiple columns

2 Answers2