I have two models
Units and Filters which are related with M:N association through unit_filter table
this.belongsToMany(Unit, {
through: "unit_filter",
as: "units",
});
this.belongsToMany(Filter, {
through: 'unit_filter',
as: 'filters',
});
The goal is to fetch units which have more than 1 filter associated with and condition.
let result = await Unit.findAll({
include: [
{
model: Filter,
where: {
id: {
[Op.and] : [2,252,4,80]
}
},
as: 'filters',
},
],
});
The above is only working if there is only one id in the array which does not make any sense.
Seqeulize documenation states
Post.findAll({
where: {
[Op.and]: [{ a: 5 }, { b: 6 }], // (a = 5) AND (b = 6)
}
})
So the code I have should work theoritically. I also tried with
where: {
[Op.and] : [{id:2},{id:252},{id:4},{id:80}]
}
which results in getting all the items from the db. It does not even care about the where condition in this case.
Would be of great help if any one points me in right direction.
You need to use Sequelize.literal with a subquery in where option in order to filter units that have more than 1 filter because simply indicating several ids of filters you will get units that have one of indicated filters (from 1 to N).
let result = await Unit.findAll({
where: Sequelize.where(
Sequelize.literal('(SELECT COUNT(*) FROM unit_filter as uf WHERE uf.unit_id=unit.id AND uf.filter_id in ($filter_ids))'),
Op.gt,
'1'),
bind: {
filter_ids: [2,252,4,80]
}
});