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

0 votes

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 ?

Dec 2, 2020 in Laravel by kartik
• 37,490 points
89 views

1 answer to this question.

0 votes

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!!

answered Dec 2, 2020 by Niroj
• 82,580 points

Related Questions In Laravel

0 votes
1 answer

How to make a constant and use globally in laravel?

Hii, You can create a constants.php page in config folder ...READ MORE

answered Mar 24, 2020 in Laravel by Niroj
• 82,580 points
357 views
0 votes
1 answer

How Can I Set the Default Value of a Timestamp Column to the Current Timestamp with Laravel Migrations?

Hello, To create both of the created_at and updated_at columns: $t->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP')); $t->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update ...READ MORE

answered Apr 2, 2020 in Laravel by Niroj
• 82,580 points
5,314 views
0 votes
1 answer

How to update a pivot table using Eloquent in laravel 5?

Hello @kartik, The code below solved my problem: $messages ...READ MORE

answered Sep 24, 2020 in Laravel by Niroj
• 82,580 points
892 views
+1 vote
1 answer

How to make anchor tag with routing using Laravel?

Hey @kartik, First you have to go to ...READ MORE

answered Mar 18, 2020 in Laravel by Niroj
• 82,580 points
5,641 views
0 votes
1 answer

What is redirection in Laravel?

Named route is used to give specific ...READ MORE

answered Mar 18, 2020 in Laravel by Niroj
• 82,580 points
653 views
0 votes
1 answer

How to install Laravel via composer?

Hello, This is simple you just need to ...READ MORE

answered Mar 23, 2020 in Laravel by Niroj
• 82,580 points
498 views
+1 vote
1 answer

What are named routes in Laravel and How can specify route names for controller actions?

Hey @kartik, Named routing is another amazing feature of ...READ MORE

answered Mar 23, 2020 in Laravel by Niroj
• 82,580 points
6,221 views
+2 votes
2 answers

How to add a new column to existing table of laravel in a migration?

You need do little modification in your ...READ MORE

answered Dec 10, 2020 in Laravel by anonymous
• 82,580 points
24,526 views
0 votes
1 answer

How to select all column name from a table in laravel?

You can get all columns name by ...READ MORE

answered Dec 2, 2020 in Laravel by Niroj
• 82,580 points
343 views