CODEWITHSUNDEEP
postgresqlsequelize.js
Yo Wakita
Yo Wakita

Reputation: 5450

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

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] }
    }
  };

Upvotes: 23

Views: 47064

Answers (2)

Derit Agustin
Derit Agustin

Reputation: 778

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

Upvotes: 60

piotrbienias
piotrbienias

Reputation: 7401

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.

Upvotes: 12

Related Questions