mysql - How to use 'having' with paginate on relationship's column in laravel 5

Question

I need to grab the vehicles whose relation 'dealer' is having distance < 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 havingRaw on the alias 'distance' from the relation (belongsTo) dealer. But failed with an error:

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

UPDATE

The issue actually occurs when I add paginate function to the above query like this.

$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

Update

If you use paginate() with your query laravel will try to execute the following SQL code to count 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 fix that issue too.

Original answer

That seams to be a MySQL-strict-mode issue. If you use laravel 5.3 strict mode is enabled per default. You have two options:

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

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

Option 2: Use a WHERE condtition

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:

A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. Enabling ONLY_FULL_GROUP_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions.

Server SQL Modes - ONLY_FULL_GROUP_BY