Laravel: Using Pagination, Sorting and Filtering with Your Tables

Laravel: Using Pagination, Sorting and Filtering with Your Tables

Introduction.

In Laravel, when you create websites displaying some data, you almost
always need to display tables of results (e.g. Products, Categories, etc).
The problem with displaying these tables is that you need to make them
sortable and filterable. Plus, you may need to paginate your results since the table may
become very big and load slowly.

Since this is a very common and very important task, in this
article we describe how to do that so you can copy and paste this solution into
your Laravel project.

We will describe two methods:

  • Using Laravel Eloquent built-in method paginate() for pagination and the package kyslik/column-sortable for sorting.
  • Using a third-party JavaScript library called DataTables.

We will describe both of them on an example of an e-commerce website with the list of Products.

Pagination

We will start from describing the method of implementing the pagination of your table (dividing the results in to pages).
In Laravel, you can easily do pagination using the Eloquent-provided method called paginate(). For instance,
assume you need to paginate your Product models. You can add the following controller action:

public function indexPaging()
{
    $products = Product::paginate(5);

    return view('products.index-paging')->with('products', $products);
}

You can see that we call paginate() on the Product model and pass it the page size (5).
This method automatically reads the request and gets the page query parameter from it. This allows it
do determine the current page number.

The corresponding Blade view template resources/views/products/index-paging.blade.php will look like below:

@extends('layout')

@section('content')
<h1>Products</h1>

<p>
    <a class="btn btn-primary" href="/products/create"><span class="glyphicon glyphicon-plus"></span> Add Product</a>
</p>

<table class="table table-bordered table-hover">
    <thead>
        <th>Category</th>
        <th>Name</th>
        <th>SKU</th>
        <th>Price</th>
        <th>Actions</th>
    </thead>
    <tbody>
        @if ($products->count() == 0)
        <tr>
            <td colspan="5">No products to display.</td>
        </tr>
        @endif

        @foreach ($products as $product)
        <tr>
            <td>{{ $product->category->name }}</td>
            <td>{{ $product->name }}</td>
            <td>{{ $product->sku }}</td>
            <td>${{ $product->price }}</td>
            <td>
                <a class="btn btn-sm btn-success" href="{{ action('ProductsController@edit', ['id' => $product->id]) }}">Edit</a>

                <form style="display:inline-block" action="{{ action('ProductsController@destroy', ['id' => $product->id]) }}" method="POST">
                    @method('DELETE')
                    @csrf
                    <button class="btn btn-sm btn-danger"> Delete</button>
                </form>
            </td>
        </tr>
        @endforeach
    </tbody>
</table>

{{ $products->links() }}

<p>
    Displaying {{$products->count()}} of {{ $products->total() }} product(s).
</p>

@endsection

Here you need to notice the $products->count() method and $products->total() method which
give you the number of items in the current page and the total number of items.

Also take a look at the $products->links() method which draws the pagination control below the table.

To make the indexPaging() action work, you need to add a route for it to your routes/web.php file:

Route::get('products/index-paging', 'ProductsController@indexPaging');

Now if you open the URL http://yourwebsite.com/products/index-paging in your browser, you should be able
to see the table with pages:

Table with pages

If you click a page button below the table, you may see the URL changes like http://laravel.local/products/index-paging?page=2
where the page=2 query parameter means the current page number.

Sorting

Next let’s implement sorting of columns for the paginated table. To simplify our work a lot, we will use
the Composer package called kyslik/column-sortable. To install that package, type:

composer require kyslik/column-sortable

Next, you need to modify your config file config/app.php and add the following item
under the providers subkey:

KyslikColumnSortableColumnSortableServiceProvider::class

Finally, run the following command to create the config/columnsortable.php config file:

php artisan vendor:publish --provider="KyslikColumnSortableColumnSortableServiceProvider" --tag="config"

Now you have the package set up. To make your Product model sortable, you need to modify it a little:

<?php

namespace App;

use IlluminateDatabaseEloquentModel;
use KyslikColumnSortableSortable;

class Product extends Model
{
    use Sortable;

    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'products';

    protected $fillable = ['name', 'sku', 'category_id', 'price'];

    public $sortable = ['name',
                        'sku',
                        'category_id',
                        'price'];

    public function category()
    {
        return $this->belongsTo('AppCategory');
    }
}

Note the line use KyslikColumnSortableSortable; in the beginning which is the shortcut for
the Sortable trait we will use.

The line use Sortable; inside the model class enables this trait in your model.

Finally, the $sortable property defines which columns should displayed as sortable.

public $sortable = ['name',
                    'sku',
                    'category_id',
                    'price'];

To demonstrate how this work, let’s add another action to our controller:

public function indexSorting()
{
    $products = Product::sortable()->paginate(5);

    return view('products.index-sorting')->with('products', $products);
}

You can see that now we call the sortable() method on the Product class. This method is very similar to
paginate() since it reads the HTTP request’s sort and direction query parameters and modifies the underlying
SQL query as needed.

The corresponding Blade view template resourses/views/products/index-sorting.blade.php will look like below:

@extends('layout')

@section('content')
<h1>Products</h1>

<p>
    <a class="btn btn-primary" href="/products/create"><span class="glyphicon glyphicon-plus"></span> Add Product</a>
</p>

<table class="table table-bordered table-hover">
    <thead>
        <th>@sortablelink('category.name', 'Category')</th>
        <th>@sortablelink('name', 'Name')</th>
        <th>@sortablelink('sku', 'SKU')</th>
        <th>@sortablelink('price', 'Price')</th>
        <th>Actions</th>
    </thead>
    <tbody>
        @if ($products->count() == 0)
        <tr>
            <td colspan="5">No products to display.</td>
        </tr>
        @endif

        @foreach ($products as $product)
        <tr>
            <td>{{ $product->category->name }}</td>
            <td>{{ $product->name }}</td>
            <td>{{ $product->sku }}</td>
            <td>${{ $product->price }}</td>
            <td>
                <a class="btn btn-sm btn-success" href="{{ action('ProductsController@edit', ['id' => $product->id]) }}">Edit</a>

                <form style="display:inline-block" action="{{ action('ProductsController@destroy', ['id' => $product->id]) }}" method="POST">
                    @method('DELETE')
                    @csrf
                    <button class="btn btn-sm btn-danger"> Delete</button>
                </form>
            </td>
        </tr>
        @endforeach
    </tbody>
</table>

{!! $products->appends(Request::except('page'))->render() !!}

<p>
    Displaying {{$products->count()}} of {{ $products->total() }} product(s).
</p>

@endsection

It is very similar to our previous view template, but with two additions:

  • inside the table header, we use the @sortablelink with Blade to render the sortable columns.
  • at the bottom, we use {!! $products->appends(Request::except('page'))->render() !!} to render the pagination control, plus append the sort and direction
    query parameters to it.

To display the sorting arrows correctly, you also need to download and include Font Awesome into the head of
your layout template resourses/views/layout.blade.php:

<link rel="stylesheet" href="/css/font-awesome.min.css">

Finally, add another route into your routes/web.php:

Route::get('products/index-sorting', 'ProductsController@indexSorting');

And now, if you open the URL http://yourwebsite.com/products/index-sorting in your browser, you should be able to see the table with sorting enabled:

Products table with sorting

Filtering

To implement filtering on the table, we can use a regular HTML form. The Blade view template
resourses/views/products/index-filtering.blade.php will look like below:

@extends('layout')

@section('content')
<h1>Products</h1>

<p>
    <a class="btn btn-primary" href="/products/create"><span class="glyphicon glyphicon-plus"></span> Add Product</a>
</p>

<form class="form-inline" method="GET">
  <div class="form-group mb-2">
    <label for="filter" class="col-sm-2 col-form-label">Filter</label>
    <input type="text" class="form-control" id="filter" name="filter" placeholder="Product name..." value="{{$filter}}">
  </div>
  <button type="submit" class="btn btn-default mb-2">Filter</button>
</form>

<table class="table table-bordered table-hover">
    <thead>
        <th>@sortablelink('category.name', 'Category')</th>
        <th>@sortablelink('name', 'Name')</th>
        <th>@sortablelink('sku', 'SKU')</th>
        <th>@sortablelink('price', 'Price')</th>
        <th>Actions</th>
    </thead>
    <tbody>
        @if ($products->count() == 0)
        <tr>
            <td colspan="5">No products to display.</td>
        </tr>
        @endif

        @foreach ($products as $product)
        <tr>
            <td>{{ $product->category->name }}</td>
            <td>{{ $product->name }}</td>
            <td>{{ $product->sku }}</td>
            <td>${{ $product->price }}</td>
            <td>
                <a class="btn btn-sm btn-success" href="{{ action('ProductsController@edit', ['id' => $product->id]) }}">Edit</a>

                <form style="display:inline-block" action="{{ action('ProductsController@destroy', ['id' => $product->id]) }}" method="POST">
                    @method('DELETE')
                    @csrf
                    <button class="btn btn-sm btn-danger"> Delete</button>
                </form>
            </td>
        </tr>
        @endforeach
    </tbody>
</table>

{!! $products->appends(Request::except('page'))->render() !!}

<p>
    Displaying {{$products->count()}} of {{ $products->total() }} product(s).
</p>

@endsection

It looks very similar to the previous one (with pagination and sorting), but note the HTML form at the top.
When we submit this form, it will give us the filter query parameter in the HTTP request.

Now let’s add the controller action:

public function indexFiltering(Request $request)
{
    $filter = $request->query('filter');

    if (!empty($filter)) {
        $products = Product::sortable()
            ->where('products.name', 'like', '%'.$filter.'%')
            ->paginate(5);
    } else {
        $products = Product::sortable()
            ->paginate(5);
    }

    return view('products.index-filtering')->with('products', $products)->with('filter', $filter);
}

In this code, we take the filter parameter taken from the request. Depending on its length,
we either add an additional WHERE products.name LIKE '%filter%' condition to our SQL or not.

Add another route to your routes/web.php:

Route::get('products/index-filtering', 'ProductsController@indexFiltering');

If you now open the URL http://yourwebsite.com/products/index-filtering in your browser,
you should be able to see the filtering form above the table:

Products table with filtering

You can enter some product name or part of it and click Filter to select only the products with the name LIKE the filter.

DataTables

DataTables is a JavaScript library which can do pagination, sorting and filtering out of the box.
Plus it can efficiently update the table in an AJAX mode, so the entire page doesn’t need to reload. We would recommend DataTables
for more complex projects where you require more presentation features.

Add the following lines to your layout head to load DataTables:

<link href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css" rel="stylesheet">  
<script src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>

Please note that DataTables should be loaded AFTER jQuery. If you are already using jQuery, ensure you
put the DataTables script after it.

To demonstrate how to use DataTables in Laravel, let’s create another controller action:

public function indexDatatables()
{
    return view('products.index-datatables');
}

The corresponding view resourses/views/products/index-datatables.blade.php will look like below:

@extends('layout')

@section('content')
<h1>Products</h1>

<p>
    <a class="btn btn-primary" href="/products/create"><span class="glyphicon glyphicon-plus"></span> Add Product</a>
</p>

<table id="products-table" class="table table-bordered table-hover" class="display" style="width:100%">
    <thead>
        <th>Category</th>
        <th>Name</th>
        <th>SKU</th>
        <th>Price</th>
        <th>Actions</th>
    </thead>
    <tbody>

    </tbody>
</table>

<script>
$(document).ready(function() {
    $('#products-table').DataTable({
        "serverSide": true,
        "ajax": {
            url: "{{ action('ProductsController@productsDataSource') }}", 
            method: "get"
        },
        "columnDefs" : [{
            'targets': [4], 
            'orderable': false
        }],
    });
});
</script>

@endsection

Note how we define the HTML table in the code above. We just define its head, keeping the body empty.
This is because that the real body of the table will be populated by DataTables on the fly.

Also note the JavaScript code at the bottom. It initializes DataTables and tells it to use AJAX for querying
a server-side data source. Using the data source allows to load only the needed part of results very fast.

Let’s implement the data source action in our controller:

public function productsDataSource(Request $request) {

    $search = $request->query('search', array('value' => '', 'regex' => false));
    $draw = $request->query('draw', 0);
    $start = $request->query('start', 0);
    $length = $request->query('length', 25);
    $order = $request->query('order', array(1, 'asc'));        

    $filter = $search['value'];

    $sortColumns = array(
        0 => 'category_name',
        1 => 'products.name',
        2 => 'products.sku',
        3 => 'products.price',
    );

    $query = Product::join('categories', 'categories.id', '=', 'products.category_id')
            ->select('products.*', 'categories.name as category_name');

    if (!empty($filter)) {
        $query->where('products.name', 'like', '%'.$filter.'%');
    }

    $recordsTotal = $query->count();

    $sortColumnName = $sortColumns[$order[0]['column']];

    $query->orderBy($sortColumnName, $order[0]['dir'])
            ->take($length)
            ->skip($start);

    $json = array(
        'draw' => $draw,
        'recordsTotal' => $recordsTotal,
        'recordsFiltered' => $recordsTotal,
        'data' => [],
    );

    $products = $query->get();

    foreach ($products as $product) {

        $json['data'][] = [
            $product->category->name,
            $product->name,
            $product->sku,
            $product->price,
            view('products.actions', ['product' => $product])->render(),
        ];
    }

    return $json;
}

In the above code, we just load the chunk of the results using the query parameters provided by DataTables:

  • the search parameter defines the filter
  • the draw indicates the sequential drawing number and we don’t care about it
  • the start and length parameters indicate the OFFSET and LIMIT for the SQL query
  • the order parameter defines the sorting column name and the sorting order

Finally, add another route to your routes/web.php:

Route::get('products/index-datatables', 'ProductsController@indexDatatables');

Now if you open the URL http://yourwebsite.com/products/index-datatables, you should be able to see the table
with pagination, sorting and filtering looking like in the image below:

DataTables powered table

Conclusion

In this article we described how to implement a very useful task in Laravel – displaying tabular results
with pagination, sorting and filtering. We considered two methods: the built-in Eloquent capabilities and DataTables.

The advantage of the first approach we used is its relative simplicity. The disadvantage
is that every time you click sorting or pagination buttons, the entire page reloads. However, this method is very good in
most cases. Plus it is very fast since we load only a small portion of results (assuming you have configured your database indexes properly).

The latter method (DataTables) is even more powerful. For example, you can make your table responsive or add a filter per each column.
Take a look at the DataTables documentation for possible capabilities. Another advantage of this method is that it uses AJAX, so the entire
page doesn’t need to reload each time you click a pagination button.

2 Replies to “Laravel: Using Pagination, Sorting and Filtering with Your Tables”

  1. Thanks for the tutorial, however you have some backslashes stripped 🙂
    in config/app.php:
    KyslikColumnSortableColumnSortableServiceProvider::class,
    Should be:
    Kyslik\ColumnSortable\ColumnSortableServiceProvider::class,

    And the command should be:
    php artisan vendor:publish –provider=”Kyslik\ColumnSortable\ColumnSortableServiceProvider” –tag=”config”

  2. class path is wrong in config/app.php
    correct it : Kyslik\ColumnSortable\ColumnSortableServiceProvider::class,
    correct command to: php artisan vendor:publish –provider=”Kyslik\ColumnSortable\ColumnSortableServiceProvider” –tag=”config”

Leave a Reply

Your email address will not be published.