Order a result by it's PersistentCollection

Question

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?

The annotation "@ORM\OrderBy({"filiale" = "DESC"})" just means that it will sort your related objects in when you do $tour->getObjects(); — Alsatian, Jul 04 '16 at 12:12

Alsatian · Answer 1 · 2016-07-04T12:41:15.280

0

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 <=.

edited Jul 04 '16 at 12:41

answered Jul 04 '16 at 12:17

Alsatian

3,086
4
25
40

I tried that as well and it says `[Syntax Error] line 0, col 71: Error: Expected =, <, <=, <>, >, >=, !=, got 'o' ` – PrimuS Jul 04 '16 at 12:22
The syntaxe 'JOIN p.object o' is the normal method to use JOIN in DQL (without 'WHERE p.object = ...' ). I don't know why your write 'JOIN AppBundle:Schedule s' ? May be is that the problem. – Alsatian Jul 04 '16 at 12:28
See here : http://doctrine-orm.readthedocs.io/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#joins – Alsatian Jul 04 '16 at 12:29
That helped to get it working like this `SELECT p FROM AppBundle:Tour p JOIN p.schedule s JOIN p.object o WHERE p.id = s.tour AND p.network = :network AND s.start BETWEEN :todayStart AND :todayEnd ORDER BY o.filiale DESC`BUT it is still not ordered?! – PrimuS Jul 04 '16 at 12:36
Remove 'WHERE p.id = s.tour' doctrine does that for you. – Alsatian Jul 04 '16 at 12:39
How do you render the result ? – Alsatian Jul 04 '16 at 13:34

Order a result by it's PersistentCollection

1 Answers1