How to add subquery in laravel query builder

Question

I'm trying to convert my sql query to Laravel Query From this

SELECT products.name,
(SELECT SUM(orders.date BETWEEN from_date AND to_date)
FROM orders WHERE orders.product_id = products.id ) as order_counts
FROM products;

Then I tried converting to laravel query builder but it results to an error:

Product::select('name', 
DB::raw("SUM(orders.date 
BETWEEN $request->from_date AND $request->to_date) AS total_orders
FROM orders WHERE orders.product_id = products.id"))
->get();

The error is syntax error on the $request->from_date inside db::raw.

what's kind of error?

Widada
– Widada

2022-10-17 01:40:09 +00:00
Commented Oct 17, 2022 at 1:40 — Widada
– Widada, Commented Oct 17, 2022 at 1:40
results in an error... provides no error description...

Robert
– Robert

2022-10-17 01:52:46 +00:00
Commented Oct 17, 2022 at 1:52 — Robert
– Robert, Commented Oct 17, 2022 at 1:52
I edited the question and add the error message

boldsinas101
– boldsinas101

2022-10-17 05:30:19 +00:00
Commented Oct 17, 2022 at 5:30 — boldsinas101
– boldsinas101, Commented Oct 17, 2022 at 5:30

Umi Umi · Accepted Answer · 2022-10-19 06:18:23Z

1

You can use the withCount method: Documentation

Product::select('name')->withCount([
    'orders' => function (Builder $query) using ($from_date, $to_date) {
        $query->whereBetween('date', [$from_date, $to_date]);
     }
]);

I saw you have edited your issue, and describe your error. When using variables in string you should put them in braces.

Product::select('name', 
  DB::raw(
    "SUM(orders.date BETWEEN {$request->from_date} AND {$request->to_date}) AS total_orders FROM orders WHERE orders.product_id = products.id"
  )
)->get();

edited Oct 19, 2022 at 6:18

answered Oct 17, 2022 at 2:09

Umi Umi

992 bronze badges

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

2 Comments

boldsinas101 Over a year ago

How about I had these subquery:

(SELECT SUM(orders.order_date BETWEEN '2022-10-06' AND '2022-10-12') - SUM(orders.order_date BETWEEN '2022-10-06' - INTERVAL 14 DAY AND '2022-10-12' - INTERVAL 7 DAY)  FROM products WHERE orders.product_id = products.id) as diff_order,

Umi Umi Over a year ago

You will have no other choice than using DB::raw method

Collectives™ on Stack Overflow

How to add subquery in laravel query builder

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related