Order by different table column on HasMany relationship condition

[stepapo]

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" ASC

Expected 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" ASC

But I am not sure if that does not break something else.

Versions

• Postgres 12.6
• Orm 4.0
• Dbal 4.0

[hrach]

Hi, thank you for great detailed report. This seems rather a big issues but not sure if fixing this in patch version would be ok, probably not.

I will try to fix it with great refactoring that is needed in this part of the Orm.