• 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

Expected result

Steps To Solution:

• 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:

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

CodePudding user response：

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

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