How to perform a search with conditional where parameters using Sequelize

In Sequelize, using the where parameter to perform search queries is a powerful and flexible feature. The where parameter allows you to specify filtering conditions so that the query only returns records matching these conditions. Here, I'll provide a basic example of how to use the where parameter and explain how to perform more complex queries.

Basic Search

Consider a model named Users with firstName and lastName fields. If you want to find all users with firstName equal to 'John', you can do the following:

javascript
const { User } = require('./models');

async function findJohn() {
  try {
    const users = await User.findAll({
      where: {
        firstName: 'John'
      }
    });
    console.log(users);
  } catch (error) {
    console.error('Search failed:', error);
  }
}

findJohn();

In this example, the where parameter is an object where firstName: 'John' specifies that we only want to retrieve records where firstName equals 'John'.

Using Multiple Conditions

You can also use multiple conditions for searching. For example, if you want to find users with first name 'John' and last name 'Doe', you can write:

javascript
const users = await User.findAll({
  where: {
    firstName: 'John',
    lastName: 'Doe'
  }
});

Using Operators

Sequelize also supports using various operators for more complex queries, such as gt (greater than), lt (less than), ne (not equal), in (in an array), etc. This requires the use of Sequelize.Op. Here is an example:

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

const users = await User.findAll({
  where: {
    age: {
      [Op.gt]: 18
    }
  }
});

This query returns all users with age greater than 18.

Using Logical Operators

You can also use logical operators such as or and and to build more complex queries. For example, if you want to find users whose last name is 'Doe' or first name is 'Jane', you can write:

javascript
const users = await User.findAll({
  where: {
    [Op.or]: [
      { lastName: 'Doe' },
      { firstName: 'Jane' }
    ]
  }
});

This query returns all users with last name 'Doe' or first name 'Jane'.

Summary

The where parameter in Sequelize offers a powerful tool for executing database searches, allowing developers to filter necessary data using simple or complex conditions. By combining operators and logical operators, we can construct almost any query condition required. The above are some basic and advanced usage methods, which can be flexibly applied based on specific requirements in practical applications.