framework: [BUG] Pagination not working if query contain HAVING statement

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

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

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

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

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.

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

    }

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.

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

iit’s simple

change paginate(10) with simplePaginate(10)