CODEWITHSUNDEEP
laraveloptimizationeloquentquery-optimizationlaravel-8
Nik Patel
Nik Patel

Reputation: 354

Paginate count(*) query issue

Describe the Bug

When I use paginate for pagination the data it call the count( * ) query every time even I pass the column name like count(['id']), and the count( * ) query scan all row in the table.

Route::get("users", "UsersController@index");


namespace App\Http\Controllers\Api\V1;

class UsersController extends Controller
{
    public function index()
    {
        return User::paginate(10 , ['id']);
    }
 }

Actual result

https://i.sstatic.net/X4kz1.png

Expected result

[Expected result image](https://i.sstatic.net/nhiA7.png)

Steps To Solution:

[Solution image](https://i.sstatic.net/z9Gn7.png)

Final Results:

https://i.sstatic.net/ns0r3.png

I have tired to solving this issue any other way or idea then please let me know

Upvotes: 3

Views: 1458

Answers (1)

RG Servers
RG Servers

Reputation: 1784

Its not recommended to ever touch the vendor files, you can always just override the functionality inside of your model, you can also pass in the columns to override the getCountForPagination() and you can also pass the columns to simplePaginate() that doesn't invoke any counting!

In order to optimize the query to count and paginate, you can do it like this:

//We will call the query on the model
$program = Program::query()->getQuery();

//count the query by specific columns
$thePaginationCount = $program->getCountForPagination(['id']);

//paginate the results using simplePaginate and select the columns we want:
$thePaginatedProgram = $program->simplePaginate(10, ['id', 'name']);

return 'test: '.$thePaginatedProgram;

Will result like this:

select count(`id`) as aggregate from `programs`
select `id`, `name` from `programs` limit 11 offset 0

enter image description here

As you can see it will only load what we specify and its very efficient!

Final Note:

If you just want to paginate without the count, you can always call Model::simplePaginate($amount, [$columns...])

https://laravel.com/docs/9.x/pagination#simple-pagination

Upvotes: 4

Related Questions