How to use 'having' with paginate on relat

问题:

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

回答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

回答2:

Answer Updated corresponding to updated question

The problem is with the query builder as all selects are discarded when doing an aggregate call (like count(*)). The make-do solution, for now, is to construct the paginator manually 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);

回答3:

Here's something I did that appears to be similar to what you're 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 handling latitude and longitude coordinates, as well as address information called Geo. You might not need that level of separation, so you can probably refactor to 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, in order to calculate distance, you need two points. The model will be able to check the distance between two points by using its own latitude and longitude columns against the latitude and longitude provided to the scope query as an argument.

You can put this in your Vehicle model and call like so:

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 hopefully it should point you in the right direction.

回答4:

having and havingRaw doesn't have access to generated fields in the query see this example from laravel docs

Your query will have to lookk like this

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');

回答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'));

回答6:

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

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