Laravel - How to make subquery in select statement?

By Hardik Savani | February 6, 2017 | | 13819 Viewer | Category : Laravel MySql


Share this post:

Sometimes we require to make subquery inside the select statement in Laravel. At that time we are thinking how to select from subquery using in Laravel Query Builder.

In this post i will give you example of subquery in select statement in Laravel 5 application from scratch. For subquery in select statement we are going to use DB::raw(). DB raw function through we can simply make suquery join in Laravel Eloquent Query Builder.

Here as bellow full example, i have three tables as listed bellow:

1)products

2)products_stock

3)products_sell

From this three tables, i require to get products with sum of stock and sell of each records. So first i have to this three records with bellow dummy records with full tables like as bellow:

1)products table data

2)products_stock table data

3)products_sell table data

Now if i use MySQL Query then it will simple as like bellow, But i require to convert this query into Laravel Query Builder.

MySQL Query:

SELECT

products.*,

(SELECT SUM(products_stock.stock) FROM products_stock

WHERE products_stock.product_id = products.id

GROUP BY products_stock.product_id) as product_stock,

(SELECT SUM(products_sell.sell) FROM products_sell

WHERE products_sell.product_id = products.id

GROUP BY products_sell.product_id) as product_sell

FROM `products`

Now above mysql query will be looks like this way in Laravel:

Laravel Query Builder:

$data = DB::table("products")

->select("products.*",

DB::raw("(SELECT SUM(products_stock.stock) FROM products_stock

WHERE products_stock.product_id = products.id

GROUP BY products_stock.product_id) as product_stock"),

DB::raw("(SELECT SUM(products_sell.sell) FROM products_sell

WHERE products_sell.product_id = products.id

GROUP BY products_sell.product_id) as product_sell"))

->get();

Output will be as bellow:

Output:

Illuminate\Support\Collection Object

(

[items:protected] => Array

(

[0] => stdClass Object

(

[id] => 1

[name] => Gold

[created_at] => 2016-09-21 17:37:53

[updated_at] => 2016-09-21 17:37:53

[product_stock] => 150

[product_sell] => 30

)

[1] => stdClass Object

(

[id] => 2

[name] => Silver

[created_at] => 2016-09-21 17:37:53

[updated_at] => 2016-09-21 17:37:53

[product_stock] => 110

[product_sell] => 10

)

[2] => stdClass Object

(

[id] => 3

[name] => Diamond

[created_at] => 2016-09-21 17:37:53

[updated_at] => 2016-09-21 17:37:53

[product_stock] => 200

[product_sell] => 60

)

)

)

I hope it can help you...



We are Recommending you: