sequelize / sequelize/sequelize

Filtering with nested include

Open
#11,036 4 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

status: understood type: bug
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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.