orm DDC-3448: @OrderBy on eager @OneToMany does not work

Jira issue originally created by user backbone:

generated code when eagerly fetching:

SELECT 
  t0.id AS id_8,
  t19.category*id AS category_id*23 
FROM 
  category t0 
  LEFT JOIN attribute*category t19 ON t19.category*id = t0.id 
WHERE 
  t0.id = ?

when fetching lazy the collection query has the ORDER BY clause

Dec 13 '14 08:12 doctrinebot

Copied from #3885: It's not an easy solution, because ordering needs only to apply for the association, not for the top-level entities though. This would require the join to not happen as part of main query, but immediately after.

May 19 '16 18:05 guilhermeblanco

Run into the same problem. Does anyone has any workaround for this?

EDIT: found a work around http://stackoverflow.com/questions/16705425/usort-a-doctrine-common-collections-arraycollection#answer-24246304

Oct 20 '16 11:10 aistis-

Current workaround is multi-step hydration - https://ocramius.github.io/blog/doctrine-orm-optimization-hydration/

Not fun, not simple, but works and is fairly efficient.

Oct 25 '16 03:10 Ocramius

Any update on this?

Feb 18 '19 11:02 quisse

This might be fixed by https://github.com/doctrine/orm/pull/7850 but never got a reference.

Feb 16 '20 19:02 beberlei

The problem still exists:

Here all elements are sorted by position:

    /**
     * @ORM\OneToMany(targetEntity=File::class, mappedBy="material", cascade={"all"}, orphanRemoval=true, fetch="LAZY")
     * @ORM\OrderBy({"position" = "ASC"})
     */
    private Collection $files;

here "position" is ignored and all fields are sorted by primary key:

    /**
     * @ORM\OneToMany(targetEntity=File::class, mappedBy="material", cascade={"all"}, orphanRemoval=true, fetch="EAGER")
     * @ORM\OrderBy({"position" = "ASC"})
     */
    private Collection $files;

Used packages: doctrine/orm - 2.9.6 doctrine/doctrine-bundle - 2.5.7 doctrine/annotations - 1.14.3