Reference parent column in nested relationship

Question

Problem

I've spent the last few hours looking for a solution for this and can't seem to find anything that works. I'm trying to load all Routes that have at least one assigned Aircraft that is currently at the departure airport of the route, like this:

Route::has('availableAircraft');

The availableAircraft relationship on Route currently looks like this, with the issue stemming from not being able to find a way to inject the Route into the final where clause (see ROUTE_ID_HERE).

// Route.php

/**
 * This returns all assigned aircraft that are not allocated to jobs and are at the departure airport of the route
 */

public function availableAircraft()
{

    return $this->belongsToMany(
      Aircraft::class, 'aircraft_route_assignments', 'route_id', 'aircraft_id')
      ->whereNull('current_job_id')
      ->where('current_airport_id', 'ROUTE_ID_HERE');
}

Raw SQL

I can perform this query using raw SQL, but I can't find a way to replicate this in Eloquent:

select
    count(*) as aggregate
from
    `routes`
where (
          select
              count(*)
          from
              `aircraft`
                  inner join `aircraft_route_assignments` on `aircraft`.`id` = `aircraft_route_assignments`.`aircraft_id`
          where
                  `routes`.`id` = `aircraft_route_assignments`.`route_id`
            and `current_job_id` is null
            and `current_airport_id` = `routes`.`departure_airport_id`
      ) > 0
  and `routes`.`deleted_at` is null

The crucial part here is the final and 'current_airport_id' = 'routes'.'departure_airport_id', which I can't seem to find a way to replicate in the query builder.

What I've Tried

I've tried to manually specify the field, like in the SQL query as so, but the actual SQL generated by this uses 'routes.departure_airport_id' as a literal string and so returns no results:

// Route.php

/**
 * This returns all assigned aircraft that are not allocated to jobs and are at the departure airport of the route
 */

public function availableAircraft()
{

    return $this->belongsToMany(
      Aircraft::class, 'aircraft_route_assignments', 'route_id', 'aircraft_id')
      ->whereNull('current_job_id')
      ->where('current_airport_id', '`routes`.`departure_airport_id`');
}

Am I vastly over-thinking this?

Answer 1

Try this as your eloquent query:

Route::whereHas('availableAircraft', function (Builder $query) {
      $query->whereNull('current_job_id')
            ->whereRaw('current_airport_id = `routes`.`departure_airport_id`');
})->get();

And change your model to this:

public function availableAircraft()
{

    return $this->belongsToMany(
       Aircraft::class, 'aircraft_route_assignments', 'route_id', 'aircraft_id');
}