Filtering by Multiple Conditions in Sequelize

Overview

Sequelize is a popular Node.js ORM for managing relational databases. It provides a rich set of tools for building dynamic queries using JavaScript. This tutorial will guide you through different ways to filter queries using multiple conditions in Sequelize, ensuring your application can handle complex data retrieval with ease.

Basic Filtering with where Clauses

Filtering in Sequelize starts with the where option. Here’s a simple example:

const users = await User.findAll({
  where: {
    isActive: true
  }
});

This code fetches all active users from the database.

Combining Conditions

To combine conditions, you can use Op.and:

const { Op } = require('sequelize');

const users = await User.findAll({
  where: {
    [Op.and]: [{ isActive: true }, { age: { [Op.gt]: 30 } }]
  }
});

This fetches active users who are older than 30.

Using Operators for Complex Filters

Sequelize provides various operators for complex filtering conditions:

• Op.or: for logical OR.
• Op.gt, Op.gte: for ‘greater than’ and ‘greater than or equal’ comparisons.
• Op.lt, Op.lte: for ‘less than’ and ‘less than or equal’ comparisons.
• Op.ne, Op.eq: for ‘not equal’ and ‘equal’ comparisons.

Here’s an example using a combination of these operators:

const users = await User.findAll({
  where: {
    [Op.or]: [
      { age: { [Op.lt]: 20 } },
      { age: { [Op.gt]: 50 } }
    ]
  }
});

The above code finds users who are either younger than 20 or older than 50.

Advanced Filtering with Nested Conditions

For more complex queries, you can nest conditions:

const users = await User.findAll({
  where: {
    [Op.and]: [
      { [Op.or]: [{ age: { [Op.lt]: 20 } }, { age: { [Op.gt]: 60 } }] },
      { isActive: true }
    ]
  }
});

This retrieves active users who are either below 20 or above 60 years old.

Filtering with Associations

When dealing with associated models, filters can be applied as follows:

const users = await User.findAll({
  include: [
    {
      model: Post,
      as: 'posts',
      where: { published: true }
    }
  ],
  where: {
    isActive: true
  }
});

This finds active users with published posts.

Dynamic Filtering for API Queries

For APIs, filters might come from parameters. Here’s how to build a dynamic query:

const buildQuery = (filters) => {
  const query = { where: {} };
  if(filters.isActive) {
    query.where.isActive = filters.isActive === 'true';
  }
  if(filters.age) {
    query.where.age = { [Op.gt]: Number(filters.age) };
  }
  return query;
};

const users = await User.findAll(buildQuery(apiFilters));

This allows for a flexible and scalable API endpoint.

Performance Considerations

When performing complex queries:

• Use indexes on columns that are often searched.
• Minimize the use of Op.or with indices, as it can affect performance.

Always test your queries for performance implications.

Conclusion

This tutorial covered the essentials of filtering by multiple conditions in Sequelize. As you start implementing more complex queries, always consider performance and the maintainability of your code. Happy querying!