0

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');
mBbkr
  • 212
  • 3
  • 13
  • Don't you want to group by a.id and SUM quantity then? – Jonnix May 27 '16 at 14:18
  • 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

2 Answers2

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');
Miguel G. Flores
  • 802
  • 7
  • 21
-2

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();
Ketav
  • 760
  • 1
  • 8
  • 27