Sequelize.js: Query for not in array ($ne for items in array)

Question

I am looking to pull items from a postgres data base with Sequelize, but only return items that have an id that does not equal any items in a given array.

In the Sequelize documentation, there are operators $ne for not equal and $in for returning items that have properties with values that match the given array, but it doesn't look like there is an operator for something that combines those two.

For example, if I were to have items in my database with ids [1, 2, 3, 4, 5, 6], and I wanted to filter those by comparing to another array (ie [2,3,4]), so that it would return items [1, 5, 6]. In my example i also randomize the return order and limit, but that can be disregarded.

function quizQuestions(req, res) {
  const query = {
    limit: 10,
    order: [ [sequelize.fn('RANDOM')] ],
    where: {
      id: { $ne: [1, 2, 3] } // This does not work
    }
  };

  Question.findAll(query)
  .then(results => res.status(200).json(map(results, r => r.dataValues)))
  .catch(err => res.status(500).json(err));
}

Edit: With @piotrbienias answer, my query looks like this:

  const query = {
    limit: 10,
    order: [ [sequelize.fn('RANDOM')] ],
    where: {
      id: { $notIn: [1, 2, 3] }
    }
  };

pirs · Accepted Answer · 2022-01-18 14:14:08Z

62

using

const Op = Sequelize.Op
where: {
      id: {[Op.notIn]:[1, 2, 3]}
}

See operators:

Sequelize exposes symbol operators that can be used for to create more complex comparisons

edited Jan 18, 2022 at 14:14

pirs

2,4612 gold badges20 silver badges25 bronze badges

answered Jan 9, 2018 at 6:38

Derit Agustin

7982 gold badges9 silver badges11 bronze badges

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

1 Comment

Ciro Santilli OurBigBook.com Over a year ago

I wonder why they don't make id: { $ne: [1, 2, 3] } work :-( Sounds intuitive given that id: { [1, 2, 3] } works and so $in is never needed anymore. Still doesn't work tested as of 6.14.

piotrbienias · Accepted Answer · 2017-03-30 18:47:40Z

12

There exists $notIn, you must have missed it. Exemplary query with $notIn generates

SELECT "id", "name"
FROM "categories" AS "Category"
WHERE "Category"."id" NOT IN (1, 2);

EDIT

The documentation you refer to is for 2.0 version of Sequelize, you should update to 3.0.

answered Mar 30, 2017 at 18:47

piotrbienias

7,4211 gold badge32 silver badges33 bronze badges

1 Comment

Yo Wakita Over a year ago

Perfect, thanks! I actually didn't notice I was reading the 2.0 docs when I posted. I am using sequelize 3.30.2, thanks for the help!

Collectives™ on Stack Overflow

Sequelize.js: Query for not in array ($ne for items in array)

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related