doctrine-orm - Doctrine DQL 计数与连接表的条件

Question

我有以下实体:

class Restaurant
{
    // ...
}

class Order
{
    /**
     * The Restaurant this order belongs to.
     *
     * @var Restaurant
     */
    protected $restaurant;

    /**
     * The status of this order: 'pending', 'accepted' or 'rejected'.
     *
     * @var string
     */
    protected $status;

    // ...
}

餐厅可以有零个或多个订单。

我想查询所有餐厅以及订单总数，以及每个状态下的订单数。

我想出了以下查询:

SELECT r, COUNT(o.id) AS totalOrders
FROM Restaurant r
LEFT JOIN Order o WITH o.restaurant = r
GROUP BY r.id

如何获得 pendingOrders 的计数, acceptedOrders和 rejectedOrders还有？

Answer 1

找到了解决办法:

SELECT r, COUNT(o.id) AS totalOrders,
  SUM(CASE WHEN o.status = 'pending' THEN 1 ELSE 0 END) AS pendingOrders,
  SUM(CASE WHEN o.status = 'accepted' THEN 1 ELSE 0 END) AS acceptedOrders,
  SUM(CASE WHEN o.status = 'rejected' THEN 1 ELSE 0 END) AS rejectedOrders
FROM Restaurant r
LEFT JOIN Order o WITH o.restaurant = r
GROUP BY r.id