基于第一个查询的热切加载 [英] Eager-Loading based on the first query
问题描述
查询问题所在
Query where the problem lies
\DB::enableQueryLog();
$place = Place::near($user)->with(['services','services.registrable' => function (MorphTo $query) {
$query->where('distance_of_visibility', '<', \DB::raw('places.distance'));
}])->find(22);
\DB::disableQueryLog();
dd(\DB::getQueryLog());
生成的查询
Generated queries
首先:查询将计算给定半径内的所有位置，并将计算出的字段距离添加到选择区域
First: The queries will calculate all the places in a given radius and add the calculated field distance to the select
select *, st_distance_sphere(`location`, ST_GeomFromText(0.000000 00.000000)) as distance from `places` where st_distance_sphere(`location`, ST_GeomFromText(0.000000 00.000000)) <= 5000 and `places`.`id` = 22 limit 1
第二个:with将急切加载services
select * from `services` where `services`.`place_id` in (22)
最后:with会尝试加载registrable，但是急切加载会给一个明显的错误，因为在此范围内不存在place.distance
Finally: with will try to load registrable but the eager-load gives an obvious error since places.distance doesn't exist in this scope
select * from information where distance_of_visibility < places.distance and information.id in (5)
扔
PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'places.distance' in 'where clause'
我想做什么
What am I trying to do
比方说，地点的计算字段距离返回值 55 米
Let's say the calculated field distance of a place returns the value 55 meters
仅当 distance_of_visibility 在这些 55 米以下时，我才希望通过服务加载信息.
I want to eager load the information (through the services) only when the distance_of_visibility is under those 55 meters.
我的问题是:如何使它以最有效的方式工作.计算距离要花一些查询时间.我会尽量避免加入表+重新计算.我想有一种方法可以访问$query的以前的查询结果，但我不知道如何.
So my question is: how can I make it work in the most efficient way. Calculate the distance take some querying time. I would try to avoid join table + recalculation. I guess there's a way to access to the previous queries results from $query but I couldn't figure how.
需要更多信息的情况下
Context in case you need more information
位置模型
1.它使用 https://github.com/grimzy/laravel-mysql-spatial
2.是hasMany 服务
3.它包含一个范围，该范围将计算出的字段距离添加到选择项:
Place Model
1. It uses https://github.com/grimzy/laravel-mysql-spatial
2. Is hasMany Service
3. It contain a scope that add the calculated field distance to the select:
/**
* Get the places near the user in a certain radius
* It also add the distance in the select statement
*
* @param SpatialBuilder $query
* @param Geolocalisable $geolocalisable
* @return SpatialBuilder
*/
public function scopeNear(SpatialBuilder $query, Geolocalisable $geolocalisable): SpatialBuilder
{
return $query
->distanceSphereValue('location', $geolocalisable->position())
->distanceSphere('location', $geolocalisable->position(), $geolocalisable->radiusInMeter());
}
服务模型
1. 服务 belongsTo一个地点
2. 服务 MorphTo一个可注册
Service Model
1. The Service belongsTo a Place
2. The Service MorphTo a Registrable
可注册模型
1.没有可注册模型.这是与多个模型的多态关系.在此示例中，您看到的是信息"
2.那些可注册型号morphOne 服务
Registrable Model
1. There's no Registrable Model. It's a polymorphic relationship with multiple Models. In this example, you're seeing "Information"
2. Those Registrable Models morphOne Service
推荐答案
不幸的是，您要求做的事情与Eloquent无关.
Unfortunately what you're asking to do isn't possible with Eloquent.
急切负载查询与初始查询完全分开，因此检索到的任何值都不再可用.
The eager-load query is entirely separate from the initial query so any values retrieved are no longer available.
您的选择要么是在您的约束中进行联接(您指出可能不是有效的)，要么是加载所有相关模型，然后使用PHP来对它们进行排序/过滤.
Your options are either to do a join in your constraint (which you indicated might not be performant), or load all the related models and then sort/filter those using PHP instead.
这篇关于基于第一个查询的热切加载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
