I have Symfony 4 width Doctrine, two entities/tables with relation Fixture (one) - Picture (many). Both tables has column with name "order". I am loading (eagerly) all fixtures and its pictures. I am trying to sort query result only by root entity.
It's working as expected when not using "addSelect" function.
$qb = $repository->createQueryBuilder('fixture');
$qb->innerJoin('fixture.pictures', 'pictures');
$qb->addOrderBy('fixture.order', 'ASC');
The result is:
SELECT f0_.name AS name_0, f0_.color AS color_1, f0_.`order` AS order_2, f0_.id AS id_3, f0_.valid_from AS valid_from_4, f0_.valid_to AS valid_to_5, f0_.disabled AS disabled_6 FROM fixture f0_ INNER JOIN fixture_picture f1_ ON f0_.id = f1_.fixture_id ORDER BY f0_.`order` ASC
But when I change it to
$qb = $repository->createQueryBuilder('fixture');
$qb->innerJoin('fixture.pictures', 'pictures');
$qb->addSelect('pictures'); //eager loading
$qb->addOrderBy('fixture.order', 'ASC');
I am getting:
SELECT f0_.name AS name_0, f0_.color AS color_1, f0_.`order` AS order_2, f0_.id AS id_3, f0_.valid_from AS valid_from_4, f0_.valid_to AS valid_to_5, f0_.disabled AS disabled_6, f1_.id AS id_7, f1_.`order` AS order_8, f1_.file_name AS file_name_9, f1_.file_path AS file_path_10, f1_.fixture_id AS fixture_id_11 FROM fixture f0_ INNER JOIN fixture_picture f1_ ON f0_.id = f1_.fixture_id ORDER BY f0_.`order` ASC, f1_.`order` ASC
I am really not sure why is there "f1_.order ASC". I also tried it with
$qb->addOrderBy('pictures.order', 'ASC');
and for this case it's working (it's sorted only by pictures).
TL;DR;
When using "$qb->addSelect('pictures');" in query builder, extra order by ", f1_.order ASC" is added in SQL query even only root entity should be sorted. It's working when "addSelect" is not used or when query is sorted by joined table 'pictures.order'
