Symfony2 Query Builder“SELECT where sum children> 0”

I am trying to gather a result set of orders that have children, OrderPayments. I want to have only the orders where the sum of orderpayments.amount > 0.

So far I have this but can't get it to work, I get a

Column not found: Unknown column 'sclr_0' in 'where clause'

    // Create the Query Builder
    $qb = $this->createQueryBuilder('o')->select('o');

    // Joins
    $qb->leftJoin('o.orderPayments', 'op')->addSelect('op')->addSelect('SUM(op.amount) AS total_sum');

    // Criteria
    $qb->where('total_sum > 0');
    $qb->andWhere('o.dateDeleted IS NULL');

    // Return the result
    return $qb->getQuery()->getResult();

Did you tried your sql query in your database?

You query looks like:

SELECT o.*, SUM(op.amount) AS total 
FROM order o 
LEFT JOIN order_payement op on o.id = op.order_id
WHERE total > 0;

This query will not work on mysql, you can't use result of operation on SELECT clause in your WHERE clause.

Your query must looks like:

SELECT o.*, SUM(op.amount) AS total 
FROM order o 
LEFT JOIN order_payement op on o.id = op.order_id
HAVING SUM(op.amount) > 0
GROUP BY o.id;

And your query builder will be:

$qb = $this->createQueryBuilder('o')
   ->select('o');
   ->leftJoin('o.orderPayments', 'op')
   ->addSelect('op')
   ->addSelect('SUM(op.amount) AS total_sum')
   ->Where('o.dateDeleted IS NULL')
   ->having('SUM(op.amount) > 0')
   ->grouBy('o.id');