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, Apr 10 '19 at 09:30
@FlorianHermann could u give me example with that custom queries and run it in entity? or maybe I misunderstand something? — WEBCENTER, Apr 10 '19 at 09:44

Florian Hermann · Accepted Answer · 2019-04-10T15:05:30.327

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.

But now I need to do $entity->findOrderByTags() to get all tags sorted by position, right? Can't I do just have in entity sorted? I mean If I get list of entities I need do foreach, and then replace tags in entity with what I get from findOrderByTags? — WEBCENTER, Apr 10 '19 at 13:20
maybe I need only set default orderby for entity? is it possible? — WEBCENTER, Apr 10 '19 at 13:22
There is no way to get a default behavior for that. I recommend you to do only custom query in your EntityRepository and use a method on it for your default behavior. I will edit this answer to show you how to do it. — Florian Hermann, Apr 10 '19 at 14:58
Thanks, but as u mention in answer, overriding default find method is not what You recommended, so what is?:) — WEBCENTER, Apr 11 '19 at 06:32
I recommend you to not override the default find but to create your own method named for example findOneWithDetailsById(int $id) and to only call this one. It's your responsability to organize this. — Florian Hermann, Apr 11 '19 at 10:16

Sort a doctrine's @OneToMany ArrayCollection by field

1 Answers1