CODEWITHSUNDEEP
mysqllaraveleager-loading
Clément Baconnier
Clément Baconnier

Reputation: 6058

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

Second: with will eager-load the services

select * from `services` where `services`.`place_id` in (22)

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)

which throw 

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'places.distance' in 'where clause'

What am I trying to do

Let's say the calculated field distance of a place returns the value 55 meters

I want to eager load the information (through the services) only when the distance_of_visibility is under those 55 meters.

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

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

Service Model
1. The Service belongsTo a Place
2. The Service MorphTo a Registrable

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

Upvotes: 1

Views: 119

Answers (1)

Matt McDonald
Matt McDonald

Reputation: 5050

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.

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.

Upvotes: 1

Related Questions