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 seems to say that you can't do this subquery without using DB::raw(). — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 11, 2016 at 15:04
Is this an example of a 3rd party being in the way of productivity? — Rick James
– Rick James, Commented Jul 12, 2016 at 4:11
When I have to use a nested select inside a bigger query I always use this approach: stackoverflow.com/a/28019768/867418. toSql() & mergeBindings() are my biggest friends. =D — Ivanka Todorova
– Ivanka Todorova, Commented Jul 14, 2016 at 8: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
– Mjh, Commented Jul 14, 2016 at 8:27

Skipp · Accepted Answer · 2016-07-14 08:30:04Z

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 :)

Collectives™ on Stack Overflow

Laravel Sub queries in select using query builder

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related