Laravel Sub queries in select using query builder

Question

I want to get this query using query builder:

SELECT *, 
(    SELECT sum(vendor_quantity) 
     from inventory 
     WHERE product_id = products.id
) as qty from products

I'm stuck with this part

(SELECT sum(vendor_quantity) from inventory where product_id = products.id)

I can do it using raw query but I want to know if there is a way to do it in query builder.

My Table Schema for products:

Schema::create('products', function (Blueprint $table) {
            $table->increments('id');
            $table->string('product_type',50);
            $table->string('product_name',255);
            $table->string('internal_reference',255);
            $table->string('barcode',255);
            $table->decimal('sale_price', 10, 2);
            $table->decimal('cost', 10, 2);
            $table->decimal('weight', 10, 2);
            $table->decimal('volume', 10, 2);
            $table->integer('added_by')->unsigned();
            $table->timestamps();
        });
// Foreign Keys
Schema::table('products', function(Blueprint $table) {
  $table->foreign('added_by')->references('id')->on('users');
});

Stocks Table:

Schema::create('stocks', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('product_id')->unsigned();
            $table->integer('vendor')->unsigned();
            $table->string('vendor_product_code',255);
            $table->string('vendor_product_name',255);
            $table->integer('vendor_quantity');
            $table->decimal('vendor_price', 10, 2);
            $table->date('vendor_produce');
            $table->date('vendor_expiry');
            $table->integer('added_by')->unsigned();
            $table->timestamps();
        });
    // Foreign Keys
    Schema::table('stocks', function(Blueprint $table) {
       $table->foreign('product_id')->references('id')->on('products');
       $table->foreign('vendor')->references('id')->on('customers');
       $table->foreign('added_by')->references('id')->on('users');
    });

[This answer](http://stackoverflow.com/questions/24823915/how-to-select-from-subquery-using-laravel-query-builder) seems to say that you can't do this subquery without using `DB::raw()`. — Tim Biegeleisen, Jul 11 '16 at 15:04
Is this an example of a 3rd party being in the way of productivity? — Rick James, Jul 12 '16 at 04:11
When I have to use a nested select inside a bigger query I always use this approach: http://stackoverflow.com/a/28019768/867418. `toSql()` & `mergeBindings()` are my biggest friends. =D — Ivanka Todorova, Jul 14 '16 at 08:23
Why aren't you creating a view on MySQL side and then, instead of using query builder and creating superbly difficult to read code, you simply have an eloquent model that deals with the view? Also, subqueries and joins are one and the same, you can rewrite your query to use joins instead of subquery. — Mjh, Jul 14 '16 at 08:27

Skipp · Accepted Answer · 2016-07-14T08:30:04.603

can you just add what exactly do you need as output? Like what do you plan to throw at your view so I can give you the eloquent setup. From the migration above it looks like you're missing some tables like "inventory".

In any way - you first need to setup eloquent relationships between your models. For the two above, something like this:

class Stock extends Model{

    public function product(){
        return $this->belongsTo(Product::class);
    }

}

and

class Product extends Model{

    public function stock(){
        return $this->hasMany(Stock::class);
    }

}

Now, that sum of yours has me confused a bit... since vendor_quantity is a column in your stocks table... Do you need to get all the products and the corresponding foreign key values from the stocks table and then sum all the values in the vendor_quantity? If that's the case do something like this:

$products = Products::with('stock')->get();

This will return the eloquent collection with all your products AND foreign key values from the stock table. Since you have the values from the related table you can just iterate through each of those an add it to a variable or just append it to the initial object for passing to your view. For example

$products = Product::with('stock')->get();

    foreach ($products as $key => $product){

        $vendorQuantitySum = $product->stock->sum('vendor_quantity');

        $products[$key]->vendorQuantity = $vendorQuantitySum;

    }

Now, when you pass $products to your view, you can easily get the sum in your view like so:

{{ $product->vendorQuantity }}

I just tested it on my Laravel install and it seems to work :)

Inventory is the stocks, sorry about that. I will give it a try and let you know. Thank you for your time to write this :) — theMohammedA, Jul 14 '16 at 10:23

Laravel Sub queries in select using query builder

1 Answers1