SeanceRepository.php

<?php
/**
 * Created by PhpStorm.
 * User: bb
 * Date: 03.02.15
 * Time: 21:20
 */

namespace WowStudio\Bundle\CinemaSystemBundle\Repository;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr\Join;

class SeanceRepository extends EntityRepository
{

    public function findAll()
    {
        $queryBuilder = $this->getEntityManager()->createQueryBuilder();

        $queryBuilder->from("CinemaSystemBundle:Seance", "s")
            ->leftJoin("CinemaSystemBundle:Reservation", 'r', Join::WITH, 's.idSeance=r.seance')
            ->select("s as seance, sum(r.numberOfTickets) reservedTickets")
            ->groupBy("s");

        $queryResult = $queryBuilder->getQuery()->getResult();

        $seances = array_map(function (array $result) {
            $seance = $result['seance'];
            /* @var $seance \WowStudio\Bundle\CinemaSystemBundle\Entity\Seance */
            $reservedTickets = (int)$result['reservedTickets'];
            $seance->setReservedTickets($reservedTickets);
            return $seance;
        }, $queryResult);

        return $seances;
    }


}