Level 88
Have you looked in the documentation for creating joins?
Documentation: https://laravel.com/docs/7.x/queries#joins
May 2, 2020
8
Level 4
How to Join multiple table and order by date
Hello, I need a help i have three tables Product , loadings, and stocksin each tables have date,
I want to select the specific [ product id] and Fetch data from this table, loadings and stocksin in current month.
Product table
Id. Name
---------------------
1. P1
2. P2
3. P3
Loadings tables
Pid. Qty. outdate
1. 20. 2020-04-01
2. 10. 2020-04-01
3. 10. 20202-05-01
2. 30. 20202-05-02
Stockin tables
Pid. Qty. Indate
1. 20. 2020-04-01
2. 10. 2020-05-01
3. 10. 20202-05-01
2. 30. 20202-05-02
Example select id=2 Am Expect to have this
Items Date. Openbalance. LoadOut. Stock. Closed balance
P2 2020-05-01. 0. 30. 0. -30
Note:
Open balnc is the closed balance of previous day of current date Closed balance = open balance + stockin-loadout
How can i archive this even if you will use subquery of sql
Level 4
@bobbybouwmann am a new in laravel, i tried to ready but the issue is how can i join that two date from loading table and stoks table,
And also i have sql query which return all products, open balance , closed balnce and stockin and loadout,
So i did no managed to change the way i expect it on above
Level 4
@bobbybouwmann this is the subquery which return monthly wise of each products
public function monthwisereport(){
{
$currentdate=DB::select(DB::raw(" SELECT products.id,
(select categories.name from categories where categories.id=products.category_id) as category_name,
products.name, products.open_stock as open_balance,
(select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id and MONTH(loadings.Outdate) <= MONTH(CURRENT_DATE())) as total_loadings_till_monthly,
(select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and MONTH(stocks.Indate) <= MONTH(CURRENT_DATE()) ) as total_stocks_till_monthly,
(select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id and MONTH(loadings.Outdate) = MONTH(CURRENT_DATE())) as total_loadings_specific_date,
(select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and MONTH(stocks.Indate) = MONTH(CURRENT_DATE())) as total_stocks_specific_date,
((products.open_stock)+(select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and MONTH(stocks.Indate) <= MONTH(CURRENT_DATE())-1)- (select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id and MONTH(loadings.Outdate) <= MONTH(CURRENT_DATE())-1)) as open_balance,
((products.open_stock)+(select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and MONTH(stocks.Indate) <= MONTH(CURRENT_DATE()))- (select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id and MONTH(loadings.Outdate) <= MONTH(CURRENT_DATE()))) as closing_balance
from products GROUP by products.id"));
return view('report.monthwise',compact('currentdate'));
Level 88
@emfinanga Does your query work as expected?
Sometimes a raw query is better than trying to make this work in Eloquent.
Level 4
@bobbybouwmann No, No
this query works like this for current month
items openbalance StockIn LoadOut closedBalance
---------------------------------------------------------------------------------------------------------------------
p1 10 20 10 10
p2 0 20 10 10
p3 10 0 10 0
now i want to select example P1 and get what qty are in and out and closed balance order by date
Level 88
I'm a bit lost. Does the query work or not? It looks like you already have the correct data...
Level 4
@bobbybouwmann , Not,because this data displayed total of each product of the current month 05
But what i need is to select the specific pid and show how many are in the loading and how many are in the stock but order by date
like this for example i just modify my query like this:
$stockSummary=DB::table('products')
->select(['products.id','products.name','open_stock'])
->addSelect([
'total_stockIn' => DB::table('stocks')
// ->selectRaw('stocks.Indate')
->selectRaw('sum(ifnull(`stocks`.`qty`,0))')
->where('stocks.pid', '=', 'products.id')
->whereMonth('stocks.Indate',date('m')),
// ->whereDate('stocks.created_at', '=', Carbon::today()),
'total_loadings' => DB::table('loadings')
// ->selectRaw('loadings.Outdate')
->selectRaw('sum(ifnull(`loadings`.`qty`,0))')
->where('loadings.pid', '=', 6)
->whereMonth('loadings.Outdate',date('m')),
'load_date' =>DB::table('loadings')
->selectRaw('loadings.Outdate')
->where('loadings.pid', '=', 6)
->whereMonth('loadings.Outdate',date('m')),
// ->whereDate('loadings.created_at', '=', Carbon::today()),
'Stock_date' =>DB::table('stocks')
->selectRaw('stocks.Indate')
->where('stocks.pid', '=', 6)
->whereMonth('stocks.Indate',date('m')),
'total_stock_jana' => DB::table('stocks')
-> selectRaw('sum(ifnull(`stocks`.`qty`,0))')
->where('stocks.pid', '=', 6)
// ->whereMonth('stock.Indate',date('m')-1),
->whereDate('stocks.Indate', '=', Carbon::yesterday()),
'total_loadings_jana' => DB::table('loadings')
->selectRaw('sum(ifnull(`loadings`.`qty`,0))')
->where('loadings.pid', '=', 6)
// ->whereMonth('loadings.Outdate',date('m')-1),
->whereDate('loadings.Outdate', '=', Carbon::yesterday()),
])
->OrderBy('load_date','Stock_date')
->get();
dd($stockSummary);
output after run dd()
Illuminate\Support\Collection {#445 ▼
#items: array:1 [▼
0 => {#441 ▼
+"id": 6
+"name": "Angle Iron 1x3mm(25x25x3mm)"
+"open_stock": 119
+"total_stockIn": null
+"total_loadings": "60"
+"load_date": "2020-05-01"
+"Stock_date": "2020-05-02"
+"total_stock_jana": "50"
+"total_loadings_jana": null
}
]
}
what i need is to fetch the data from that two table loading and stock and order by date load_date and stock_date as date in this current month as per shown listed below.
this is breakdown of pid=6 in this month
Date open_stock total_stockIn total_loadings
----------------------------------------------------------------------------------------------------
2020-05-01 119 null 60
2020-05-02 100 50 null
2020-05-03 null null null
if you don't mind i can show you in my laptop emma4082@gmail.com, you can help me as well.
Level 4
In short Am expecting to get all the data which are entered in loading table and stock table by specific id where created at this month and order by date or merge by date At the end i will display like that above
Please or to participate in this conversation.