AbdulBazith
1 month ago
277
6
Laravel

Sum the Product quantity of same product id in two tables and sum it laravel

Posted 1 month ago by AbdulBazith

Guys iam working with a project hotel management system.

i have three tables

Table 1: OpenStock with columns

id
open_date
product_id
opening_qty

// its respective model

 public function product()
    {
        return $this->belongsTo('App\Product', 'product_id');
    }

Table 2 : Purchase with columns

id
purchase_date
product_id
product_qty

//its respective model

 public function product()
    {
        return $this->belongsTo('App\Product', 'product_id');
    }

Table 3 :StockIssue with columns

id
product_id
issued_date
issued_qty

//its respective model

 public function product()
    {
        return $this->belongsTo('App\Product', 'product_id');
    }

My products table has columns

id
product_name

what i expect is need to fetch records from OpenStock table, Purchase table, and StockIssue table and add the quantity of same product_id (sum the OpenStock and Purchase) and subtract it from stockIssue table

this what the output i expect

product     openstock       purchasestock   totalstock  issuedstock balance

potato      10              20              30          2           28
tomato      12              20              32          30          2

iam expecting the output like above.kindly some one give idea please..

i was so confused that from where i should start the query. from product table or purchase table or openstock table or issued table.

i know that need to used joins. but whenever joins are involved iam so confused.

so kindly some one suggest ideas please

Please sign in or create an account to participate in this conversation.