Laravel & DataTable - Custom Filtering / Ordering

This is part 3 of the Laravel & DataTable, today I will show you how to add more controls with extra filtering and ordering to the DataTable.

Index

0. Setting up DataTable in Laravel
1. Passing Data
2. Filtering / Ordering
3. Export to CSV, Excel or PDF

With the understanding of setting up DataTable and how we would send data from backend to the frontend, let's first step through how to enable / disable ordering.

We would modify or create the table needed for this example.

php artisan make:migration "CreateBooksTable"

class CreateBooksTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('books', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('author');
            $table->date('published_at');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('books');
    }
}

Create a factory to automate the database seeding.

<?php

use Faker\Generator as Faker;

/*
|--------------------------------------------------------------------------
| Model Factories
|--------------------------------------------------------------------------
|
| This directory should contain each of the model factory definitions for
| your application. Factories provide a convenient way to generate new
| model instances for testing / seeding your application's database.
|
*/

$factory->define(App\Book::class, function (Faker $faker) {
    return [
        'name' => $faker->name,
        'author' => $faker->name,
        'published_at' => $faker->date,
    ];
});

After which, we can easily execute a command in our tinker environment to populate the database.

php artisan tinker
 
 >>>  factory(App\Book::class, 100)->create();

This will insert 100 rows of book data into our database. A standard blade boilerplate to display the data can be found in Lesson 1 if you need it.

Initiatialise the DataTable as below and you will notice that it automatically had the first column sorted.

<script>
    $(document).ready(function () {
        $('#book-table').DataTable({
            processing: true,
            language: {
                processing: '<span>Processing</span>',
            },
            serverSide: true,
            ajax: '{{ route('books.query') }}',
            columns: [
                { data: 'name' },
                { data: 'author' },
                { data: 'published_at' },
            ],
        });
    });
</script>

Sorting / Ordering

Default to No Sorting

$('#book-table').DataTable({
 order: [], 
 // ...
});

Default to Sort 2nd Column Descending

$('#book-table').DataTable({
 order: [2, 'desc'],  // use 'asc' for ascending
 // ...
});

Disable Sorting

$('#book-table').DataTable({
 ordering: false
 // ...
});

Filtering

Disable Searching Totally

$('#book-table').DataTable({
 searching: false
 // ...
});

Disable Searching on Specified Column

$('#book-table').DataTable({
 columns: [
 	{ data: 'name' },
 	{ data: 'author', searchable: false },
 	{ data: 'published_at' },
 ],
 // ...
});

Enable Case Sensitive Search

$('#book-table').DataTable({
 columns: [
 	{ data: 'name' },
 	{ data: 'author', searchable: false },
 	{ data: 'published_at' },
 ],
 // ...
});

Custom Filter

Now comes the fun part, what if you need a custom filter? For example, you need to filter by date range?

First, let's add a start date and end date to our blade file.

...
<div class="row m-5">
    <div class="col-6">
      <label for="start-date">Start Date</label>
      <input id="start-date" class="form-control" type="date">
    </div>
    <div class="col-6">
      <label for="end-date">End Date</label>
      <input id="end-date" class="form-control" type="date">
    </div>
  </div>
  <div class="row m-5">
    <div class="col-12">
      <button id="btn-filter" class="btn btn-primary">Filter</button>
    </div>
  </div>
  ...

Next, we would need to modify our DataTable initialisation to pass the value of start date and end date to the backend.

<script>
  $(document).ready(function () {
    let table = $('#book-table').DataTable({
      processing: true,
      language: {
        processing: '<span>Processing</span>',
      },
      serverSide: true,
      ajax: {
        url: '{{ route('books.query') }}',
        method: 'get',
        data: function (d) {
            d.start_date = $('#start-date').val(); // Pass along start date and end date here
            d.end_date = $('#end-date').val();
        }
      },
      columns: [
        {data: 'name'},
        {data: 'author', searchable: false},
        {data: 'published_at'},
      ],
    });

    $("#btn-filter").click(function(){
        table.draw(); // Update data table upon filter button clicked
    });

  });

Update your controller code as below:

public function query()
 {
    $query = \App\Book::query();
    
    // Add the where clause if there's start_date & end_date passed in
    if(request('start_date') && request('end_date')){
    	$query->whereBetween('published_at', [request('start_date'), request('end_date')]);
    }
        
    return Datatables::of($query)->make(true);
 }

Now you should be able to query the DataTable by the newly added start date and end date!

Conclusion

DataTable is highly customizable, there's tons more stuff you can do with it, visit DataTable's Options reference page and just key in search or order for more options.