Doctrine Order OneToMany collection

Question

Hello i am trying to order doctrine collection by multiple fields

tried something like this

  /**
     * @var Collection
     * @ORM\OrderBy({"date" = "ASC","TimeBegin" = "ASC"})
     * @ORM\OneToMany(targetEntity="Schedule", mappedBy="event")
     */
    protected $schedules;

This code isn't working

Date field is in format "1927-12-01" timeBegin "00:13:01"

This is my query

 public function getAppointmentDetails(int $eventId): ?Event
    {

        $eventAlias = 'event';
        /** @var EventQueryBuilder $queryBuilder */
        $queryBuilder = $this->createQueryBuilder($eventAlias);

        $queryBuilder->select($eventAlias)
            ->whereEventId($eventId)
            ->withRoom()
            ->withService()
            ->withSchedulesAndInstructorsOrderedByDateAndTime();


        $appointmentDetails = $queryBuilder->getQuery()->getOneOrNullResult();

        return $appointmentDetails;
    }

and my method withSchedulesAndInstructorsOrderedByDateAndTime

 /**
     * With Schedules And Instructors Ordered by Date and Time
     * @return EventQueryBuilder
     */
    public function withSchedulesAndInstructorsOrderedByDateAndTime() : EventQueryBuilder
    {
        $this->join($this->getRootAliases()[0] . '.schedules', 'sc');
        $this->join('sc' . '.instructors', 'in');

        return $this;
    }

Thing is is if i add orderBy my instructor collection will be empty

Not sure if it's possible with Annotations, but you can achieve this easily with a QueryBuiler, see https://stackoverflow.com/questions/11575325/order-by-multiple-columns-with-doctrine — Erwan Haquet, Mar 01 '19 at 15:37
The OrderBy supports multiple fields and your syntax looks to be correct on that account. So what exactly isn't working with your code? Can you provide an example set of the resulting entities list (with the related date and timebegin values that is)? — ejuhjav, Mar 01 '19 at 15:49
I suspect this isn't working since i have another join with instructors entity, right after joining schedules — Ambulance lada, Mar 01 '19 at 16:13
ok, so you had a bit more things going on there compared to the original information :) So just to double check, the same repository function returns something when you drop out the orderBy from the association mapping? Maybe you could print out the prepared sql statements in both cases, modify those to sql queries and run those against your database to see what exactly is different between those two cases? — ejuhjav, Mar 01 '19 at 16:34

score 0 · Answer 1 · answered Mar 08 '19 at 03:48

As the documentation states:

To keep the database impact low, these implicit ORDER BY items are only added to an DQL Query if the collection is fetch joined in the DQL query.

So you need to do a fetch join by adding Schedule to your select :

    /**
     * With Schedules And Instructors Ordered by Date and Time
     * @return EventQueryBuilder
     */
    public function withSchedulesAndInstructorsOrderedByDateAndTime() : EventQueryBuilder
    {
        $this->addSelect('sc');
        $this->join($this->getRootAliases()[0] . '.schedules', 'sc');
        $this->join('sc' . '.instructors', 'in');

        return $this;
    }

Doctrine Order OneToMany collection

1 Answers1