Converting query into Laravel Query Builder with Count, filter, and WhereIn

Question

I have a query and I tried to convert it to laravel query builder style. However I couldn't succeed.

This is my query

SELECT
  count(*)
    FILTER (WHERE order_status_id IN (5, 4, 15))        AS ready_for_delivery,
  count(*)
    FILTER (WHERE order_status_id IN (6))               AS out_for_delivery,
  count(*)
    FILTER (WHERE order_status_id IN (4, 10, 9, 12, 7)) AS completed_order,
  count(*)
    FILTER (WHERE order_status_id IN (11, 16, 17, 13))  AS waiting_for_cancellation_return_change
FROM order_item_histories;

This is what I got from http://www.midnightcowboycoder.com/ which obviously doesn't work. But it is a starting point

DB::table('order_item_histories')
        ->selectSub('count', 'ready_for_delivery')
        ->selectSub('FILTER', 'ready_for_delivery')
        ->selectSub('count', 'out_for_delivery')
        ->selectSub('FILTER', 'out_for_delivery')
        ->selectSub('count', 'completed_order')
        ->selectSub('FILTER', 'completed_order')
        ->selectSub('count', 'waiting_for_cancellation_return_change')
        ->selectSub('FILTER', 'waiting_for_cancellation_return_change')
        ->get();

This query works but it is just raw. I know no harm but..

        $aa = DB::table('order_item_histories')
            ->select(DB::raw('count(*)
    FILTER (WHERE order_status_id IN (5, 4, 15))        AS ready_for_delivery,
  count(*)
    FILTER (WHERE order_status_id IN (6))               AS out_for_delivery,
  count(*)
    FILTER (WHERE order_status_id IN (4, 10, 9, 12, 7)) AS completed_order,
  count(*)
    FILTER (WHERE order_status_id IN (11, 16, 17, 13))  AS waiting_for_cancellation_return_change'))
            ->get();

I think multiple calls like this will work ->selectSub('count(*) FILTER (WHERE order_status_id IN (5, 4, 15))', 'ready_for_delivery') but it's perfectly fine to write raw queries is this case, just beware of SQL injection — Razor
– Razor, Commented Apr 3, 2018 at 21:08

Jonas Staudenmeir · Accepted Answer · 2018-04-04 00:01:37Z

1

DB::table('order_item_histories')
    ->selectRaw('count(*) FILTER (WHERE order_status_id IN (?, ?, ?)) AS ready_for_delivery', [5, 4, 15])
    ->selectRaw('count(*) FILTER (WHERE order_status_id IN (?)) AS out_for_delivery', [6])
    ->selectRaw('count(*) FILTER (WHERE order_status_id IN (?, ?, ?, ?, ?)) AS completed_order', [4, 10, 9, 12, 7])
    ->selectRaw('count(*) FILTER (WHERE order_status_id IN (?, ?, ?, ?))  AS waiting_for_cancellation_return_change', [11, 16, 17, 13])
    ->get();

answered Apr 4, 2018 at 0:01

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

afsal c · Accepted Answer · 2018-04-04 07:27:25Z

1

Try using case instead of filter

$users = DB::table('order_item_histories')
                 ->select(DB::raw('SUM(CASE WHEN order_status_id IN (5, 4, 15) THEN 1 ELSE 0 END) AS ready_for_delivery'))
                 ->select(DB::raw('SUM(CASE WHEN order_status_id IN (6) THEN 1 ELSE 0 END) AS out_for_delivery'))
                 ->select(DB::raw('SUM(CASE WHEN order_status_id IN (4, 10, 9, 12, 7) THEN 1 ELSE 0 END) AS completed_order'))
                 ->select(DB::raw('SUM(CASE WHEN order_status_id IN (11, 16, 17, 13) THEN 1 ELSE 0 END) AS waiting_for_cancellation_return_change'))
                 ->get();

answered Apr 4, 2018 at 7:27

afsal c

6204 silver badges12 bronze badges

2 Comments

SNaRe Over a year ago

Didn't give me expected output.

afsal c Over a year ago

What result did you got

Collectives™ on Stack Overflow

Converting query into Laravel Query Builder with Count, filter, and WhereIn

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related