sequelize, Statement(where) in statement(where)

Question

I'm trying for 2 hours to resolve a little problem which is not one. I'm on an generated yeoman Angular-fullstack App.

I want to write this code with sequelize:

SELECT * 
FROM demand 
WHERE city_id NOT IN (
SELECT city_id
FROM demand
WHERE user_id=req.params.user_id)

I have yet the following code but that doesn't work. I get only []

export function getCity(req, res) {
  return Demand.findAll({
    where: {
      city_id:{ $notIn: Demand.findAll({
        attributes: ['city_id'],
        where: {
          user_id: req.params.user_id,
        }
      })}
    }
  })
  .then(handleEntityNotFound(res))
  .then(respondWithResult(res))
  .catch(handleError(res));
}

Have you a clue? Thank you.

score 3 · Answer 1 · answered Aug 19 '16 at 13:24

The problem from your first query is there because the findall method of sequelize returns a promise.

The only way to use subqueries in where condition with sequelize is through the literal method: sequelize.literal('your query');

Check this issue to get more informations https://github.com/sequelize/sequelize/issues/3961

score 1 · Accepted Answer · answered Aug 11 '16 at 08:24

I have not found a nice solution: I have finally wrote with query.

export function showDemandArtistNoUser(req, res) {
  return db.sequelize.query('SELECT * FROM demands WHERE city_id NOT IN (SELECT city_id FROM demands WHERE user_id='+req.params.user_id+')', { type: db.sequelize.QueryTypes.SELECT })
  .then(handleEntityNotFound(res))
  .then(respondWithResult(res))
  .catch(handleError(res));
}

And with my angular-fullstack, i have to add on my page:

 import db from '../../sqldb';

Shahar Hadas · Answer 3 · 2019-04-06T11:18:51.047

I have encountered a similar issue in my project. The way I choose to implement it is a bit different for two reasons:

If at one point in time Sequelize decides to implement sub queries - the syntax is ready.
Use Sequelize protection agains SQL injection.

Here is my code snippet, hope it helps.

const tempSQL = sequelize.dialect.QueryGenerator.selectQuery('MyOtherTable',{
    attributes: ['fkey'],
    where: {
         field1: 1,
         field2: 2,
         field3: 3
    }})
    .slice(0,-1); // to remove the ';' from the end of the SQL

MyTable.find( {
    where: {
        id: {
             $notIn: sequelize.literal('(' + tempSQL + ')'),
        }
    } 
} );

Some people might choose to not use the tempSQL variable and simply build the SQL inside the find structure (maybe using a helper method?)

I also think this might be the basis for a sub queries extension for sequelize as it uses the same syntax almost.

sequelize, Statement(where) in statement(where)

3 Answers3

Linked