Estoy tratando de construir una dinámica de secuela donde la condición de la cláusula tenga una cláusula AND y OR.
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); });Consulta SQL generada
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'));Consulta SQL esperada --> 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'));Debe combinar todas las condiciones dinámicas en un Op.or superior y solo luego agregar este Op.or a una matriz para 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'))], };