Creating query with laravel builder for Subquery,filter,count at the same

Question

I have a query like this. I tried to convert it into query builder style. But since it is too complex I have trouble with it.

I love to use whereIn . But if I use raw DB query I must code it myself ( get collection put comma , etc ) So that I am looking for a way to make it laravel query builder friendly.

SELECT
    total_amount_until_now/total_orders_until_now as avg_order_value_now,
    total_amount_until_a_month_ago/total_orders_until_a_month_ago as avg_order_value_until_a_month_ago

FROM(
SELECT
  COUNT(DISTINCT a.order_id)
    FILTER (WHERE CURRENT_DATE >= b.order_creation_date AND b.seller_id IN (1, 3))                     AS total_orders_until_now,
  SUM(invoice_amount)
    FILTER (WHERE CURRENT_DATE >= b.order_creation_date AND b.seller_id IN (1, 3))                     AS total_amount_until_now,
  COUNT(DISTINCT a.order_id)
    FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date AND b.seller_id IN (1, 3)) AS total_orders_until_a_month_ago,
  SUM(invoice_amount)
    FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date AND b.seller_id IN (1, 3)) AS total_amount_until_a_month_ago
FROM
  order_items a
  INNER JOIN orders b ON a.order_id = b.order_id)  xyz"

This is my try which I couldn't succeed

DB::select()
        ->selectSub(`total_amount_until_now`, `avg_order_value_now`)
        ->selectSub(`/`, `avg_order_value_now`)
        ->selectSub(`total_orders_until_now`, `avg_order_value_now`)
        ->selectSub(`total_amount_until_a_month_ago`, `avg_order_value_until_a_month_ago`)
        ->selectSub(`/`, `avg_order_value_until_a_month_ago`)
        ->selectSub(`total_orders_until_a_month_ago`, `avg_order_value_until_a_month_ago`)
        ->from(`SELECT COUNT(DISTINCT a.order_id) FILTER ( WHERE CURRENT_DATE >= b.order_creation_date AND b.seller_id IN (1, 3) ) AS total_orders_until_now, SUM(invoice_amount) FILTER ( WHERE CURRENT_DATE >= b.order_creation_date AND b.seller_id IN (1, 3) ) AS total_amount_until_now, COUNT(DISTINCT a.order_id) FILTER ( WHERE CURRENT_DATE - INTERVAL 1 month > b.order_creation_date AND b.seller_id IN (1, 3) ) AS total_orders_until_a_month_ago, SUM(invoice_amount) FILTER ( WHERE CURRENT_DATE - INTERVAL 1 month > b.order_creation_date AND b.seller_id IN (1, 3) ) AS total_amount_until_a_month_ago FROM order_items a INNER JOIN orders b ON a.order_id = b.order_id as xyz`)
        ->get();

Sorry but that is wicked unreadable. Is there no way to just use plain SQL in laravel? I promise that the speed of which your db index runs is faster than anything running in PHP. — Alexander Kleinhans
– Alexander Kleinhans, Commented Apr 4, 2018 at 19:51

Jonas Staudenmeir · Accepted Answer · 2018-04-04 20:12:32Z

$from = DB::table('order_items AS a')
    ->selectRaw('COUNT(DISTINCT a.order_id) FILTER (WHERE CURRENT_DATE >= b.order_creation_date AND b.seller_id IN (?, ?)) AS total_orders_until_now', [1, 3])
    ->selectRaw('SUM(invoice_amount) FILTER (WHERE CURRENT_DATE >= b.order_creation_date AND b.seller_id IN (?, ?)) AS total_amount_until_now', [1, 3])
    ->selectRaw("COUNT(DISTINCT a.order_id) FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date AND b.seller_id IN (?, ?)) AS total_orders_until_a_month_ago", [1, 3])
    ->selectRaw("SUM(invoice_amount) FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date AND b.seller_id IN (?, ?)) AS total_amount_until_a_month_ago", [1, 3])
    ->join('orders AS b', 'a.order_id', '=', 'b.order_id');
DB::query()
    ->selectRaw('total_amount_until_now/total_orders_until_now as avg_order_value_now')
    ->selectRaw('total_amount_until_a_month_ago/total_orders_until_a_month_ago as avg_order_value_until_a_month_ago')
    ->fromSub($from, 'xyz')
    ->get();

You probably can also use bindings (?) for '1 month' but I'm not totally sure. You have to try that.

Collectives™ on Stack Overflow

Creating query with laravel builder for Subquery,filter,count at the same

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related