How to create dynamic where clause in sequelize query using $or and $ and

When performing database operations with Sequelize, constructing dynamic WHERE clauses is crucial, especially when dealing with complex query conditions. $or and $and are key operators in Sequelize for building compound query conditions. Below, I'll illustrate how to dynamically use these operators in Sequelize queries with a specific example.

Suppose we have a Users table, and we need to retrieve data based on the user's email or username along with their account status. The account status may include active, suspended, etc.

First, ensure that the Op object is imported in the model definition:

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

Next, we can create a function that accepts three parameters: email, username, and status, and constructs the query conditions based on these parameters:

javascript
function findUsers(email, username, status) {
  // Create an empty where object
  let whereClause = {};

  // Use $or to specify that users can be searched by email or username
  if (email || username) {
    whereClause[Op.or] = [];
    if (email) {
      whereClause[Op.or].push({ email: email });
    }
    if (username) {
      whereClause[Op.or].push({ username: username });
    }
  }

  // Use $and to ensure the user's status matches the query conditions
  if (status) {
    whereClause[Op.and] = [{ status: status }];
  }

  // Execute the query using the constructed whereClause
  return Users.findAll({
    where: whereClause
  });
}

In this example, we first check if email or username is provided. If so, we use the $or operator to include them in the query conditions. Additionally, if the status parameter is provided, we use the $and operator to ensure the status matches.

The advantage of this approach is that it allows us to flexibly construct query conditions based on the provided parameters, rather than hardcoding query logic. This is essential for building maintainable and scalable applications. Of course, this is a basic example; in real-world applications, you may need to handle additional fields and more complex logical conditions.