menu

Questions & Answers

Paginate count(*) query issue
  • Laravel Version: 8.0
  • PHP Version: 7.3.0
  • Database Driver & Version: MySQL 5.7.34

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.

  • Table name is users and it has 45 column

  • Route

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

  • Controller
namespace App\Http\Controllers\Api\V1;

class UsersController extends Controller
{
    public function index()
    {
        return User::paginate(10 , ['id']);
    }
 }
  • Call users route
  • Telescope showing me that two queries

Actual result

https://i.stack.imgur.com/X4kz1.png

Expected result

[Expected result image](https://i.stack.imgur.com/nhiA7.png)

Steps To Solution:

[Solution image](https://i.stack.imgur.com/z9Gn7.png)

  • The following image shows that I had done changes as per our functionality, It will take the first column name from the passed in the paginate method array of $columns params and that query does not scan all columns of the users tables.

Final Results:

https://i.stack.imgur.com/ns0r3.png

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

Comments:
2023-01-24 23:30:13
are you using ( App\Models\User; ) ?! what is [id]?
Answers(1) :

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

Comments:
2023-01-24 23:30:13
Any ideas for the using paginate() method because paginate method is also supported to give the $column value , in this, paginate method we have to pass the column value then and make support for the first $column param as a count($column[0]) the we can solve it in paginate method laravel.com/docs/9.x/…
2023-01-24 23:30:13
what do you mean, you can already access the models like this foreach($theprogram as $item){ dd($item); }
2023-01-24 23:30:13
@NikunjGadhiya if your issue is resolved, kindly mark the answer as complete (using the checkmark) :)