我正在加入以在控制器中創建正確的表聯接。目標是創建一個 sumamry 表。
我當前的代碼如下
$aircraftCosts = flight::with([
'FuelUsage' => function ($query) {
$query->where('campID', $campid);
},
])
->select('aircraftID')
->selectRaw('(Sum(fullstopLandings)*30) as fullstopLandings') //Get Total of Fullstop Landings
->selectRaw('Sum(flightTotal) as flightTotal') //Get Total of Flight Income
->selectRaw('Sum(flightTotal) - (Sum(fullstopLandings)*30) as balance')
->selectRaw('Sum(price as fuelCost')
->where('campID',$campid)
->groupBy('aircraftID')
->get();
我有以下表格
燃料記錄
id | aircraftID | fuelamount | price | fueldate | campID | fullstopLandings |
航班
id | aricraftID | hours | flightTotal
飛機
id | registration |
我的飛機模型上有以下內容
public function Flights()
{
return $this->hasMany('App\Flight', 'aircraftID', 'id');
}
public function HoursFlown()
{
return $this->hasMany('App\Flight', 'aircraftID', 'id')->sum('hours');
}
public function FuelUsage()
{
return $this->hasMany('App\FuelRecord', 'aircraftID', 'id');
}
我試圖創建的是一個匯總表，顯示在下面
Aircraft | Movement Costs |Fuel Costs | Flight Income | Balance
除了燃料成本之外，我可以獲得每個專案的摘要，因為它存盤在不同的表中。
我知道我的移動成本在這里是硬編碼的，這將被移動到一個變數，只是嘗試首先從資料構建表
uj5u.com熱心網友回復：
您可以使用 Join 功能而不是 Eloquent Relationship。
flight::leftJoin("fuel_records AS fr", "fr.aircraftID" "flights.aircraftID")
->select('aircraftID')
->selectRaw('(Sum(fr.fullstopLandings)*30) as fullstopLandings') //Get Total of Fullstop Landings
->selectRaw('Sum(flights.flightTotal) as flightTotal') //Get Total of Flight Income
->selectRaw('Sum(flights.flightTotal) - (Sum(fr.fullstopLandings)*30) as balance')
->selectRaw('Sum(fr.price as fuelCost')
->groupBy("flights.aircraftID")
->get();
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/403668.html
標籤：