英文:
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 |
| ... | | ... |
+-------+ +----------+
您可以定义一个hasOne
或belongsTo
,使用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>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论