Incorrect generated SQLs with AnyAggregator #663
Description
Describe the bug
Generated SQLs are wrong for some of the filters using new aggregation features of 5.0 RC. Specifically a different behavior is expected when combining AnyAggregator with another AnyAggregator or with other aggregation function.
To Reproduce
- Let's say I want to find all books that have both tags ID 1 and 2. Throughout development of 5.0 this was possible with correct result by doing:
$books = $this->orm->books->findBy([
ICollection::AND,
[ICollection::AND, new AnyAggregator('1'), 'tags->id' => 1],
[ICollection::AND, new AnyAggregator('2'), 'tags->id' => 2],
]);
Assert::same($books->count(), 1);However now ORM generates query that does not provide correct result:
SELECT "books".*
FROM "books" AS "books"
LEFT JOIN "books_x_tags" AS "books_x_tags" ON ("books"."id" = "books_x_tags"."book_id")
LEFT JOIN "tags" AS "tags_1" ON ("books_x_tags"."tag_id" = "tags_1"."id")
LEFT JOIN "tags" AS "tags_2" ON ("books_x_tags"."tag_id" = "tags_2"."id")
WHERE ((("tags_1"."id" = 1)) AND (("tags_2"."id" = 2)))
GROUP BY "books"."id";Generated query has to be like this:
SELECT "books".*
FROM "books" AS "books"
LEFT JOIN "books_x_tags" AS "books_x_tags_1" ON ("books"."id" = "books_x_tags_1"."book_id")
LEFT JOIN "tags" AS "tags_1" ON ("books_x_tags_1"."tag_id" = "tags_1"."id")
LEFT JOIN "books_x_tags" AS "books_x_tags_2" ON ("books"."id" = "books_x_tags_2"."book_id")
LEFT JOIN "tags" AS "tags_2" ON ("books_x_tags_2"."tag_id" = "tags_2"."id")
WHERE ((("tags_1"."id" = 1)) AND (("tags_2"."id" = 2)))
GROUP BY "books"."id";- Let's say I want to find all books with tag ID 3 and no other tag. In query logic that means I'm looking for all books with tag 3 that have one tag:
$books = $this->orm->books->findBy([
ICollection::AND,
[ICollection::AND, new AnyAggregator('3'), 'tags->id' => 3],
[CompareEqualsFunction::class, [CountAggregateFunction::class, 'tags->id'], 1],
]);
Assert::same($books->count(), 1);Generated query is showing incorrect result again:
SELECT "books".* FROM "books" AS "books"
LEFT JOIN "books_x_tags" AS "books_x_tags" ON ("books"."id" = "books_x_tags"."book_id")
LEFT JOIN "tags" AS "tags_3" ON ("books_x_tags"."tag_id" = "tags_3"."id")
LEFT JOIN "tags" AS "tags__COUNT" ON ("books_x_tags"."tag_id" = "tags__COUNT"."id")
GROUP BY "books"."id", "tags_3"."id"
HAVING ((("tags_3"."id" = 3)) AND (COUNT("tags__COUNT"."id") = 1));This is the correct version:
SELECT "books".*
FROM "books" AS "books"
LEFT JOIN "books_x_tags" AS "books_x_tags_3" ON ("books"."id" = "books_x_tags_3"."book_id")
LEFT JOIN "tags" AS "tags_3" ON ("books_x_tags_3"."tag_id" = "tags_3"."id")
LEFT JOIN "books_x_tags" AS "books_x_tags__COUNT" ON ("books"."id" = "books_x_tags__COUNT"."book_id")
LEFT JOIN "tags" AS "tags__COUNT" ON ("books_x_tags__COUNT"."tag_id" = "tags__COUNT"."id")
GROUP BY "books"."id", "tags_3"."id"
HAVING ((("tags_3"."id" = 3)) AND (COUNT("tags__COUNT"."id") = 1));Since this was working throughout the development, namely one or two years ago, I hope the issue won't be too hard to fix. Great job and good luck with new version.
Versions::
- Database: PostgreSQL 13.13.0
- Orm: 5.0.0-rc1
- Dbal: 5.0.0-rc4