Table Joins in Laravel controller

Question

I am joining to create the correct Table Join in the Controller. The goal is to create a sumamry table.

My Current code is as follows

$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();

I have the following tables

fuel_records

id | aircraftID | fuelamount | price | fueldate | campID | fullstopLandings |

Flights

id | aricraftID | hours | flightTotal

aircraft

id | registration |

I have the following on my Aircraft model

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

What I am trying to create is a summary table to be displayed below

Aircraft | Movement Costs |Fuel Costs | Flight Income | Balance

I can gain the summary for each item apart from Fuel Costs as this is stored in a different table.

I know my movement cost is hard coded here, this will be moved to a variable, just trying to build the table from data first

Ts8060 Ts8060 · Accepted Answer · 2022-01-04T03:26:32

You can use Join function instead of 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();

Table Joins in Laravel controller

1 Answers