Where condition for joined table in Sequelize ORM

javascript

sequelize-orm

join-conditions

raw-sql

byAnton Shumikhin·Nov 21, 2024

When using Sequelize, to filter records in a joined table, utilize the include along with the nested where options like this:

const result = await MainModel.findAll({
  include: [{
    model: JoinedModel,
    where: { column: condition }
  }]
});

Just replace MainModel and JoinedModel with your Sequelize models, column with the column from the joined table you'd like to filter upon, and condition with your chosen filtering criteria. Doing this properly applies the where clause to the joined table.

Bracket Bonanza: Sequelize sure loves its nested brackets, like a matryoshka doll of JavaScript syntax. 🇷🇺

Category is: Include!

When including models in your queries, it would be impeccable to know the model and required options. They are crucial for correct implementation of joins.

{
  model: JoinedModel, // This is the Sequelize model for the joined table
  required: true, // Aka an INNER JOIN, set it to `false` for a LEFT OUTER JOIN
}

Dynamic conditions & Aliases

Sometimes our queries need to be a bit more dynamic. Let's say your joined table's columns are aliased. Sequelize's got your back, use $$:

const result = await MainModel.findAll({
  include: [{
    model: JoinedModel,
    as: 'Alias',
    where: { '$Alias.column$': condition }
  }]
});

Ah, the sheer power of the dollar (sign). 💪$$

In this instance, replace Alias with the alias identified in your association. This allows Sequelize to understand that you are trying to reference this aliased column.

Synchronised Swimming of Conditions

Multiple conditions? With Sequelize's $or, you can bring several conditions together in harmony, like synchronised swimmers:

const result = await MainModel.findAll({
  include: [{
    model: JoinedModel,
    where: {
      [Sequelize.Op.or]: [{ column1: condition1 }, { column2: condition2 }]
    }
  }]
});

This performs a logical OR between the conditions set for column1 and column2, i.e., meet column1 condition or column2 condition (or both) to make it to the result team.

Optional Joins

In cases where you have an optional related record (a left join), remember to set required to false:

const result = await MainModel.findAll({
  include: [{
    model: JoinedModel,
    required: false  // Now we're pulling a LEFT OUTER JOIN on you!
  }]
});

Setting required: false tells Sequelize to keep the main table rows, even without matching entries in the joined table.

Fall back to Raw SQL

There are times where Sequelize just doesn't cut it. Complex conditions or more uncommon SQL operations, for instance, may be more comfortably written in raw SQL:

const [results, metadata] = await sequelize.query(`SELECT * FROM MainTable m JOIN JoinedTable j ON m.id = j.MainTableId WHERE j.column = :condition`, {
  replacements: { condition: 'desiredCondition' }
});

Raw SQL. As naked as nature intended SQL to be. 🍃

Be advised: you're stepping out of ORM safety here. Make sure to parameterize your inputs to prevent SQL injection.

Nine-One-One, this is an Emergency

In case Sequelize isn't being cooperative, you should consider raw SQL queries as a fallback plan. This helps when dealing with complex conditions or when performance is a priority, and a hand-crafted SQL query is considerably faster.

explain-codes / Javascript / Where condition for joined table in Sequelize ORM

Linked

Sequelize Where if not null



How to make Sequelize use singular table names



How does group by work in sequelize?



Typeorm "OR" operator



Activerecord Join Query and select in Rails

