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'));
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'))],
};