sequelize / sequelize/sequelize
Filtering with nested include
Nobody has claimed this yet.
- Dominant language
- TypeScript
- Stars
- 30.4k
- Forks
- 4.3k
- Avg merge
- 1d 6h
- Merged PRs (30d)
- 68
Description
What are you doing?
Trying to filter on an associated field.
const A = Connection.define('a');
const B = Connection.define('b');
A.belongsTo(B);
B.hasMany(A);
const C = Connection.define('c',
{
myField:Sequelize.STRING
});
B.belongsTo(C);
const main = async()=>
{
await Connection.sync({force:true});
const c= await C.create({myField:"123"});
const b = await B.create({cId:c.id});
const a = await A.create({bId:b.id});
const resA = await A.findAll({
logging:console.log,
include:[{model:B,include:[{model:C,where:{myField:"dontCare"}}]}]
});
const resB = await A.findAll({
logging:console.log,
include:[{model:B,where:{id:{[Op.gt]:0}},include:[{model:C,where:{myField:"dontCare"}}]}]
});
console.log(resA.map(a=>a.get({plain:true})));
console.log(resB.map(a=>a.get({plain:true})));
};
main();
To Reproduce
Run the code above.
What do you expect to happen?
I'm not entirely sure what the correct way of doing this kind of filtering is. But
I did not expect these 2 queries to produce a different result like this.
What is actually happening?
The first query runs the nested where on a different level from the second query. Seems inconsistent.
The inner where clause is shifted out when I include the useless filtering
where:{id:{[Op.gt]:0}}
Output:
SELECT `a`.`id`, `a`.`createdAt`, `a`.`updatedAt`, `a`.`bId`, `b`.`id` AS `b.id`, `b`.`createdAt` AS
`b.createdAt`, `b`.`updatedAt` AS `b.updatedAt`, `b`.`cId` AS `b.cId`, `b->c`.`id` AS
`b.c.id`, `b->c`.`myField` AS `b.c.myField`, `b->c`.`createdAt` AS `b.c.createdAt`, `b->c`.`updatedAt` AS
`b.c.updatedAt` FROM `as` AS `a` LEFT OUTER JOIN ( `bs` AS `b` INNER JOIN `cs` AS `b->c` ON
`b`.`cId` = `b->c`.`id` AND `b->c`.`myField` = 'dontCare' ) ON `a`.`bId` = `b`.`id`;
SELECT `a`.`id`, `a`.`createdAt`, `a`.`updatedAt`, `a`.`bId`, `b`.`id` AS `b.id`, `b`.`createdAt` AS
`b.createdAt`, `b`.`updatedAt` AS `b.updatedAt`, `b`.`cId` AS
`b.cId`, `b->c`.`id` AS `b.c.id`, `b->c`.`myField` AS `b.c.myField`, `b->c`.`createdAt` AS
`b.c.createdAt`, `b->c`.`updatedAt` AS `b.c.updatedAt` FROM `as` AS `a` INNER JOIN
`bs` AS `b` ON `a`.`bId` = `b`.`id` AND `b`.`id` > 0 INNER JOIN `cs` AS `b->c` ON
`b`.`cId` = `b->c`.`id` AND `b->c`.`myField` = 'dontCare';
Environment
Dialect:
- [X ] mysql
Dialect library version: 1.6.5
Database version: XXX
Sequelize version: 5.8.7
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
No source file or test is named. Start by running the supplied reproduction against MySQL and comparing the two generated SQL queries, then trace the include and nested where handling. Done means the two equivalent nested-filter cases have defined, consistent behavior and the result is covered by a regression test.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- javascript, mysql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100