Aggregate SUM with Doctrine2 and QueryBuilder

Question

in my repository class I have this, but the query is not working.

 public function getResultsByName($page, $resultsCount, array $request_arr){     
 $qb = $this->createQueryBuilder('xx'); 
 $qb->addSelect('SUM(xx.quantity) as total')
    ->leftJoin('xx.reception', 'x')
    ->addSelect('x')
    ->leftJoin('x.purchase', 'p')
    ->addSelect('p')
    ->leftJoin('p.provider', 'pr')
    ->addSelect('pr')
    ->where('pr.id = :company_id')
    ->setParameter('company_id', $request_arr['company_id']);

 $query = $qb->getQuery(); 

 return parent::getPaginator($query, $page, $resultsCount); }

The error is appearing in my twig template this is the important chunk of it

    {% for result in results %}
<tr>
    <td>{{result.reception.id}}</td>
    <td>{{result.reception.date|date('d-m-Y')}}</td>
    <td>{{result.reception.purchase.id}}</td>
    <td>{{result.reception.purchase.provider.name|upper}} [{{result.reception.purchase.provider.id}}]</td>
    <td>{{result.purchaseProduct.name |upper}} [{{result.purchaseProduct.productCode |upper}}]</td>
    <td>{{result.purchasePrice}}</td>
    <td>{{result.quantity}}</td>
    <td></td>
    <td>{{result.quantityStock}}</td>
</tr>   
{% endfor %}

What do you mean it is not working? What exactly error do you have? — ozahorulia, Jun 23 '13 at 22:27
The error is appearing in TWIG template, this is the eror:Item "sale" for "Array" does not exist in "AgroMyEntityManagerBundle:MyEntityManager:Sale/SaleProductSaleList.html.twig" at line 14, look abov I added my template code — jsf, Jun 23 '13 at 22:30
It's not because of sum. You're trying to access the `result.sale` property that doesn't exist. — ozahorulia, Jun 23 '13 at 22:33
No sorry, well my current error is this: Item "reception" for "Array" does not exist in "AgroMyEntityManagerBundle:MyEntityManager:Purchase/PurchaseProductReceptionList.html.twig" at line 15 (500 Internal Server) — jsf, Jun 23 '13 at 22:40
I have updated template code, but all work perfectly once I remove this line addSelect('SUM(xx.quantity) as total') — jsf, Jun 23 '13 at 22:43

Paul Andrieux · Answer 1 · 2013-06-24T15:42:39.380

3

as you have two selects on your query, your result object is an array of this kind:

array(
    'total' => $total,
    'xx' => array(
        'reception' => $reception,
        'quantityStock' => $quantityStock,
        [...]
    ) 
);

To access your properties in twig, you have to access it like that:

{{result.xx.reception}}
{{result.total}}

edited Jun 24 '13 at 15:42

answered Jun 24 '13 at 12:20

Paul Andrieux

1,836
11
24

even adding xx in my loop like this {% for result in results %} {{result.xx.quantity}} ... {% endfor %} doesn't work. I'm getting just the same error. – jsf Jun 24 '13 at 15:16
I've edited my answer, the "quantity" is aliased as "total" in your SUM, other properties as "reception" must be available from {{result.xx.reception}} – Paul Andrieux Jun 24 '13 at 15:43
Unfortunately neither quantity or the others properties(reception) are accessed, it might be something else wrong. – jsf Jun 24 '13 at 19:54

Aggregate SUM with Doctrine2 and QueryBuilder

1 Answers1