Custom relationship query

Hi All,

We're currently building an application for a client who wants automatic parcel pickup assignments based on Dutch postcodes, e.g 1018DR.

I have a model Account, in this model i have the properties id, postcode and country

id: 1. postcode: 1018DR. country: NL
id: 2. postcode: 1014BB. country: NL

I have a model Range, in this model i have the properties pickup_point_id, from and to.

pickup_point_id: 1. from: 1000AA. to:1100ZZ
pickup_point_id: 2. from: 1015AA. to:1018ZZ

Will be saved as ASCII, so 10006565 to 11009090, but the letters are for readability

I have a model PickupPoint, in this model i have the property id

id: 1
id: 2

I have a model Parcel, in this model i have the properties id, account_id, is_default and pickup_point_id. pickup_point_id is nullable. pickup_point_id will be used for overruling the ranges, in case a client calls and tell them to deliver it at pickup point 2 instead of pickup point 1.

id: 1. account_id: 1 pickup_point_id: null
id: 2. account_id: 2 pickup_point_id: 2
id: 3. account_id: 2 pickup_point_id: null

I have the following simple code:

<?php
use App\Models\Parcel;

$parcel = Parcel::find(1);
$pickup_point = $parcel->pickup_point ?? $parcel->account->pickup_point;

$parcel = Parcel::find(2);
$pickup_point = $parcel->pickup_point ?? $parcel->account->pickup_point;

$parcel = Parcel::find(3);
$pickup_point = $parcel->pickup_point ?? $parcel->account->pickup_point;
?>

1. The pickup_point should be 2, based on the range
2. The pickup_point should be 2, because of the assigned pickup_point
3. The pickup_point should be 1, based on the range

For case 2, i don't see any problem, because i have a relation that has been set.

<?php
public function pickup_point(): HasOne
{
    return $this->hasOne(PickupPoint::class);
}
?>

But case one and three are giving me headaches. In account, i also want a HasOne relationship, but i don't have an ID, because of the range. I already have the query to select the pickup_point based on the postcode.

select @postcode := (
    CASE WHEN
        (UPPER(SUBSTRING(accounts.postcode, -2, 1)) BETWEEN 'A' AND 'Z')
        AND
        (UPPER(SUBSTRING(accounts.postcode, -1, 1)) BETWEEN 'A' AND 'Z')
        AND
        accounts.country = 'NL'
    THEN
        CONCAT(
            SUBSTRING(accounts.postcode, 1, 4),
            ASCII(UPPER(SUBSTRING(accounts.postcode, -2, 1))),
            ASCII(UPPER(SUBSTRING(accounts.postcode, -1, 1)))
        )
    ELSE
        accounts.postcode
    END
) AS 
    postcode,
    ranges.pickup_point_id,
    ((@postcode - ranges.from) + (ranges.to - @postcode)) AS sum,
    ranges.from,
    ranges.to
FROM 
    accounts, ranges
WHERE 
    ranges.from <= @postcode
AND 
    ranges.to >= @postcode
AND 
    ranges.country = 'NL' /* This should be accounts.country */
AND 
    accounts.id = 1 /* This should be accounts.id */
ORDER BY 
    sum

Long story short: I want the above query to be injected in the $relation->setQuery() in this piece of code in the Account model:

<?php
public function pickup_point(): HasOne
{
    $relation = $this->hasOne(PickupPoint::class)
        ->withDefault(
            PickupPoint::query()
                ->where('is_default', '=', true)
        );


    // Here should the query be "injected"
	$relation->setQuery();

    return $relation;
}
?>

And no, i cannot use $this->id and $this->country because $this is empty when eager loading.