sequelize: `$association.references$` are incompatible with `limit` and one-to-many & many-to-many includes/associations
Issue Description
What are you doing?
I’m trying to run the queries shown here adding the limit parameter on the top level.
I’ve created a small Codesandbox using the examples on the documentation and adding the limit parameter. The resulting query can be easily seen in the sandbox console.
Here is the query anyway:
User.findAll({
where: {
"$Instruments.id$": { [Sequelize.Op.is]: null }
},
include: {
model: Tool,
as: "Instruments"
},
limit: 2
}).catch((err) => {});
What do you expect to happen?
SELECT `user`.*,
`Instruments`.`id` AS `Instruments.id`,
`Instruments`.`name` AS `Instruments.name`,
`Instruments`.`size` AS `Instruments.size`,
`Instruments`.`userId` AS `Instruments.userId`
FROM (
SELECT `user`.`id`,
`user`.`name`
FROM `users` AS `user`
) AS `user`
LEFT OUTER JOIN `tools` AS `Instruments` ON `user`.`id` = `Instruments`.`userId`
WHERE `Instruments`.`id` IS NULL
LIMIT 2
What is actually happening?
SELECT `user`.*,
`Instruments`.`id` AS `Instruments.id`,
`Instruments`.`name` AS `Instruments.name`,
`Instruments`.`size` AS `Instruments.size`,
`Instruments`.`userId` AS `Instruments.userId`
FROM (
SELECT `user`.`id`,
`user`.`name`
FROM `users` AS `user`
WHERE `Instruments`.`id` IS NULL
LIMIT 2
) AS `user`
LEFT OUTER JOIN `tools` AS `Instruments` ON `user`.`id` = `Instruments`.`userId`
Additional context
I believe the queries and the code explain themselves. What I’m trying to achieve is that exact query:
- Be able to do a LEFT OUTER
- Filter it by the missing matches (ie “id” is NULL)
- Limit the query.
If there is any other way to manage to obtain the query that I expect feel free to suggest it !
Environment
- Sequelize version: 6.4.0
- Node.js version: 10.22.0
- Operating System: macOS / Codesandbox is running a regular linux
Issue Template Checklist
How does this problem relate to dialects?
- I think this problem happens regardless of the dialect.
- I think this problem happens only for the following dialect(s):
- I don’t know, I was using PUT-YOUR-DIALECT-HERE, with connector library version XXX and database version XXX
Would you be willing to resolve this issue by submitting a Pull Request?
- Yes, I have the time and I know how to start.
- Yes, I have the time but I don’t know how to start, I would need guidance.
- No, I don’t have the time, although I believe I could do it if I had the time…
- No, I don’t have the time and I wouldn’t even know how to start.
Regards
About this issue
- Original URL
- State: open
- Created 3 years ago
- Reactions: 19
- Comments: 21 (6 by maintainers)
Adding
limit
turns the query into a subQuery to ensurelimit
only impacts how many users are returned.Adding
subQuery: false
could solve your issue, with a caveat, but this is indeed a bug as even when using a subQuery, $references$ to joined tables should still function.Ok, guys, here is some proof-of-concept workaround inspired by @nikkorfed idea.