Doctrine 2 query with multiple joins

Question

I am stuck with a query and I hope someone outhere can maybe help with my code.

My Classes look sth like this:

Person 1 ------- 1..* Participation 1...* -------- 1 Event

Now I have an attribute on the class Event called 'isActive' and on the Person Class I have an attribute 'isChecked'.

With my Query I want to return all 'isChecked' Persons that dont have a participation in the 'isActive' Event.

This is my Query so far, but it does not return what I need:

$query = $em->createQueryBuilder('c')
        ->select('p')
        ->from('AppBundle:Person', 'p')
        ->andWhere('p.isChecked = true')
        ->leftJoin('p.participation', 'pa')
        ->andWhere('pa IS NULL')
        ->leftJoin('pa.event', 'e')
        ->andWhere('e.isActive = true');

If Anyone can tell me, what I am doing wrong here, would be awesome.

Kind Regards, Sebastian

Answer 1

You need to tweak your joining part by using WITH clause, remove ->andWhere('e.isActive = true') from where filter and move this clause in joining part of your entities, So that only events (rows get joined with pa) returned which are marked as isActive.

DQL

SELECT p
FROM AppBundle:Person p
LEFT JOIN p.participation pa
LEFT JOIN pa.event e WITH e.isActive = true
WHERE p.isChecked = true
AND pa IS NULL

In query builder you could represent it as

$query = $em->createQueryBuilder('c')
        ->select('p')
        ->from('AppBundle:Person', 'p')
        ->leftJoin('p.participation', 'pa')
        ->leftJoin('pa.event', 'e','WITH', 'e.isActive = true')
        ->andWhere('p.isChecked = true')
        ->andWhere('pa IS NULL');