Level 5
@snapey can u suggest me.
dont think that iam often tagging uu. sorry
Sep 17, 2019
7
Level 5
Sum the Product quantity of same product id in two tables and sum it laravel
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
Level 122
dont think that iam often tagging uu. sorry
but you are?
as this is a table of Product then that is where I would start.
The sum, you can do in the view.
Level 5
@snapey sorry, sorry sorry sorry.
but when no one responding, i feel bad. thats why tagging you. if some body just give any suggestion then i can move forward.
once again sorry sorry sorry sorry.
can you give any glimpses of coding please.
Level 122
Make a start. Then ask specific question.
You already know how to list products with opening stock etc
Level 5
@snapey sorry once again, i tried like this
$products=Product::with('OpenStock ','Purchase ','StockIssue ')->get();
actually the adding can be done in blade file. but the sum of same products must be done in controller itself, so how to write??
is this start right?
why iam asking is in my openstock what i did is just fetched the openstock table like this
$openingstocks =OpeningStock::where('hotel_id', Auth::user()->hotel_id)->orderBy('created_at', 'asc')->where('open_date', Carbon::today())->get();
and then need to fetch rate from purchase table
so did the coding like this which u gave
$latestPrices = Purchase::where ('hotel_id',Auth::user()->hotel_id)->whereIn('id',
DB::table('purchase')->where ('hotel_id',Auth::user()->hotel_id)
->selectRaw('max(id) as id')
->groupBy('product_id')
->pluck('id')
)->get();
here no rate . just need to take the qty from all the three tables. but a condition that in purchase and Stockissue table there my more entries for same product_id, so for the it must sum the qty of same product id.
Kindly please help me
Level 54
laravel have a sum helper method
https://laravel.com/docs/5.8/collections#method-sum
try to get your collection accordingly then append ->sum(<whatever_quantity_in_the_collection>)
Level 5
@snapey @siangboon and sorry for delay
Thank you. thank you soo much. it worked for me.
what i did is
$prd = Product::
with([
'openstock' => function ($query) use ($request) {
$query->where('hotel_id', Auth::user()->hotel_id)->whereBetween('open_date', [$request->from_date, $request->to_date])->
selectRaw('product_id,SUM(open_qty) as totalopenqty')
->groupBy('product_id');
},
])->
with([
'specificpurchasebill' => function ($query) use ($request) {
$query->where('hotel_id', Auth::user()->hotel_id)->whereBetween('d_date', [$request->from_date, $request->to_date])->
selectRaw('product_id,SUM(qty) as totalpurchaseqty')
->groupBy('product_id');
},
])->
with([
'storeissue' => function ($query) use ($request) {
$query->where('hotel_id', Auth::user()->hotel_id)->whereBetween('idate', [$request->from_date, $request->to_date])->
selectRaw('product_id,SUM(issued_qty) as totalissuedqty')
->groupBy('product_id');
},
])->get();
ans then passed it blade file. with foreach i got what i expected. thank you guys. @snapey you made me think my self. thank you..
Kindly if possible please suggest idea for the below threads
Please or to participate in this conversation.