In Laravel can you establish a relationship between models based on fields that are not keys

Question

I have three models: Vehicle, LiveData, and Place.

In my Vehicle model, I currently have a relationship between Vehicle and LiveData that gets the latest LiveData for a vehicle:

public function latestPosition(): HasOne
{
    return $this->hasOne(LiveData::class)->latestOfMany();
}

Now in my LiveData model, I want to establish a relationship between LiveData and Place, however there are no direct keys between the two, the relationship is between a range of latitude and longitude. This is how the relationship would be called in pure sql:

LEFT JOIN `places`
    ON `live_data`.`speed` = 0 AND `places`.`lat` BETWEEN (`live_data`.`lat` - 0.00007) AND (`live_data`.`lat` + 0.00007) AND `places`.`lng` BETWEEN (`live_data`.`lng` - 0.00007) AND (`live_data`.`lng` + 0.00007)

Because I want to be able to use this relationship with eloquent like so:

$vehicles = Vehicle::where('disabled', 0)
    ->with(['latestPosition.place', 'tags'])
    ->get();

Is this something that is even possible?

Answer 1

Yes and no.

Yes, you can establish eloquent relationships with fields that are not keys.

For example, for tables such as these

+-------+   +----------+
| users |   | profiles |
+-------+   +----------+
| id    |   | ...      |
| email |   | email    |
| ...   |   | ...      |
+-------+   +----------+

You could define a hasOne or belongsTo using email as instead of a key. Foreign keys are not a requirement. They just make it easier to keep data integrity and your rdbms knows how to optimize queries for them a bit.

function profile() { return $this->hasOne(Profile::class, 'email', 'email');

No, you cannot define a relationship that does that exact query in the background. Not unless you were to implement a custom Relation class. Laravel does not provide (to my knowledge) any relationship that resolves to a LEFT JOIN clause that compares columns to values or aggregate values.

You could define a query scope that encapsulates all that logic on the LiveData model and then call that.

I use $join->where instead of $join->on because on is reserved for comparing columns whereas where can be used to compare with values.

// LiveData
public function scopeWithPlace(Builder $query)
{
    return $query->leftJoin('places', function (JoinClause $join) {
        $join->where('live_data.speed', 0)
            ->whereRaw('places.lat BETWEEN (live_data.lat - ?) AND (live_data.lat + ?)', [0.00007, 0.00007])
            ->whereRaw('places.lng BETWEEN (live_data.lng - ?) AND (live_data.lng + ?)', [0.00007, 0.00007])
    })
    ->addSelect('places.*');
}

$vehicles = Vehicle::query()
    ->where('disabled', 0)
    ->with([
        'latestPosition' => fn ($liveData) => $liveData->withPlace(),
        'tags'
    ])
    ->get();