Example of using dynamic relationships in Laravel based on parent row condition with ordering and default on null match

Demo.php

<?php
        $assignments = UserVesselAssignment::withDetails()->get();

UserVesselAssignment.php

<?php
class UserVesselAssignment extends Model
{
    use Compoships;

    protected $fillable = [
            'starts_at',
            'ends_at',
    ];
    
    protected $dates = [
        'starts_at',
        'ends_at',
    ];
    
    /**
     * Select the correct details for the assignment, that is, the latest details within the assignment period or ... TODO
     * @param Builder $query
     */
    public function scopeWithDetails(Builder $query)
    {

        $select = UserVesselDetails::select('id')->whereColumn('user_vessel_assignments.ends_at', '>=', 'user_vessel_details.created_at')
                                   ->whereColumn('user_vessel_assignments.user_id', 'user_vessel_details.user_id')
                                   ->whereColumn('user_vessel_assignments.vessel_id', 'user_vessel_details.vessel_id')
                                   ->orderBy('user_vessel_details.created_at', 'DESC')->limit(1)->toSql();

        $null = UserVesselDetails::select('id')->whereColumn('user_vessel_assignments.starts_at', '<=', 'user_vessel_details.created_at')
                                 ->whereColumn('user_vessel_assignments.user_id', 'user_vessel_details.user_id')
                                 ->whereColumn('user_vessel_assignments.vessel_id', 'user_vessel_details.vessel_id')
                                 ->orderBy('user_vessel_details.created_at', 'ASC')->limit(1)->toSql();

        $query->select()
              ->addSelect(DB::raw("COALESCE(({$select}), ({$null})) AS user_vessel_details_id"))
              ->with('details');
    }

    /**
     * @return BelongsTo
     *
     * Only use this in conjunction with @scopeWithDetails as there isn't actually a user_vessel_detail_id on the Assignment to make the BelongsTo work
     */
    public function details(): BelongsTo
    {
        return $this->belongsTo(UserVesselDetails::class, ['vessel_id', 'user_id'], ['vessel_id', 'user_id']);
    }
}

UserVesselDetails.php

<?php
class UserVesselDetails extends Model
{
      protected $fillable = [
        'user_id',
        'vessel_id',
        ];
}

This example uses compound keys on the belongsTo, provided via the Compoships package. but will work just as well without. To adapt to a single key, simple replace the ['vessel_id', 'user_id'] with 'your_key' and replace delete one of the whereColumn on the 2 sub selects (and update the other to use 'your_key'

If you don't need a default value, you can remove the second sub select and update ->addSelect(DB::raw(... to not use COALESCE

You could also belongsTo relation to have withDefault(), but this may result in N+1 queries for any row that doesn't find a match on the initial scopeWithDetails call.