In my Laravel app, I want to have a relation conditionally load data from one of two tables, depending on a specified parameter.

I have two pivot tables with identical columns, called driver_route and driver_route_history. They both relate driver_id and route_id columns. The only difference is driver_route_history has a date column which holds an absolute date in time (e.g. 06/08/2022), while driver_route has a weekday column which refers to the current week.

The records in driver_route can be modified by the user. At the end of each week, they are cloned to driver_route_history, so that changes from week to week are preserved looking back.

I want to be able to pull data for a single day. If the specified day falls within the current week or references a future date, data should be loaded from driver_route. If the date falls prior to the start of the current week, data should be pulled from driver_route_history.

I have a function that can do this, when called ordinarily as a function from somewhere within the app:

    public function driversOnDay($date)
    {
        $carbon_date = Carbon::parse($date);
        if ($carbon_date->isBefore(Carbon::today()->startOfWeek())) {
            return $this->belongsToMany(Driver::class, 'driver_route_history')->wherePivot('date', $carbon_date);
        } else {
            return $this->belongsToMany(Driver::class, 'driver_route')->wherePivot('weekday', strtolower($carbon_date->shortDayName));
        }
    }

I would like to load this relation on every Route record using Laravel's eager loading, like such:

Route::with(['driversOnDay' => function($query) { ... });

How can I include the driversOnDay date parameter into this eager load call?

CodePudding user response：

From what I understand, model Route is associated with Driver model via many-to-many relationship and you need to join the relation via driver_route pivot table if the $date is in current week or in future and via driver_route_history pivot table if $date is older than start of current week.

One way of doing it would be to have two separate relationships defined on the model and then use a scope:

class Route extends Model
{
    public function driversOnDay()
    {
        return $this->belongsToMany(Driver::class, 'driver_route');
    }


    public function driversOnDayHistory()
    {
        return $this->belongsToMany(Driver::class, 'driver_route_history');
    }

    public function scopeWithDrivers($query, $date)
    {
        $carbon_date = Carbon::parse($date);
        return $query
            ->when(
                $carbon_date->isBefore(Carbon::today()->startOfWeek()),
                function($query) use($carbon_date){
                    $query->with([
                        'driversOnDayHistory' => function($query) use($carbon_date){
                            $query->wherePivot('weekday', strtolower($carbon_date->shortDayName));
                        }
                    ]);
                },
                function($query) use($carbon_date){
                    $query->with([
                        'driversOnDay' => function($query) use($carbon_date){
                            $query->wherePivot('date', $carbon_date);
                        }
                    ]);
                }
            );
    }
}

Then you can use the local scope on Route model anywhere

$records = Route::query()->withDrivers($date)->get();

So if the date is in past $records will have driversOnDayHistory eager loaded else it will have driversOnDay eager loaded.

Update

Is there a way to rename the attribute, so it has the same name in both cases? Right now, the loaded collections are named differently depending on which relation was loaded.

You can manipulate the resulting collection. Say you always want to have the eager loaded relations available as driversOnDay, then you can try

$records = Route::query()
    ->withDrivers($date)
    ->get()
    ->map(function($route) {
        if($route->relationLoaded('driversOnDayHistory')) {
            $drivers = $route->driversOnDayHistory;
            $route->unsetRelation('driversOnDayHistory');
            $route->setRelation('driversOnDay', $drivers);
        }

        return $route;
    });