Aggregate subquery in doctrine2 QueryBuilder

Question

I would like to calculate totals for all rooms. They are joined to charges:

    return $this->createQueryBuilder('r')
        ->select("COUNT(r) AS num,
            SUM(r.rent) AS rent,
            SUM(c.price) AS charges")
        ->leftJoin('r.charges', 'c')
        ->getQuery()
        ->getSingleResult();

Obviously it throws exception because result is not single. How to add this subquery to builder ?

Thanks

Answer 1

General rule of thumb with SQL, when you include an aggregate function (COUNT(), SUM(), MIN(), MAX(), etc) then you also need a GROUP BY clause. That should be all you need here

return $this->createQueryBuilder('r')
    ->select("COUNT(r) AS num")
    ->addSelect("SUM(r.rent) AS rent")
    ->addSelect("SUM(c.price) AS charges")
    ->leftJoin('r.charges', 'c')
    // Group aggregates per room
    ->groupBy('r.id')
    ->getQuery()
    ->getSingleResult();