通过它的PersistentCollection订购结果

I have a function to get all "Tours By Network" for today.

I have a custom repository like this:

public function toursTodayByNetwork($network){

    $todayStart = \DateTime::createFromFormat( "Y-m-d H:i:s", date("Y-m-d 00:00:00") );
    $todayEnd = \DateTime::createFromFormat( "Y-m-d H:i:s", date("Y-m-d 23:59:59") );

    return $this->getEntityManager()
        ->createQuery(
            'SELECT p FROM AppBundle:Tour p JOIN AppBundle:Schedule s WHERE p.id = s.tour AND p.network = :network AND s.start >= :todayStart AND s.start <= :todayEnd'
        )
        ->setParameter('todayStart',$todayStart)
        ->setParameter('todayEnd',$todayEnd)
        ->setParameter('network',$network)
        ->getResult();

}

The Tour Entity has an OneToMany relation to an Object Entity.

I would now like to sort the whole thing by the filiale column in AppBundle:Object, which as you can see is not in the query, so I cannot simply just ORDER BY.

I tried

/**
 * @ORM\OneToMany(targetEntity="Object", mappedBy="tour")
 * @ORM\OrderBy({"filiale" = "DESC"})
 */
protected $object;

But that does not change anything.

THIS looks like something I have to do, but I don't understand where to put the EventListener? I also believe I maybe only need to alter the query?

You have to join your object table :

public function toursTodayByNetwork($network){

    $todayStart = \DateTime::createFromFormat( "Y-m-d H:i:s", date("Y-m-d 00:00:00") );
    $todayEnd = \DateTime::createFromFormat( "Y-m-d H:i:s", date("Y-m-d 23:59:59") );

    return $this->getEntityManager()
        ->createQuery(
            'SELECT p FROM AppBundle:Tour p JOIN p.schedule s JOIN p.object o WHERE p.network = :network AND s.start BETWEEN :todayStart AND :todayEnd ORDER BY o.filiale DESC'
        )
        ->setParameter('todayStart',$todayStart)
        ->setParameter('todayEnd',$todayEnd)
        ->setParameter('network',$network)
        ->getResult();

}

For the date condition, BETWEEN is a better practice as >= AND <=.