ItSolutionStuff.com

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, laravel 6, laravel 7, laravel 8, laravel 9, laravel 10 and laravel 11 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...