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

[doctrinebot]

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

[guilhermeblanco]

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.

[aistis-]

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

[Ocramius]

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.

[quisse]

Any update on this?

[beberlei]

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

[secit-pl]

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

[derrabus]

The problem still exists: […] Used packages: doctrine/orm - 2.9.6

That release is almost two years old. I guess that does not really count as "still exists". 😉

[krugerman007]

What about updates?

doctrine/orm - 2.16.1 - problem exists :(

[schwierBD]

Any Update?

[oleg-andreyev]

Fix looks like quite easy: \Doctrine\ORM\UnitOfWork::eagerLoadCollections when calling loadAll, just pass orderBy mapping.

¯_(ツ)_/¯