带有列的PHP中的游标分页

Question

I am using Laravel version 5.2. As its having large amount of data, I am using cursor pagiations as below table having primary id and name fields

for page 1 -> select * from table LIMIT 10

For page 2 -> select * from table where id < $lastpagelastementID LIMIT 10

As the same way, if we sort by the name column, How can we handle this cursor pagination?
Is there any option to do this?

we can use Laravel pagination as Explained in

Laravel Pagination

But i need a Cursor Pagination as above. Can any one help me to find solutions?

Answer 1

You can install this package via composer using:

composer require juampi92/cursor-pagination

Config

To publish the config file to config/cursor_pagination.php run:

php artisan vendor:publish --provider="Juampi92\CursorPagination\CursorPaginationServiceProvider" --tag="config"

How does it work

The main idea behind a cursor pagination is that it needs a context to know what results to show next. So instead of saying page=2, you say next_cursor=10. The result is the same as the old fashioned page pagination, but now you have more control on the output, cause next_cursor=10 should always return the same (unless some records are deleted).

Pros

• New rows don't affect the result, so no duplicated results when paginating.
• Filtering by an indexed cursor is way faster that using database offset.
• Using previous cursor to avoid duplicating the first elements.

Cons

No previous page, although the browser still has them. No navigating to arbitrary pages (you must know the previous result to know the next ones).

Paginating Query Builder Results

There are several ways to paginate items. The simplest is by using the cursorPaginate method on the query builder or an Eloquent query. The cursorPaginate method automatically takes care of setting the proper limit and fetching the next or previous elements based on the cursor being viewed by the user. By default, the cursor is detected by the value of the page query string argument on the HTTP request. This value is automatically detected by the package taking your custom config into account, and is also automatically inserted into links and meta generated by the paginator.

public function index()
{
    $users = DB::table('users')->cursorPaginate();
    return $users;
}