Description
Describe the bug
Nested items are returned as null, when they are not.
Example query
todoItems {
id
name
toTags {
tag {
id
name
}
}
}
You have two tables todoItems and tags as well as a many2many join table todo-to-tag.
If there are soft deleted records inside the join table and you try to query the todoItems with their linked tags, all todoItems will be returned with the right number of toTags (join column) records but some of the tags inside the toTags will be returned as null.
Have you read the Contributing Guidelines?
Yes
To Reproduce
Steps to reproduce the behavior:
I have created a fork of this repo and added an example and failling test that should pass once the issue is fixed.
https://github.com/rostaingc/nestjs-query
Expected behavior
All the items in the nested objects should be returned and not be null.
Additional context
When analyzing the SQL queries made by the nestjs query, what happens is:
-
Query A is made on the todoItems table.
-
Query B made on the join table:
SELECT "todos-to-tags"."todoID", "todos-to-tags"."tagID" FROM "todos-to-tags" WHERE "todos-to-tags"."deleted" IS NULL AND "todos-to-tags"."todoID" IN (x)
With x being the IDs returned by query A -
Query C is made on the tags table:
SELECT * FROM "tags" INNER JOIN "todos-to-tags" ON "tags".id = "todos-to-tags"."tagID" WHERE "tags"."deleted" IS NULL AND "todos-to-tags"."todoID" IN (x) AND "todos-to-tags"."tagID" IN (y) LIMIT z
With x and y being the IDs returned by query B and z the total count of items returned by query B.
The issue here is that in query C, there is no check on deleted for the join table ("todos-to-tags"."deleted" IS NULL), thus leading to deleted join records being returned, and non deleted join items being left out because of the LIMIT.
When the data is then aggregated and put inside each nest item, some of the tags are null as they were cut off by the LIMIT.
The solution would be to make sure that there is a check for deleted IS NULL on all tables of the query in query C.