Laravel – How to make subquery in select statement?
In this post we will give you information about Laravel – How to make subquery in select statement?. Hear we will give you detail about Laravel – How to make subquery in select statement?And how to use it also give you demo for it if it is necessary.
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:
IlluminateSupportCollection 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…
Hope this code and post will helped you for implement Laravel – How to make subquery in select statement?. if you need any help or any feedback give it in comment section or you have good idea about this post you can give it comment section. Your comment will help us for help you more and improve us. we will give you this type of more interesting post in featured also so, For more interesting post and code Keep reading our blogs