CODEWITHSUNDEEP
mysqllaravelpostgresql
SNaRe
SNaRe

Reputation: 2067

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

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();

Upvotes: 0

Views: 123

Answers (1)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

$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.

Upvotes: 1

Related Questions