CODEWITHSUNDEEP
doctrine-orm
BenMorel
BenMorel

Reputation: 36554

Doctrine DQL count with condition on joined table

I have the following entities:

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;

    // ...
}

A Restaurant can have zero or more Orders.

I'd like to query all the Restaurants along with the total count of Orders, as well as the count of Orders in each status.

I came up with the following query:

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

How can I get the count of pendingOrders, acceptedOrders and rejectedOrders as well?

Upvotes: 2

Views: 1386

Answers (1)

BenMorel
BenMorel

Reputation: 36554

Found the solution:

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

Upvotes: 3

Related Questions