Symfony / Doctrine COUNT Group By and Left JOIN

Question

I have two Tables in symfony join by ManyToOne bidirectional relationship. articles and dates.

I would like to sum all the d.quantity for the same a.id How can I do that ?

I try :

In my table (dates) i have :

+----------------------------
| id   | a.id  |   quantity
+----------------------------
| 1    | 4     |      1 
| 2    | 4     |      3
| 3    | 6     |      4
| 4    | 5     |      6
| 5    | 4     |      15 
----------------------------

$allIndispo = $qb
    ->select('a2.id')
    ->leftJoin('a2.dates', 'd')
    ->where('(a2.quantity - COUNT(d.quantity)) <= 0')
    ->groupBy('d.quantity')
    ->orderBy('a2.id', 'ASC');

Yes but when i change group by a2.id i get : SQLSTATE[HY000]: General error: 1111 Invalid use dof GROUP — mBbkr, May 27 '16 at 15:20
That may be to do with your WHERE clause than the GROUP BY clause. http://stackoverflow.com/questions/2330840/mysql-invalid-use-of-group-function might be of relevance. — Jonnix, May 27 '16 at 15:23

Miguel G. Flores · Accepted Answer · 2016-05-27T16:33:45.167

2

You probably need to use HAVING instead of WHERE, and group by the a.id, not for quantity, and project (in the select) the result.

$allIndispo = $qb
    ->select('a2.id, SUM(a2.quantity) as total')
    ->leftJoin('a2.dates', 'd')
    ->groupBy('a2.id')
    ->having('(a2.quantity - COUNT(d.quantity)) <= 0')
    ->orderBy('a2.id', 'ASC');

edited May 27 '16 at 16:33

answered May 27 '16 at 16:25

Miguel G. Flores

802
7
21

score -2 · Answer 2 · answered May 27 '16 at 17:02

You should read this

    $repository = $this->getDoctrine()
    ->getRepository('AppBundle:Product');

// createQueryBuilder automatically selects FROM AppBundle:Product
// and aliases it to "p"
$query = $repository->createQueryBuilder('p')
    ->where('p.price > :price')
    ->setParameter('price', '19.99')
    ->orderBy('p.price', 'ASC')
    ->getQuery();

$products = $query->getResult();
// to get just one result:
// $product = $query->setMaxResults(1)->getOneOrNullResult();

Symfony / Doctrine COUNT Group By and Left JOIN

2 Answers2

Linked