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

    }

[iwasherefirst2]

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

[devcircus]

According to the comment above, it is a duplicate of another issue.

[anlutro]

Probably more accurately closed as a wontfix because of #5515

[asepmedia]

iit's simple

change paginate(10) with simplePaginate(10)

[royduin]

What I've done (Laravel 5.5) is overwritten runPaginationCountQuery() from the Query Builder with:

/**
 * Run a pagination count query.
 *
 * @param  array  $columns
 * @return array
 */
protected function runPaginationCountQuery($columns = ['*'])
{
    if ($this->havings) {
        $query = $this->cloneWithout(['orders', 'limit', 'offset'])
                      ->cloneWithoutBindings(['order']);

        // We don't need simple columns, only specials
        // like subselects which is why we're using
        // havings after all.
        foreach ($query->columns as $key => $value) {
            if (is_string($value)) {
                unset($query->columns[$key]);
            }
        }

        $countQuery = DB::table(DB::raw('('.$query->toSql().') as x'))->mergeBindings($query);

        // Using a aggregate here won't work when
        // groups are present because the
        // getCountForPagination() is
        // checking for it.
        if (!$this->groups) {
            $countQuery->setAggregate('count', $this->withoutSelectAliases($columns));
        }

        return $countQuery->get()->all();
    }

    return $this->cloneWithout(['columns', 'orders', 'limit', 'offset'])
                ->cloneWithoutBindings(['select', 'order'])
                ->setAggregate('count', $this->withoutSelectAliases($columns))
                ->get()->all();
}

Update: I've created a package for this: https://github.com/justbetter/laravel-pagination-with-havings

[jayantakundu]

Hi
Issue is still there. I have found same issue laravel 5.6.

$data = DB::table('jobs as j')
->selectRaw('j.*, (case
when (seeker_action = 'like' and employer_action = 'like') then 'match'
when (seeker_action is null and employer_action = 'like') then 'employer-liking'
when (seeker_action = 'like' and employer_action = 'delete') then 'rejected'
else seeker_action end) as 'action'')
->join('profiles_jobs as p', 'p.job_id', '=', 'j.id')
->where('p.profile_id', '=', 2)
->whereRaw('(p.seeker_action is not null) or (p.seeker_action is null and p.employer_action = 'like')
and j.deleted_at is null')
->having('action', '=', 'employer-liking');

$data = $data->paginate(10);

Please help me

[asepmedia]

@jayantakundu change paginate(10) to simplePaginate(10) if you work with having.

[jayantakundu]

I am using laravel api resource in my api & using Resource::collection to get paginated api response. But if i change this paginate to simplePaginate, in the api response some attributes missing in pagination links & meta as generated from paginate method.

[ko1eda]

Just ran into this issue in 5.6 @royduin solution seemed to fix it so cheers! Is there any actual planned fix for this?

[BorjaRafols]

Same Here, @royduin seems to fix it. Any plans to put this into the source code?

[solofeed]

@royduin How you override QueryBuilder?

[royduin]

@solofeed I'm currently with vacation, when I'm back next week I'll share it, create a PR or a module for this.

[solofeed]

@royduin sry for this, thank you) have a nice vacation)

[royduin]

I'm back @solofeed and I've created a package for it: https://github.com/justbetter/laravel-pagination-with-havings

[dipbro]

Thank you @royduin for your great work .

[maxpohlmann]

Whenever I had this problem, the following worked fine for me, though I'm not sure whether this is a general solution:
For your given $query, replace
$query->paginate()
with
\DB::query()->fromSub($query, '_')->paginate().

Though this is probably pretty inefficient on large result sets, as MySQL will now load the entire subquery into working memory.

[joselilove]

Thank you so much @royduin .

[shbifcompany]

@royduin thank you