How to create pagination but with a max limit?

Let's say I have a table with 100,000 rows. I want only the first 1000 rows, but I also want to paginate them by 25 as well. Is there any way to do it?

I saw this post: https://laracasts.com/discuss/channels/laravel/how-to-limit-in-pagination-in-laravel

The answer there was to paginate the collection, but is it possible to instead do a normal pagination over database instead?

LaryAI

3 years ago

Level 58

Yes, it is possible to do a normal pagination over the database with a max limit. You can use the take method to limit the number of rows returned by the query, and then use the paginate method to paginate the results.

Here's an example code snippet that limits the query to the first 1000 rows and paginates them by 25:

$items = DB::table('my_table')
    ->orderBy('created_at', 'desc')
    ->take(1000)
    ->paginate(25);

In this example, my_table is the name of the table you want to paginate, and created_at is the column you want to order the results by. You can replace these with the appropriate values for your use case.

Note that the take method limits the number of rows returned by the query, so it's important to order the results before calling take. If you don't order the results, you may get unexpected results.

Also, keep in mind that limiting the query to the first 1000 rows may not be the most efficient way to paginate large datasets. If you have a very large table, you may want to consider using a more advanced pagination technique, such as cursor-based pagination.

Snapey

3 years ago

Level 122

Do you need to show pagination controls that reflect just 1000 records?

JussiMannisto

3 years ago

Level 50

Pagination uses limit and offset in the database queries. Instead of limiting the results to 1000 rows, you could limit them by the number of pages. As far as I know, there's no built-in way to do this with paginate() or simplePaginate(), but you can do it yourself.

Pagination uses the page parameter for the page number by default. You could set up validation rules to only allow values between 1..40. But you'll also have to deal with the number of page links rendered in the UI.

You could modify the UI to only show pages between 1...40. A cleaner way is to pass your own modified version of the paginator object to the view. paginate() returns a LengthAwarePaginator object*. You could construct a new object with a limited $total value. Looking at the constructor, you could probably do something like this:

use Illuminate\Pagination\LengthAwarePaginator;

...

$maxPages = 40;
$pageSize = 25;
$paginator = $query->paginate($pageSize);
$limitedTotal = min($paginator->total(), $maxPages * $pageSize);

$customPaginator = new LengthAwarePaginator(
	$paginator->items(),
	$limitedTotal,
	$paginator->perPage(),
	$paginator->currentPage(),
	$paginator->getOptions()
);

// And then pass $customPaginator instead of $paginator.

None of the code above is tested. See the LengthAwarePaginator constructor for reference.

* Strictly speaking, paginate() returns a \Illuminate\Contracts\Pagination\LengthAwarePaginator interface as per the function signature. But the LengthAwarePaginator class implements that interface and is used by default.

Please or to participate in this conversation.