Any idea how to solve pagination issue with having clause?

Level 32

You guys have to chat with your viewers...

PS: Isn't it fixed?

Level 1

Ozan: come chat with us :)

I don't think it's fixed, since I am running into an issue with fresh Laravel install.

vjandrea

Level 1

Do you have a code snippet to show? I have a side project where this code works:

        return \App\Location::select(['locations.*', \DB::raw('round(distance) AS rdist')])
                        ->where('active', true)
                        ->groupBy('rdist')
                        ->having('rdist','<', 50)
                        ->orderBy('rdist')
                        ->paginate(25);

Level 1

On the model:

    public function scopeNearLatLng($query, $lat, $lng, $maxDistance = 10, $units = 'K')
    {
        $earthRadiusInKilometers = 6371;
        $earthRadiusInMiles      = $earthRadiusInKilometers / 1.609344;

        $earthRadius = 0;
        if ($units == 'K') {
            $earthRadius = $earthRadiusInKilometers;
        } elseif ($units == 'M') {
            $earthRadius = $earthRadiusInMiles;
        } else {
            throw new Exception("Unknown unit of measure $units.");
        }

        $rawSQL = '( ? * acos( cos( radians(?) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( lat ) ) ) ) AS distance';

        return $query
            ->select(DB::raw($rawSQL))
            ->addBinding("$earthRadius")
            ->addBinding("$lat")
            ->addBinding("$lng")
            ->addBinding("$lat")
            ->groupBy('id')
            ->having('distance', '<', $maxDistance);
    }

In a controller:

        if ($lat && $lng) {
            $places = $places->NearLatLng($request->lat, $request->lng);
        }

dd($places->paginate());

Results in:

QueryException in Connection.php line 624: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from `places` group by `id` having `distance` < 6371)

Level 1

To answer my own question, and to document for others who might be searching for the solution, here is the result.

This is scope implements a Haversine formula search, with additional optimization for speed, which is documented here.

I wish there was a cleaner way to get raw SQL from the query object, but unfortunately toSql() returns SQL before the placeholders have been substituted, so I relied on several *Raw calls. It's not too bad, but I wish it was cleaner.

The code assumes you have columns lat and lng in your table.

    const DISTANCE_UNIT_KILOMETERS = 111.045;
    const DISTANCE_UNIT_MILES      = 69.0;

    /**
     * @param $query
     * @param $lat
     * @param $lng
     * @param $radius numeric
     * @param $units string|['K', 'M']
     */
    public function scopeNearLatLng($query, $lat, $lng, $radius = 10, $units = 'K')
    {
        $distanceUnit = $this->distanceUnit($units);

        if (!(is_numeric($lat) && $lat >= -90 && $lat <= 90)) {
            throw new Exception("Latitude must be between -90 and 90 degrees.");
        }

        if (!(is_numeric($lng) && $lng >= -180 && $lng <= 180)) {
            throw new Exception("Longitude must be between -180 and 180 degrees.");
        }

        $haversine = sprintf('*, (%f * DEGREES(ACOS(COS(RADIANS(%f)) * COS(RADIANS(lat)) * COS(RADIANS(%f - lng)) + SIN(RADIANS(%f)) * SIN(RADIANS(lat))))) AS distance',
            $distanceUnit,
            $lat,
            $lng,
            $lat
        );

        $subselect = clone $query;
        $subselect
            ->selectRaw(DB::raw($haversine));

        // Optimize the query, see details here:
        // http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

        $latDistance      = $radius / $distanceUnit;
        $latNorthBoundary = $lat - $latDistance;
        $latSouthBoundary = $lat + $latDistance;
        $subselect->whereRaw(sprintf("lat BETWEEN %f AND %f", $latNorthBoundary, $latSouthBoundary));

        $lngDistance     = $radius / ($distanceUnit * cos(deg2rad($lat)));
        $lngEastBoundary = $lng - $lngDistance;
        $lngWestBoundary = $lng + $lngDistance;
        $subselect->whereRaw(sprintf("lng BETWEEN %f AND %f", $lngEastBoundary, $lngWestBoundary));

        $query
            ->from(DB::raw('(' . $subselect->toSql() . ') as d'))
            ->where('distance', '<=', $radius);
    }

    /**
     * @param $units
     */
    private function distanceUnit($units = 'K')
    {
        if ($units == 'K') {
            return static::DISTANCE_UNIT_KILOMETERS;
        } elseif ($units == 'M') {
            return static::DISTANCE_UNIT_MILES;
        } else {
            throw new Exception("Unknown distance unit measure '$units'.");
        }
    }

This can be used as such:

            $places->NearLatLng($lat, $lng, $radius, $units);
            $places->orderBy('distance');

The SQL generated, will look approximately like this:

select
  *
from
  (
    select
      *,
      (
        '111.045' * DEGREES(
          ACOS(
            COS(
              RADIANS('45.5088')
            ) * COS(
              RADIANS(lat)
            ) * COS(
              RADIANS('-73.5878' - lng)
            ) + SIN(
              RADIANS('45.5088')
            ) * SIN(
              RADIANS(lat)
            )
          )
        )
      ) AS distance
    from
      `places`
    where lat BETWEEN 45.418746  AND 45.598854
      and lng BETWEEN -73.716301 AND -73.459299
  ) as d
where `distance` <= 10
order by `distance` asc

3 likes

yoram

Level 1

Hi Moltar, I'm interested in your solution but can't get it to work... Where exactly do you place the scopeNearLatLng() method and how to use in on a model (collection)? For example I have a model Store in table 'stores'. How do I select all Store models within a given radius? And paginate them? Can you give a code example of this? Thank you very much in advance! Best, Yoram

Level 1

@Yoram You put scopeNearLatLng and everything else you see into your Eloqument model class. The usage I have outlined:

$places->NearLatLng($lat, $lng, $radius, $units);
$places->orderBy('distance');

Maybe put some code on Gist so I can see it.

yoram

Level 1

Hi moltar, for example I'm used to query the database like this:

// Get last 5 stores from DB
$stores = Store::orderBy('id','desc')->take(5)->get();
dd($stores);

How can I do something similar using your method scopeNearLatLng? I already have lat an lng fields in my 'stores' table so i want to do something like:

$lat = 51.2194;
$lng = 4.4025;

// Get all stores from DB within radius of 100 kilometers of given lat and lng, finally paginate in groups of 10 items (pseudo code)
$stores = Store::scopeNearLatLng($lat, $lng, 100, 'K')->paginate(10);
dd($stores);

The above won't work but hopefully it makes clear what I am trying to accomplish here... Thanks again!

Level 1

@Yoram

You need to put scopeNearLatLng function and other related code into your Store class.

Then you call it like this, note that you need to omit scope from the method name when you actually make the call. The methods start with scope in the class, as a naming convention, but then when you do the call, you remove that part. Please see docs for further explanation of scopes.

$stores = Store::NearLatLng($lat, $lng, 100, 'K')->paginate(10);

yoram

Level 1

@moltar

Thank you very much, it works as expected now! Didn't know about scopes yet! :)

Presto

Level 6

This is really awesome @moltar, quick question I need to do a join with this query, how would we go about adding a left join on to this query to pull images from a photos table?

Here is my scope

public function scopeNearLatLng($query, $lat, $lng, $radius = 10, $units = 'K')
{
    $distanceUnit = $this->distanceUnit($units);

    if (!(is_numeric($lat) && $lat >= -90 && $lat <= 90)) {
        throw new Exception("Latitude must be between -90 and 90 degrees.");
    }

    if (!(is_numeric($lng) && $lng >= -180 && $lng <= 180)) {
        throw new Exception("Longitude must be between -180 and 180 degrees.");
    }

    $haversine = sprintf('properties.*, (%f * DEGREES(ACOS(COS(RADIANS(%f)) * COS(RADIANS(lat)) * COS(RADIANS(%f - lng)) + SIN(RADIANS(%f)) * SIN(RADIANS(lat))))) AS distance',
        $distanceUnit,
        $lat,
        $lng,
        $lat
    );

    $subselect = clone $query;
    $subselect->selectRaw(DB::raw($haversine));

    $latDistance      = $radius / $distanceUnit;
    $latNorthBoundary = $lat - $latDistance;
    $latSouthBoundary = $lat + $latDistance;
    $subselect->whereRaw(sprintf("lat BETWEEN %f AND %f", $latNorthBoundary, $latSouthBoundary));

    $lngDistance     = $radius / ($distanceUnit * cos(deg2rad($lat)));
    $lngEastBoundary = $lng - $lngDistance;
    $lngWestBoundary = $lng + $lngDistance;
    $subselect->whereRaw(sprintf("lng BETWEEN %f AND %f", $lngEastBoundary, $lngWestBoundary));

    $query
        ->from(DB::raw('(' . $subselect->toSql() . ') AS properties'))
        ->where('distance', '<=', $radius);
}

And here is my code within my controller

$properties = Property::NearLatLng(Session::get('lat'), Session::get('lng'), Session::get('proximity'), Session::get('unit'))
    ->orderBy('distance')
    ->where('term', '=', Session::get('term'))
    ->leftJoin('photos as photos', 'properties.id', '=', 'photos.property_id')
    ->paginate(10);

This almost works, it does do the join but it brakes the main proximity search and returns all locations, blah!.

Presto

Level 6

After many hours of playing around with code this is what I got working, hopefully it may save someone else some time. The issue I was having was trying to add more options to sort and filter by and every time I added to the query it would fail.

This is my working code:

public function scopeProximity($query, $lat, $lng, $radius, $units)
{
    $radius = $radius ? $radius : 500;

    if($units == 'KM'){
        $distanceUnit = 111.045;
    }else{
        $distanceUnit = 69.0;
    }

    $haversine = sprintf('properties.*, (%f * DEGREES(ACOS(COS(RADIANS(%f)) * COS(RADIANS(lat)) * COS(RADIANS(%f - lng)) + SIN(RADIANS(%f)) * SIN(RADIANS(lat))))) AS distance', $distanceUnit, $lat, $lng, $lat);

    $subselect = clone $query;
    $subselect->selectRaw(DB::raw($haversine)); // Optimize haversine query: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

    $latDistance      = $radius / $distanceUnit;
    $latNorthBoundary = $lat - $latDistance;
    $latSouthBoundary = $lat + $latDistance;
    $subselect->whereRaw(sprintf("lat BETWEEN %f AND %f", $latNorthBoundary, $latSouthBoundary));

    $lngDistance     = $radius / ($distanceUnit * cos(deg2rad($lat)));
    $lngEastBoundary = $lng - $lngDistance;
    $lngWestBoundary = $lng + $lngDistance;
    $subselect->whereRaw(sprintf("lng BETWEEN %f AND %f", $lngEastBoundary, $lngWestBoundary));

    $query
        ->from(DB::raw('(' . $subselect->toSql() . ') as properties'))
        ->where('distance', '<=', $radius);
}

I also added these two new scopes to get the property's thumbnail and to check if the user had hearted the property.

public function scopeThumbnail($query)
{
    $query->addSelect(DB::raw('(SELECT photos.file AS photo FROM photos WHERE photos.property_id = properties.id LIMIT 1) as photo'));
}
public function scopeFavored($query)
{
    $query->addSelect(DB::raw('(SELECT (CASE WHEN favorite_properties.id IS NOT NULL THEN 1 ELSE 0 END) AS isfavorited FROM favorite_properties WHERE favorite_properties.property_id = properties.id) as isfavorited'));
}

This is my query within my controller

$properties = Property::proximity(Session::get('lat'), Session::get('lng'), Session::get('proximity', 50), Session::get('unit', 'MILES'))
    ->addSelect('properties.*')
    ->orderBy('distance','asc')
    ->thumbnail()
    ->favored()
    ->paginate(10);

1 like

pjsinco

Level 5

Super helpful @moltar! Thank you!

vipindasks