Sequelize - subquery in where clause

Question

I'm using Sequelize in my Express app. I need to generate a query that has a subquery in the WHERE clause.

SELECT *   FROM MyTable  WHERE id NOT IN (        SELECT fkey          FROM MyOtherTable         WHERE field1 = 1           AND field2 = 2           AND field3 = 3        ) 

I first tried relations/associations through my models but couldn't get it to work. Something like:

MyTable.find( {     where: {         id: {             $notIn: // <= what goes here? Can I somehow reference my include model?         }     },     include: [ {         model: MyOtherTable,         where: {           field1: 1,           field2: 2,           field3: 3     } ] } ); 

Then I tried using Sequelize.where(), no luck there.

Then I tried Sequelize.literal() and that works but not sure if it's a "proper" way of doing a subquery in a where clause in Sequelize as I'm new to it.

MyTable.find( {     where: {         id: {             $notIn: sequelize.literal(                  '( SELECT fkey ' +                     'FROM MyOtherTable ' +                    'WHERE field1 = ' + field1 +                     ' AND field2 = ' + field2 +                     ' AND field3 = ' + field3 +                  ')'         }     }  } ); 

I also know that I could use Sequelize.query() but don't really know if I should reach for it or if literal()is the right away as I feel like there's something I'm overlooking.

I would really like to know how to perform a subquery in a WHERE clause with Sequelize the "proper" way.

Thanks for the feedback!

Answer 1

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

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

Here is my code snippet, hope it helps.

Sequelize v5

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: {               [Sequelize.Op.notIn]: sequelize.literal(`(${tempSQL})`)         }     }  } ); 

Sequelize v6

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: {               [Sequelize.Op.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.

Answer 2

In addition to @Shahar Hadas answer, because i fall into some errors using the code he showed.

Here is a more complexe example. In this example we have a main table named "Artist" in a Many-to-Many relationship with "Tag". "Tag" are associated to a predefined list of tags i named "TagType". We want to fetch all Artists linked to all the searched tags (TagType Id).

const tagsSearched = [1, 2];  const subQueryOptions = {     attributes: ['id'], // You have to list at least one attribute     include: [         {             model: models.Tag,             required: true,             attributes: [], // Avoid the only_full_group_by error             through: {                 attributes: [], // Avoid the only_full_group_by error             },             include: {                 model: models.TagType,                 required: true,                 attributes: [], // Avoid the only_full_group_by error                 where: {                     id: {                         [Op.in]: tagsSearched, // Array of tags searched                     }                 },             },         }             ],     group: sequelize.col('artist.id'), // Group by the main parent ID of this query     having: sequelize.where(sequelize.fn('count',              sequelize.col('tags.tagType.id')), {                 [Op.gte]: tagsSearched.length,     }), // Get only the artists that have at least the "tagsSearched" associated }  // Parse the subQueryOptions, this operation would serialize the queryOptions Model._validateIncludedElements.bind(models.Artist)(subQueryOptions);  // First argument has to be a string (table name, by default in plural) // Second argument is our serialized query options // Third argument is the model used const artistsSubQuery = sequelize.dialect.queryGenerator.selectQuery("artists", subQueryOptions, models.Artist) .slice(0,-1); // to remove the ';' from the end of the SQL query  models.Artist.findAll({     where: {         id: {             [Op.in]: sequelize.literal(`(${artistsSubQuery})`),         }     } }); 

I will update this in case of questions.