php - Symfony2 查询生成器 "SELECT where sum children > 0"

Question

我正在尝试收集包含子订单 OrderPayments 的订单结果集。我只想获取 order payment 总和 > 0 的订单。

到目前为止，我已经有了这个，但无法让它工作，我得到了

未找到列:“where 子句”中存在未知列“sclr_0”

    // 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();

Answer 1

您是否在数据库中尝试过 SQL 查询？

您的查询如下所示:

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

此查询不适用于 mysql，您不能在 WHERE 子句中使用 SELECT 子句的运算结果。

您的查询必须类似于:

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;

您的查询生成器将是:

$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');