Doctrine2 Query Builder Left Join with Select

Question

I want to implement this SQL using doctrine2 query builder:

SELECT c.*, COUNT(s.id) AS studentCount
FROM classes c
LEFT JOIN (
    SELECT * 
    FROM student_classes
    WHERE YEAR =  '2012'
) sc ON c.id = sc.class_id
LEFT JOIN students s ON sc.student_id = s.id
GROUP BY c.id

I tried this one but didn't work

$qb = $this->getEntityManager()
    ->getRepository('Classes')
    ->createQueryBuilder('c');
$qb->select('c.id AS id, c.name AS name, COUNT(s) AS studentCount');
$qb->leftJoin(
    $qb->select('sc1')
        ->from('StudentClasses', 'sc1')
        ->where('sc1.year = :year')
        ->setParameter('year', $inputYear), 
    'sc2'
);
$qb->leftJoin('sc2.students', 's');
$qb->groupBy('c.id');
return $qb->getQuery()->getScalarResult();

or should I use nativeSQL instead?

any help would be appreciated, thanks.

Answer 1

What are you trying to do is really interesting, because JOIN on a SELECT seems to not be supported by Doctrine2 with DQL or QueryBuilder. Of course, you can try with a native query.

However, to answer to your question, I believe that you don't need to make a JOIN on a SELECT. Simply, JOIN on StudentClasses and then add a condition in the WHERE about the $year! The WHERE clause is made for that.

Answer 2

You can use WITH clause to join entity with additional check, For your subquery you can write the same using left join with year filter, In join part i have used c.studentClasses based on the assumption that in Classes entity you have some mapped property for StudentClasses entity

$qb = $this->getEntityManager()
    ->getRepository('Classes')
    ->createQueryBuilder('c');
$qb->select('c.id AS id, c.name AS name, COUNT(s) AS studentCount');
$qb->leftJoin('c.studentClasses','sc2', 'WITH', 'sc2.year = :year');
$qb->leftJoin('sc2.students', 's');
$qb->setParameter('year', $inputYear);
$qb->groupBy('c.id');