Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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] }
    }
  };
like image 829
Yo Wakita Avatar asked Mar 30 '17 18:03

Yo Wakita


People also ask

How to get Sequelize query result with where condition for array?

To get sequelize query result with where condition for given array with using **Op.in**code to find result with in array as

How to use join or eager query in node Sequelize?

When you want use join or eager query in node sequelize then first you have to declare relation or association between tables. Sequelize provides four types of associations that should be combined to create them: relation or association in node sequelize example. 1const A = sequelize.define('A', /* ... */); 2const B = sequelize.define('B', /* ...

How to use Sequelize string attributes?

The attribute can also be an object from one of the sequelize utility functions ( sequelize.fn, sequelize.col etc.) For string attributes, use the regular { where: { attr: something }} syntax. If you don't want your string to be escaped, use sequelize.literal.

What is a Sequelize model in SQL Server?

A sequelize model used to build the returned model instances Set of flags that control when a query is automatically retried. Accepts all options for retry-as-promised. Only retry a query if the error matches one of these strings. How many times a failing query is automatically retried.


1 Answers

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

like image 103
Derit Agustin Avatar answered Sep 27 '22 15:09

Derit Agustin