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

Question

I have a tricky case ...

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 ?

Thanks

Answer 1

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

Answer 2

This is somewhat of a problem 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 pagniator manually.

$query = DB::table('posts')
    ->select(DB::raw('(c1 - c2) as distance'))
    ->having('distance', '<=', 5);

$perPage = 10;
$curPage = Paginator::getCurrentPage(); // reads the query string, defaults to 1

// clone the query to make 100% sure we don't have any overwriting
$itemQuery = clone $query;
$itemQuery->addSelect('posts.*');
// this does the sql limit/offset needed to get the correct subset of items
$items = $itemQuery->forPage($curPage, $perPage)->get();

// manually run a query to select the total item count
// use addSelect instead of select to append
$totalResult = $query->addSelect(DB::raw('count(*) as count'))->get();
$totalItems = $totalResult[0]->count;

// make the paginator, which is the same as returned from paginate()
// all() will return an array of models from the collection.
$paginatedItems = Paginator::make($items->all(), $totalItems, $perPage);

Tested with the following schema using MySQL:

Schema::create('posts', function($t) {
    $t->increments('id');
    $t->integer('c1');
    $t->integer('c2');
});

for ($i=0; $i < 100; $i++) { 
    DB::table('posts')->insert([
        'c1' => rand(0, 10),
        'c2' => rand(0, 10),
    ]);
}