Laravel relationship with condition

I have a relationship instructor on a TentativeCourse model. When I try to use the with eager loading the instructor model is not loaded. When I try to use it without eager loading it works.

What would be the best way to achieve the filtering for term_code in the relationship? Ultimately I want it work with filamentphp table relationship column as mentioned in the docs here. https://filamentphp.com/docs/3.x/tables/columns/relationships#displaying-data-from-relationships

use App\Models\TentativeCourse;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class TentativeCourse extends Model
{
  public function instructor(): BelongsTo
  {
    return $this->belongsTo(Instructor::class, 'crn', 'crn')
      ->where('term_cd', $this->term_code);
  }
}

$works = TentativeCourse::first();

dump($works->instructor); // Loads instructor

$fails = TentativeCourse::with(["instructor"])->first(); // Doesn't load instructor

dump($fails);

LaryAI

9 months ago

Level 58

The issue here is that when you use $this->term_code inside the relationship definition, it works for lazy loading (i.e., $tentativeCourse->instructor) because $this refers to the current model instance. However, during eager loading (i.e., with('instructor')), Laravel builds a single query for all parent models, so $this->term_code is not available in the relationship scope.

Solution: Use whereColumn or a closure for dynamic conditions

To filter the relationship based on a column from the parent model during eager loading, you should use a closure in your relationship definition. Here’s how you can do it:

use App\Models\TentativeCourse;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class TentativeCourse extends Model
{
    public function instructor(): BelongsTo
    {
        return $this->belongsTo(Instructor::class, 'crn', 'crn')
            ->whereColumn('term_cd', 'tentative_courses.term_code');
    }
}

Explanation:

whereColumn('term_cd', 'tentative_courses.term_code') tells Eloquent to match the term_cd column on the instructors table with the term_code column on the tentative_courses table for each row, which works for both lazy and eager loading.

Usage:

// Lazy loading (works)
$course = TentativeCourse::first();
dump($course->instructor);

// Eager loading (now works)
$course = TentativeCourse::with('instructor')->first();
dump($course->instructor);

For FilamentPHP: This approach is compatible with FilamentPHP's relationship columns, as it uses standard Eloquent relationships.

Summary:
Use whereColumn in your relationship to compare columns between the related tables. Avoid using $this->term_code directly in the relationship for eager loading scenarios.

Please or to participate in this conversation.