Adonis Query Builder: Conditional Count

Question

so what I want to do is group list by date, and on that group, I want to count a column with a specific value.

Here is a sample values of that table:

[
  {
     id: 1,
     delivery_date: 2020-06-01,
     order_status: 0,
     quantity: 2
  },
  {
     id: 2,
     delivery_date: 2020-06-01,
     quantity: 2,
     order_status: 0,
  },

  {
     id: 3
     delivery_date: 2020-06-01,
     quantity: 2,
     order_status: 1,
  },
]

I now queried this

...
await Order
      .query()
      .select('delivery_date')
      .groupBy('delivery_date')
      .count('delivery_date as total_orders')

Result:

delivery_date: "2020-06-01"
total_orders: "6"

The Result I'm trying to achieve:

delivery_date: "2020-06-01",
total_orders: 6,
// The count of order_status with value of 0
pending_orders: 2 ,
// The count of order_status with value of 1
confirmed_orders: 2

so that would group all the orders with the same delivery_date, and return the date, and the total orders in it as total_orders.

what I want to do now is to count all order_status with a value of 0 and order_status with a value of 1. Been stuck from this for hours now, Thanks in advance!

UPDATE Gave up temporarily on using the Query Builders, and I achieved what I want using Database.raw(), but I still need to use the Query Builders for project consistency sake, So can anyone help me convert this into a QueryBuilder version?

await Database
      .raw(
        'SELECT delivery_date, ' +
        'COUNT(*) FILTER (WHERE order_status = 0) as pending_orders, ' +
        'COUNT(*) FILTER (WHERE order_status = 1) as confirmed_orders, ' +
        'COUNT(*) FILTER (WHERE order_status = 2) as suspended_orders ' +
        'FROM orders ' +
        'GROUP BY delivery_date'
      )

score 1 · Accepted Answer · answered Jun 19 '20 at 05:34

I have solved it, but I still have to use Database.raw

It looks something like this:

await Order
      .query()
      .select('delivery_date')
      .select(Database.raw('COUNT(*) FILTER (WHERE order_status = 0) as pending_orders'))
      .select(Database.raw('COUNT(*) FILTER (WHERE order_status = 1) as confirmed_orders'))
      .select(Database.raw('COUNT(*) FILTER (WHERE order_status = 2) as suspended_orders'))
      .groupBy('delivery_date')

Adonis Query Builder: Conditional Count

1 Answers1