Question 172930 in Laravel Question posted in
The official Laravel Documentation can be found here.

Can models in Laravel have relationships based on non-key fields?

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?

Tags:

2

Answers


  1. 0

    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();
    Login or Signup to reply.
  2. 0

    Yes, it is possible to establish a relationship between the LiveData model and the Places model based on a range of latitude and longitude values. In Laravel’s Eloquent ORM, you can define a custom relationship using a closure in the model’s with method.

    In your LiveData model, you can define the relationship with Places using the whereHas method along with a closure to specify the latitude and longitude range conditions. Here’s an example of how you can define the relationship:

    public function place()
{
    return $this->hasOne(Places::class)
        ->where('speed', 0)
        ->whereBetween('lat', [$this->lat - 0.00007, $this->lat + 0.00007])
        ->whereBetween('lng', [$this->lng - 0.00007, $this->lng + 0.00007]);
}

    In this example, the place relationship is defined as a hasOne relationship with Places model. It includes the conditions to check for speed being 0 and latitude and longitude falling within the specified range based on the lat and lng values of the LiveData model.

    Now, in your query to retrieve vehicles, you can eager load the latestPosition.place relationship along with other relationships:

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

    With this setup, each LiveData record associated with a vehicle will be eager loaded with its corresponding place based on the latitude and longitude range conditions specified in the relationship.

    Make sure to adjust the column names and ranges according to your actual database structure and requirements.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search