在Laravel中,你可以基于非键字段来建立模型之间的关联吗?

huangapple go评论62阅读模式
英文:

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

问题

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?

英文:

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?

答案1

得分: 2

Yes and no.

,您可以与不是键的字段建立清晰的关系。

例如,对于这样的表格

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

您可以定义一个hasOnebelongsTo,使用email而不是键。外键不是必需的。它们只是使数据完整性保持更容易,而且您的关系数据库管理系统知道如何为它们优化查询。

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

不是,您不能定义一个在后台执行确切查询的关系。除非您实现了自定义的Relation类。据我所知,Laravel不提供任何将列与值或聚合值进行比较的LEFT JOIN子句的关系。

您可以定义一个查询作用域,在LiveData模型上封装所有这些逻辑,然后调用它。

我使用$join->where而不是$join->on,因为on用于比较列,而where可用于与值比较。

// 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();
英文:

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();

</details>



huangapple
  • 本文由 发表于 2023年5月21日 00:46:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/76296321.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定