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');
});