Cursor Pagination in PHP with a Column

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?

I think you should use ajax to fetch the next page records in json format — Amrit Shrestha, Jan 08 '19 at 09:05

score 0 · Accepted Answer · answered Jan 08 '19 at 09:06

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;
}

score 0 · Answer 2 · answered Jan 08 '19 at 09:15

I think this may solve your problem. It's a custom pagination

In your Blade View File:

<!-- pass variable page with a value 
     this generation of your pagination
     should be based to the number of rows
     of your table in the database and not
     static. use foreach for this.
-->
<a href="pagination?page=1">1</a>
...

In your Controller:

public function pagination(Request $request){
    // set the target page to 0 if page is 1 or null
    // else get the value 
    $targetPage = ($request->input('page') == 1 || !$request->input('page')) ? 0 : ($request->input('page') - 1) ;
    $noOfDataToGet = 10;      

    // get the number of data and skip the data based of target page * number of data        
    $tables = Tables::take($noOfDataToGet)->skip($targetPage * $noOfDataToGet)->get();
}

Hope this helps

Cursor Pagination in PHP with a Column

2 Answers2