MySQL Query Builder: Database Pagination

Laravel Course

- Paginating Eloquent Results
- Displaying Pagination Results
- Paginator Instance Methods
- Customizing the Pagination Links View

Laravel frammework has a paginator system integrated with the query builder and Eloquent ORM, and provides an easy-to-use pagination system of database results.

Paginating Query Builder Results

The simplest way to paginate database results is by using the paginate() method on the query builder or an Eloquent query.
The paginate() method takes one argument: the number of items you would like displayed "per page". Then, it automatically sets the proper limit and offset based on the current page.
The current page with paginating results is detected by the value of the query string argument on the HTTP request, which is automatically inserted into pagination links.

- Example of a controller that displays 20 items per page:

<?php
namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class PagesController extends Controller{
  //Shows paginated results
  //@return a view
  public function index(){
    $pages = DB::table('table_name')->paginate(20);

    return view('pages.index', ['pages'=>$pages]);
  }
}

- You may call paginate() after setting other parameters on the query, such as where() clauses or DB:raw().

$pages = DB::table('table_name')->where('visits', '>', 100)->paginate(20);

$pages = DB::table('table_name')->select(DB::raw('*, DATE_FORMAT(dtreg, "%Y-%m-%d") as dtreg'))->paginate(20);

Paginating Eloquent Results

You may also paginate Eloquent queries. The syntax is nearly identical to paginating query builder results:

$users = App\User::paginate(20);

$users = User::where('votes', '>', 100)->paginate(20);

Displaying Pagination Results

The result set with data returned by the paginate() method may be looped as an array, (for example with foreach()).
To display the automatically created pagination links, use the $pages->links() method (Here, $pages is the object returned by the paginate() method).
You may display the results and render the page links using Blade template:

<div class="container">
@foreach($pages as $page)
  {{ $page->id }} - {{ $page->title }}
@endforeach
</div>

{{ $pages->links() }}

- The $pages->links() method returns a <ul> list with pagination links, compatible with the Bootstrap CSS framework.

simplePaginate() method

If you only need to display simple "Next" and "Previous" links in your pagination view (without links for each page number), you may use the simplePaginate() method to perform a more efficient query.

//with query builder
$users = DB::table('users')->simplePaginate(20);
//or
$users = DB::table('users')->where('votes', '>', 100)->simplePaginate(20);

//with Eloquent
$users = User::simplePaginate(20);
//or
$users = User::where('votes', '>', 100)->simplePaginate(20);

Customizing the Paginator URI

If you want the paginator to generate links like: //example.com/custom/url?page=N, use the withPath() method, with the "custom/url" string as argument.

Route::get('pages', function(){
  $pages = DB::table('table_name')->paginate(20);

  $pages->withPath('custom/url');
  //
});

- Then, use the {{ $pages->links() }} in your view.

To append strings to each pagination link, use the appends() method.
- For example, to append "sort=votes" to each pagination link, you may use the following code: If you wish to append a "hash fragment" to the paginator's URLs, you may use the fragment() method.
- For example, to append '#bip' to the end of each pagination link, use the following code:

Paginator Instance Methods

Each paginator instance (returned by the paginate() method) provides additional pagination informations via the following methods:

• count() - returns the number of items in current result set.
• currentPage() - returns the index number of current page.
• firstItem() - the index number of the first item in current page.
• hasMorePages() - True if there is a next page, otherwise False.
• lastItem() - the index number of the last item in current page.
• lastPage() - returns the index number of last page (Not available when using simplePaginate()).
• nextPageUrl() - URL of next page. NULL if there is not a next page.
• perPage() - number of items per page.
• previousPageUrl() - URL of previous page. NULL if there is not a previous page.
• total() - total number of items (Not available when using simplePaginate()).
• url($nr) - URL of the pagination page with index of $nr.

Pagination results object as JSON

To return the results of the paginate() method in JSON format, you can use the toJson() method:

public function index(){
  $pages = DB::table('table_name')->paginate(15);

  return $pages->toJson();
}

The JSON from the paginator will include meta information such as total, current_page, last_page, and more.
The actual result objects will be available via the "data" key in the JSON array.
- The above code will return a JSON like this:

{
  "total": 50,
  "per_page": 15,
  "current_page": 1,
  "last_page": 4,
  "next_page_url": "https://coursesweb.net?page=2",
  "prev_page_url": null,
  "from": 1,
  "to": 15,
  "data":[
    {
      // Result Object of first item
    },
    {
      // Result Object of second item
    }
  ]
}

Customizing the Pagination Links View

By default, the returned HTML code with pagination links are compatible with the Bootstrap CSS framework.
To define your own HTML code for these links, create a view file: resources/views/pagination/default.blade.php with custom pagination code, and pass the view name ('pagination.default') as the first argument to the links() method.
- For example, this is the default Laravel pagination view:

@if($paginator->hasPages())
  <ul class="pagination">
    {{-- Previous Page Link --}}
    @if($paginator->onFirstPage())
      <li class="disabled"><span>&laquo;</span></li>
    @else
      <li><a href="{{ $paginator->previousPageUrl() }}" rel="prev">&laquo;</a></li>
    @endif

    {{-- Pagination Elements --}}
    @foreach($elements as $element)
      {{-- "Three Dots" Separator --}}
      @if(is_string($element))
        <li class="disabled"><span>{{ $element }}</span></li>
      @endif

      {{-- Array Of Links --}}
      @if(is_array($element))
        @foreach($element as $page => $url)
          @if($page == $paginator->currentPage())
            <li class="active"><span>{{ $page }}</span></li>
          @else
            <li><a href="{{ $url }}">{{ $page }}</a></li>
          @endif
        @endforeach
      @endif
    @endforeach

    {{-- Next Page Link --}}
    @if($paginator->hasMorePages())
      <li><a href="{{ $paginator->nextPageUrl() }}" rel="next">&raquo;</a></li>
    @else
      <li class="disabled"><span>&raquo;</span></li>
    @endif
  </ul>
@endif

- Create a php file: resources/views/pagination/default.blade.php, and copy the code above in that file. Modify the "pagination/default.blade.php" however you want.
- Then, display the pagination links using this code: You may also pass data to the pagination view:

- Documentation: Laravel - Database: Pagination

<ul>
 <li>http://coursesweb.net/html/</li>
 <li>http://coursesweb.net/css/</li>
</ul>

.some_class {
  display: list-item;
}

var obj = {
 "courses": ["php", "javascript", "ajax"]
};
var jsonstr = JSON.stringify(obj);
alert(jsonstr);    // {"courses":["php","javascript","ajax"]}

$strhtml = '<body><div id="dv1">CoursesWeb.net</div></body>';
$dochtml = new DOMDocument();
$dochtml->loadHTML($strhtml);
$elm = $dochtml->getElementById("dv1");
echo $elm->nodeValue;    // CoursesWeb.net