Order by different table column on HasMany relationship condition #518
Description
Describe the bug
After filtering by HasMany property, when I want to order result by a column from a joined table, it only works in MySQL. In Postgres, the query fails.
To Reproduce
public function testOrderByDifferentTableColumnOnHasManyRelationshipCondition(): void
{
$publisher = $this->orm->publishers->getByIdChecked(1);
$books = $publisher->books->toCollection()->findBy([
'tags->id' => 1,
])->orderBy('author->name');
Assert::same(1, $books->count());
}The above test results in following error:
Nextras\Dbal\Drivers\Exception\QueryException: ERROR: column "author.name" must appear in the GROUP BY clause or be used in an aggregate function
The generated query is as follows:
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" ON ("books_x_tags"."tag_id" = "tags"."id")
LEFT JOIN "public"."authors" AS "author" ON ("books"."author_id" = "author"."id")
WHERE (("tags"."id" = 1))
AND ("books"."publisher_id" IN (1))
GROUP BY "books"."id"
ORDER BY "author"."name" ASCExpected behavior
The generated query has to be different. I am not sure which would be the best choice. One solution would be to include column(s) used for ordering in GROUP BY statement (as the error says). Or use DISTINCT instead and include order column(s) in SELECT statement. Like this:
SELECT DISTINCT "books".*, "author"."name"
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" ON ("books_x_tags"."tag_id" = "tags"."id")
LEFT JOIN "public"."authors" AS "author" ON ("books"."author_id" = "author"."id")
WHERE (("tags"."id" = 1))
AND ("books"."publisher_id" IN (1))
ORDER BY "author"."name" ASCBut I am not sure if that does not break something else.
Versions
- Postgres 12.6
- Orm 4.0
- Dbal 4.0