How to use 'having' with paginate in relationship column in laravel 5

Question

I need to capture vehicles with a dealer ratio of <200

Vehicle::join('dealers', 'vehicles.dealer_id', '=', 'dealers.id') ->select(DB::raw("dealers.id, ( cos( radians(latitude) ) * cos( radians( longitude ) ) ) AS distance")) ->havingRaw('distance < 200'); 

I am trying to use hasRaw at a distance of "alias" from the relationship dealer (owned). But failed with the error:

Column not found: 1054 Unknown column 'distance' in 'having clause'

UPDATE

The problem occurs when I add the paginate function to the above query.

 $vehicle = Vehicle::join('dealers', 'vehicles.dealer_id', '=', 'dealers.id') ->select(DB::raw("dealers.id, ( cos( radians(latitude) ) * cos( radians( longitude ) ) ) AS distance")) ->havingRaw('distance < 200'); $result = $vehicle->paginate(15); 

Answer 1

Answered Updated by updated question

The problem is the query builder, since all selected items are discarded when making a cumulative call (for example, count (*)). The make-do solution at the moment is to manually create the paginator as:

 $query = Vehicle::join('dealers', 'vehicles.dealer_id', '=', 'dealers.id') ->select(DB::raw("dealers.id, ( cos( radians(latitude) ) * cos( radians( longitude ) ) ) AS distance")) ->having('distance', '<', '200'); $perPage = 10; $curPage = \Illuminate\Pagination\Paginator::resolveCurrentPage(); $itemQuery = clone $query; $items = $itemQuery->forPage($curPage, $perPage)->get(); $totalResult = $query->addSelect(DB::raw('count(*) as count'))->get(); $totalItems = $totalResult->first()->count; $vehicles = new \Illuminate\Pagination\LengthAwarePaginator($items->all(), $totalItems, $perPage); 

Answer 2

Update

If you use paginate() with your query, laravel will try to execute the following SQL code to calculate the total number of possible matches:

 select count(*) as aggregate from `vehicles` inner join `dealers` on `vehicles`.`dealer_id` = `dealers`.`id` having distance < 200 

As you can see, there is no such column or alias distance in this query.

Option 2 in my original answer will also fix this problem.

Original answer

This seam is a problem with strict MySQL mode. If you are using laravel 5.3, strict mode is enabled by default. You have two options:

Option 1: disable strict mode for MySQL in config/database.php

 ... 'mysql' => [ ... 'strict' => false, ... ], ... 

Option 2: use the WHERE clause

 Vehicle::join('dealers', 'vehicles.dealer_id', '=', 'dealers.id') ->select(DB::raw("dealers.id, ( cos( radians(latitude) ) * cos( radians( longitude ) ) ) AS distance")) ->whereRaw('cos( radians(latitude) ) * cos( radians( longitude ) ) < 200'); 

Documentation:

The MySQL extension for standard SQL allows HAVING references for an expression with an alias in the select list. Enabling ONLY_FULL_GROUP_BY disables this extension, which requires that must be written using unexplored expressions.

SQL Server Modes - ONLY_FULL_GROUP_BY

Answer 3

Here I did something similar to what you are trying to do:

 public function scopeNearest($query, Geo $geo, $miles = 25) { return $query ->select(DB::raw("*, ( 3959 * acos( cos( radians({$geo->lat}) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians({$geo->lng}) ) + sin( radians({$geo->lat}) ) * sin( radians( lat ) ) ) ) AS distance")) ->groupBy('id') ->having('distance', '<', $miles) ->orderBy('distance'); } 

In this example, I had a separate model processing latitude and longitude coordinates, as well as address information called Geo . You may not need this level of separation, so you can probably reorganize something like this:

 public function scopeNearest($query, $lat, $lng, $miles = 25) { return $query ->select(DB::raw("*, ( 3959 * acos( cos( radians({$lat}) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians({$lng}) ) + sin( radians({$lat}) ) * sin( radians( latitude ) ) ) ) AS distance")) ->groupBy('id') ->having('distance', '<', $miles) ->orderBy('distance'); } 

Remember that you need two points to calculate the distance. The model will be able to check the distance between two points using its latitude and longitude columns against the latitude and longitude provided in the query area as an argument.

You can put this in your car model and call it like this:

 Vehicle::nearest($latitude, $longitude, 200); 

This is untested for your use case, so I can’t guarantee that it will work out of the box, but I hope it should point you in the right direction.

Answer 4

having and havingRaw does not have access to the generated fields in the request; see this example from laravel docs

Your request will look as follows

 Vehicle::join('dealers', 'vehicles.dealer_id', '=', 'dealers.id') ->select(DB::raw("dealers.id, ( cos( radians(latitude) ) * cos( radians( longitude ) ) ) AS distance")) ->havingRaw('(cos(radians(latitude)) * cos(radians(longitude))) < 200'); 

Answer 5

This should work:

 Vehicle::join('dealers', 'vehicles.dealer_id', '=', 'dealers.id') ->select(DB::raw("dealers.id, ( cos( radians(latitude) ) * cos( radians( longitude ) ) ) AS distance")->havingRaw('distance < 200')); 

Answer 6

Not sure how the query builder handles the alias. Try duplicating the formula in the filter.

 ( cos( radians(latitude) ) * cos( radians( longitude ) ) ) < 200