Sequelize build dynamic where clause condition with AND and OR clause

user_2320559 0
Viewed 158

I am trying to build a sequelize dynamic where clause condition which has AND and OR clause.

const objArr = [{ active: 'true' }];
      for (let i = 0; i < dynamicList.length; i++) {
        objArr.push({
          [Op.or]: [{ hierarchy: { [Op.like]: `%${dynamicList[i]}%` } }, { id: dynamicList[i] }],
        });
      }
      const condition = {
        where: {
          [Op.and]: objArr,
        },
        attributes: [Sequelize.fn('DISTINCT', Sequelize.col('name'))],
      };
      db.tableName
        .findAll(condition)
        .then((resul) => {
          callback(null, result);
        })
        .catch((err) => {
          callback(err, null);
        });

SQL Query generated

SELECT DISTINCT(name)
FROM tableName AS tableName 
WHERE (tableName.active = 'true' AND
 (tableName.hierarchy LIKE '%111%' OR tableName.id = '111') AND
 (tableName.hierarchy LIKE '%222%' OR tableName.id = '222'));

SQL Query Expected --> active=true AND ((dynamic_list) OR (dynamic_list))

SELECT DISTINCT(name)
FROM tableName AS tableName 
WHERE (tableName.active = 'true' AND
 (tableName.hierarchy LIKE '%111%' OR tableName.id = '111') OR
 (tableName.hierarchy LIKE '%222%' OR tableName.id = '222'));
sequelize.js javascript
1 Answers

You need to combine all dynamic conditions into an upper Op.or and only then add this Op.or to an array for Op.and:

const objArr = [{ active: 'true' }];
const dynamicConditions = [];
      for (let i = 0; i < dynamicList.length; i++) {
        dynamicConditions.push({
          [Op.or]: [{ hierarchy: { [Op.like]: `%${dynamicList[i]}%` } }, { id: dynamicList[i] }],
        });
      }
      if (dynamicConditions.length) {
        objArr.push({
          [Op.or]: dynamicConditions
        });
      }
      const condition = {
        where: {
          [Op.and]: objArr,
        },
        attributes: [Sequelize.fn('DISTINCT', Sequelize.col('name'))],
      };
user_13766180
Related