Order by distance on POINT in Laravel 5.1 via associated tables

I'm attempting to order events by their distance from a user submitted postcode and distance.

I've attached a sample of my database tables and their relationships, as you can see geom is associated with multiple addresses via postcode and addresses can be associated to multiple tables (in this instance the events table).

I'm taking a postcode from the end user as well as a radius in miles to retrieve appropriate events, here is a sample of how I am achieving this in Eloquent.

/**
 * Extend locale method which initially only gets lat/long for given postcode to search
 *
 * @param \Illuminate\Database\Eloquent\Builder $query The query builder
 * @param \App\Http\Requests\SearchRequest $request The search request
 * @return void
 */
protected function locale(Builder $query, SearchRequest $request)
{
    $postcode = $this->formatPostcode($request->postcode);

    $geom = Geom::query()->where('postcode', $postcode)->first();
    if (! $geom || Cache::has('postcodeAPIFailed')) {
        return;
    }

    $lat = $geom->geo_location['lat'];
    $long = $geom->geo_location['long'];

    // Top-left point of bounding box
    $lat1 = $lat - ($request->within / 69);
    $long1 = $long - $request->within / abs(cos(deg2rad($lat)) * 69);

    // Bottom-right point of bounding box
    $lat2 = $lat + ($request->within / 69);
    $long2 = $long + $request->within / abs(cos(deg2rad($lat)) * 69);

    $query->whereHas('address', function (Builder $query) use ($request, $lat, $long, $lat1, $long1, $lat2, $long2) {
        $query->whereHas('geom', function (Builder $query) use ($request, $lat, $long, $lat1, $long1, $lat2, $long2) {
            $query->whereRaw('st_within(geo_location, envelope(linestring(point(?, ?), point(?, ?))))', [$long1, $lat1, $long2, $lat2]);
        });
    });
}

In the controller after we have retrieved the search results, we calculate the distances for each of the results.

if ($request->has('postcode')) {
    $postcodeDistances = $this->getDistances($results, $request);
}

This produces an array with a key of postcode and value of distance, i.e $postcodeDistances['L1 0AA'] = '3';, we send this array to the view.

In the view we then use the following logic to display distance on a record where applicable

@if($postcodeDistances)
    <span>
        
        mile away
    </span>
@endif

I've tried a few methods but I've been unable to update my function locale() to do the ordering by distance. I've considered maybe I can attached the distance to the collection and use a Laravel method to order the collections that way but achieving this from the database layer would be ideal if the latter is even possible.

My first attempt was to addSelect a distance field after whereHas('geom') and order by the new field

$query->addSelect(\DB::raw("ST_DISTANCE_SPHERE(geo_location, POINT({$long}, {$lat})) AS distance"));

I receive the following error:

SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 2 column(s) (SQL: select count(*) as aggregate from `event` where (select count(*) from `address` where `address`.`addressable_id` = `event`.`id` and `address`.`addressable_type` = event and (select count(*), ST_DISTANCE_SPHERE(geo_location, POINT(-2.717472, 53.427078)) AS distance from `geom` where `geom`.`postcode` = `address`.`postcode` and st_within(geo_location, envelope(linestring(point(-3.6903924055016, 52.847367855072), point(-1.7445515944984, 54.006788144928))))) >= 1) >= 1 and (select count(*) from `organisation` where `event`.`organisation_id` = `organisation`.`id` and `status` = 1) >= 1 and `event_template_id` is not null and `date_start` >= 2018-07-31 00:00:00 and `status` in (1, 5))

I also attempted to use orderByRaw in the same place instead, whilst I did not receive an error the results were not ordered accordingly.

$query->orderByRaw('ST_DISTANCE_SPHERE(geo_location, POINT(?, ?)) ASC', [$long, $lat]);

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2M35RRT
via IFTTT