How to Add Search Filters Inside Sequelize include in Node.js / JavaScript

 

When working with Sequelize, you might encounter situations where you want to apply filters on both the main model and its associations (included models).
However, Sequelize executes the where clause for the main model and includes separately.
If not handled correctly, this can lead to unexpected results because the conditions on the included models are treated as separate subqueries.

Let’s go step by step.

Scenario

Assume you want to generate a SQL query like this:

SELECT * 
FROM Collection
JOIN Item ON Item.collectionId = Collection.id
WHERE Collection.customer = 'blabla' 
  AND Item.itemId = 1;

To achieve this in Sequelize, you should reference the included model field in the main where clause using $ notation, like this:

model.Collection.findAll({
  where: {
    customer: customerParam,
    '$items.itemId$': itemParam,
  },
  include: [{
    model: models.Item,
    as: 'items',
  }]
});

✅ Explanation:

• customerParam: filter applied to the main model (Collection).

• '$items.itemId$': filter applied to the included model (Item). Notice the $ notation.

• as: 'items': make sure the alias matches your model association.

---

Dynamic Search Filter with OR Conditions

Now, suppose you want to add search filters dynamically across multiple fields and multiple relationships, like searching across users and collaborators.

You can build a flexible where statement like this:

const whereStatement = {
  communityId: params.communityId,
  status: constants.REQUEST_STATUS.pending,

  [Op.or]: [
    // Search in users table
    { '$users.name$': { [Op.iLike]: `%${params.searchKey}%` } },
    { '$users.userName$': { [Op.iLike]: `%${params.searchKey}%` } },
    { '$users.email$': { [Op.iLike]: `%${params.searchKey}%` } },

    // Search in collaborators table
    { '$collaborators.name$': { [Op.iLike]: `%${params.searchKey}%` } },
    { '$collaborators.userName$': { [Op.iLike]: `%${params.searchKey}%` } },
    { '$collaborators.email$': { [Op.iLike]: `%${params.searchKey}%` } },
  ]
};

✅ Explanation:

• Op.or: allows multiple conditions in an "OR" manner.

• The $table.field$ notation refers to the field inside the associated model.

• Op.iLike: enables case-insensitive search for PostgreSQL (use Op.like for other databases).

You can then pass this whereStatement into your Sequelize query:

model.Request.findAll({
  where: whereStatement,
  include: [
    { model: models.User, as: 'users' },
    { model: models.Collaborator, as: 'collaborators' },
  ]
});

---

Summary

• ✅ Use $ notation ('$items.itemId$') to filter associated models from the main where clause.

• ✅ Use Op.or to dynamically add multiple search conditions.

• ✅ Ensure the alias names (as) in the include match exactly with the references in the where clause.

With this approach, you can build powerful, flexible search queries across multiple related models in Sequelize!