Sort a doctrine's @OneToMany ArrayCollection by field

Question

Close question was enter link description here but I need to more deep sorting:

/**
 * @var ArrayCollection[SubjectTag]
 *
 * @ORM\OneToMany(targetEntity="SubjectTag", mappedBy="subject")
 * @ORM\OrderBy({"position" = "ASC"})
 * @Assert\Valid()
 */
protected $subjectTags;

In subjectTag I have:

/**
 * @var ArrayCollection[tag]
 *
 * @ORM\OneToMany(targetEntity="Tag", mappedBy="subject")
 * @ORM\OrderBy({"name" = "ASC"})
 * @Assert\Valid()
 */
protected $tags;

Now I want to sort by SubjectTag.tags. How can I do that?

EDIT:

Entity1.php: /** * @ORM\ManyToOne(targetEntity="Entity2", referencedColumnName="id", nullable=false) * @Assert\Valid() */ protected $entity2;

Entity2.php: /** * @ORM\ManyToOne(targetEntity="Entity3", referencedColumnName="id", nullable=false) * @Assert\Valid() */ protected $entity3;

Entity3.php:

/**
 * @ORM\Column(type="integer", nullable=true)
 */
protected $position;

And now.. I want have in Entity1 Entity2 sorted by position. How can I do that by default?

I suggest you to do a custom query in your repository in order to do something more flexible. This annotation allows you to quickly get the result order by a property without adding the subjectTags to your initial query result but you will have a n+1 problem if you want to order by $tags anyway. If you give more details I will be able to show you how to do it in an answer — Florian Hermann
@FlorianHermann could u give me example with that custom queries and run it in entity? or maybe I misunderstand something? — WEBCENTER

Florian Hermann Florian Hermann · Accepted Answer · 2019-04-10T09:55:44

As explained in my previous comment, you should do a custom query in your repository class corresponding to your base Entity (You didn't give the name of it).

So in your App\Repository\"YourBaseENtityName"Repository class, you do something like this.

public function findOrderByTags()
{
    return $this
        ->createQueryBuilder('baseEntityAlias')
        ->addSelect('st')
        ->addSelect('t')
        ->leftJoin('baseEntityAlias.subjectTags', 'st')
        ->leftJoin('st.tags', 't')
        ->orderBy('st.position', 'ASC')
        ->addOrderBy('t.name', 'ASC')
        ->getQuery()
        ->getResult();
}

Moreover, I'm not sure about what kind of order you want to perform based on your question. Here the baseEntity->subjectTags will be ordered by their positions and then the baseEntity->subjectTags->tags will be ordered by name.

Now you can call this method from your base entity repository class

Hope it will be helpful for you.

EDIT: Here is a way to introduce a default behavior for your queryBuilder and reuse it.

/**
 * In your EntityRepository add a method to init your query builder
 */
public function createDefaultQueryBuilder(string $alias = 'a')
{
    return $this
        ->createQueryBuilder($alias)
        ->addSelect('st')
        ->addSelect('t')
        ->leftJoin('baseEntityAlias.subjectTags', 'st')
        ->leftJoin('st.tags', 't')
        ->orderBy('st.position', 'ASC')
        ->addOrderBy('t.name', 'ASC');
}

/**
 * In this example, I override the default find method. I don't recommend it thought
 */
public function find($id, $lockMode = null, $lockVersion = null)
{
    return $this
        ->createDefaultQueryBuilder()
        ->where('a.id = :id')
        ->setParameter('id', $id)
        ->getQuery()
        ->getOneOrNullResult();
}

As you can see, I reuse the createDefaultQueryBuilder method in order to get a default behavior with subjectTags and tags init in the relation and ordered in the right way.

Sort a doctrine's @OneToMany ArrayCollection by field

1 Answers