2

I have a query like this: 

return $this->createQueryBuilder('supervision')
        ->join('supervision.supervisionEvents', 'supervisionEvents')
        ->andWhere('supervisionEvents.episode = :episode')
        ->setParameter('episode', $episode)
        ->getQuery()
        ->getResult()
        ;

I want to get all supervisions but the supervisionEvents I want to filter. In result I only want to have SupervisionEvents with episode = :episode.

I used join, leftJoin and innerJoin. Nothing works like desired.

I tried to have a condition on the join: 

 ->innerJoin('s.supervisionEvents', 'supervisionEvent', 'WITH', 'supervisionEvent.episode = :episode')

But thats also not working!

Selaka Nanayakkara
  • 3,296
  • 1
  • 22
  • 42
Slowwie
  • 1,146
  • 2
  • 20
  • 36
  • 1
    This is the expected behaviour, if you only want to have some SupervisionEvents you need to filter them with php or change the query to search for SupervisionEvents only. – Vyctorya Dec 11 '19 at 15:00
  • the expected behavior is, that u can not have a join with a condition? – Slowwie Dec 11 '19 at 15:03
  • 1
    You can have a join with a condition, but this will only change the found supervision entities. Your query (with innerjoin) means something like 'get all supervision entities where at least one event has episode = :episode'. With doctrine you don't have to think like normal sql but about objects, doctrine will always load all related entities and you can not change that. – Vyctorya Dec 11 '19 at 15:09

1 Answers1

-1

You are actually pretty close. You want to filter only on the join, so use a left join with condition to the join clause:

return $this->createQueryBuilder('supervision')
    ->addSelect('supervisionEvents')
    ->leftJoin('supervision.supervisionEvents', 'supervisionEvents', Join::WITH, 'supervisionEvents.episode = :episode')
    ->setParameter('episode', $episode)
    ->getQuery()
    ->getResult()
    ;

It is needed also to select the joined relation, so that it is hydrated on the result.

References

Jannes Botis
  • 11,154
  • 3
  • 21
  • 39