[BUG] Pagination not working if query contain HAVING statement

[Elyahou]

I have a query that return the cities on a fixed distance from a point:

$query->addSelect(DB::Raw(
            '(3959 * acos (
              cos ( radians('.$lat.') )
              * cos( radians( lat ) )
              * cos( radians( lon ) - radians('.$lon.') )
              + sin ( radians('.$lat.') )
              * sin( radians( lat ) )
            )) AS distance'))
      ->having('distance', '<', 300)->paginate(10);

This cause this mysql error:

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

Because the agregate query generated by the pagination class contain the having but not the select (distance)

select count(*) as aggregate from `cities` where `id` > 0 having `distance` < 300

[patrickheeney]

This might be related to my issue #2560 and pull request #2564. Basically when using pagination it firsts runs the aggregate query and fails to add the select's back to the query which errors out.

[anlutro]

See http://stackoverflow.com/a/20945960/2490608 for a workaround if you're interested.

[taylorotwell]

Closing this as duplicate.

[deep88]

Hello sir i am getting this problem in laravel 5.3.. i am stuck on this from long..please solve.

[deep88]

my query is as follwing

$properties = \DB::table('properties')->select(\DB::raw("*,( 6371 * acos( cos( radians($this->user_lat) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($this->user_lng) ) + sin( radians($this->user_lat) ) * sin( radians( latitude ) ) ) ) AS distance"))->having('distance', '<=', $radius)
->paginate(config('local.PROPERTIES_PER_PAGE'));

[deep88]

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from properties having distance <= 500)

[carlosalaniz]

use ->simplePaginate();

public function searchByRange(Request $request)
     {
         //Validate
         $this->validate($request,
             [
                 "long" => ["required", "numeric"],
                 "lat" => ["required", "numeric"],
                 "distance" => ["numeric", "max:100"],
                 "kilometers" => ["boolean"]
             ]
         );
         $results = Item::distance(
                $request->get("distance"),
                $request->get("lat"),
                $request->get("long")
         );
         return $results->simplePaginate();
     }

It's faling due to the having clause in your query,
On method public function getCountForPagination($columns = ['*']) in Query/Builder.php it tries to do a Count(*). evaluation order on the query is causing it to break.

[carlosalaniz]

Now here an other solution...
In your model...

    /**
     * @param \Illuminate\Database\Eloquent\Builder $subQuery
     * @param double $distance
     * @param double $latitude
     * @param double $longitude
     * @return Builder
     */
    public function scopeDistance($subQuery, $distance, $latitude, $longitude)
    {
        //Generating Query
        $item_distance_query = '* , (3959 * ' .
            'acos( cos( radians(?) ) ' . //lat
            '* cos( radians( latitude ) ) ' .
            '* cos( radians( longitude ) - radians(?) ) ' . //long
            '+ sin( radians(?) ) ' . //lat
            '* sin( radians( latitude ) ) ' .
            ') ) as distance'; //distance3

        $subQuery->getQuery()->selectRaw($item_distance_query,
            [$latitude, $longitude, $latitude]
        );
        $rawQuery = self::getSql($subQuery);
        return DB::table(DB::raw("(" . $rawQuery . ") as item"))
            ->where('distance', '<', $distance);
    }

    /**
     * @param Builder $builder
     * @return string
     */
    private static function getSql($builder)
    {
        $sql = $builder->toSql();
        foreach($builder->getBindings() as $binding)
        {
            $value = is_numeric($binding) ? $binding : "'".$binding."'";
            $sql = preg_replace('/\?/', $value, $sql, 1);
        }
        return $sql;
    }

In your controller...

public function searchByRange(Request $request)
    {
        //Validate
        $this->validate($request,
            [
                "long" => ["required", "numeric"],
                "lat" => ["required", "numeric"],
                "distance" => ["numeric", "max:100"],
                "kilometers" => ["boolean"]
            ]
        );
        /** @var Builder $results */
        $results = Item::distance(
            $request->get("distance"),
            $request->get("lat"),
            $request->get("long")
        );

        return $results->paginate();

    }

[niels-numbers]

Why is this question closed? The problem still exists. Using paginator and having at the same query makes it crash.