How to use paginate with a having clause when column does not exist in table

Question

Following database query does not work:

DB::table('posts')
->select('posts.*', DB::raw($haversineSQL . ' as distance'))
->having('distance', '<=', $distance)
->paginate(10);

It fails with message: column distance does not exist.

The error occurs when paginate() tries to count the records with

select count(*) as aggregate from {query without the column names}

As the column names are stripped, distance is not known and an exception is raised.

Does somebody have a work around to be able to use pagination is this case ?

Answer 1

Hello @kartik,

You can calculate the distance in the WHERE part:

DB::table('posts')
    ->whereRaw($haversineSQL . '<= ?', [$distance])
    ->paginate(10);

If you need the distance value in your application, you'll have to calculate it twice:

DB::table('posts')
    ->select('posts.*', DB::raw($haversineSQL . ' as distance'))
    ->whereRaw($haversineSQL . '<= ?', [$distance])
    ->paginate(10);

Hope it helps!!