How to Implement Column Filter in Laravel 7 Yajra Datatable

Yajra datatable provides a customized way to manage a large number of data set. You can easily filter out, sort the data from the table. There are various features of using the Yajra datatable. By using the Yajra datatable column filter, you can search for any record in a particular column. I have already shared tutorials on the implementation of Yajra datatable and server-side processing. But, in this post, I will show you the Yajra datatable column filter option in the Yajra datatable. Datatable already provides the global search option. But using the column filter, you can search for any data within a column.

Prerequisites

For this project, I have the below requirements in my system. My system is ready to create the Laravel 7 project using the composer.

1. PHP version >= 7.2.5
2. MySQL (version > 5)
3. Apache/Nginx Server
4. VS Code Editor

Create a Project For Yajra Datatable Column Filter

Firstly, I will be creating a new project in Laravel 7. So, that I will cover all the steps here. Secondly, on the dataset, I will be implementing the Yajra datatable. Lastly, I will show you the column filter on the implemented yajra datatable.

Hence, hit the below command in the terminal to create a new project.

composer create-project --prefer-dist laravel/laravel datatable-filter

Here, my project has been created and we are good to go for the next steps.

How to Implement Google Charts in Laravel 7

Create and Configure a Database

Firstly, I will be creating a new database in MySQL. Then, I will configure it for our project.

CREATE DATABASE laravel_datatable;

When the database is ready, let’s configure it in the .env file of the project.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel_datatable
DB_USERNAME=root
DB_PASSWORD=

Here, we have connected the database successfully. Now, in the next step, we will create a model and migration for the tables.

Add Yajra Datatable Package in Laravel 7

Yajra datatable package can be added easily inside the Laravel. So, we will be adding the package for the yajra datatable implementation.

composer require yajra/laravel-datatables-oracle

It will take a couple of minutes to finish the package installation. So, just wait till it finishes.

Create Model and Migration in Laravel 7

This project will require a model and migration for the data set. Here, we have the default model and migration for the Users. So, I will be using this one for this project. Otherwise, you can create a new model and migration.

In the project directory database->migrations you will find the create_users_table.php file. Now, replace the schema of the table with the below schema.

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->string('phone');
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

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

In the above schema, I have added some fields. Now, add the fillable data in the User mode (User.php) file.

How to Import CSV File Data in Laravel 6

Add Fillable Data in User Model

The fillable data is called the mass assignment in Laravel. The specified fields will be synchronized with the fields of the table.

<?php

namespace App;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

class User extends Authenticatable
{
    use Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name', 'email', 'password','phone'
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password', 'remember_token',
    ];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];
}

After the mass assignment, let’s migrate the table in the database.

How to Send Email Using Gmail SMTP in Laravel 7

Migrate Tables in the Database

We have only one table schema for the migration. That is the user’s table. So, let’s migrate it.

php artisan migrate

After the successful migration, the table will be generated inside the database. Now, in the next step, we will be filling out the table with the dummy records. And then on that dummy records, we will be implementing the

Create Tinker Factory For Inserting Dummy Records

Here, we will be creating some dummy records using the factory class. In the factory class, we will specify the fields for which the dummy record will be created. This will be done using the faker class. The faker class will create the instance of the specified fields.

You will find the factory class inside the database->factories folder. There is a default factory file for the user. So, just change the fields as showing below.

<?php

/** @var \Illuminate\Database\Eloquent\Factory $factory */

use App\User;
use Faker\Generator as Faker;
use Illuminate\Support\Str;

/*
|--------------------------------------------------------------------------
| 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(User::class, function (Faker $faker) {
    return [
        'name' => $faker->name,
        'email' => $faker->unique()->safeEmail,
        'phone' => $faker->unique()->phoneNumber,
        'email_verified_at' => now(),
        'password' => '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', // password
        'remember_token' => Str::random(10),
    ];
});

Once the fields are created, let’s generate the records using the tinker class.

How to Implement Yajra DataTable in Laravel 7

Generate Dummy Records

Laravel provides the tinker class to generate the n number of records for the specified table.

php artisan tinker
factory(App\User::class, 4762)->create();

Hit the above command and wait till it completes the execution.

Create Controller to Implement Yajra Datatable Column Filter

I will create a controller for the User, so that, we can add the functionality for the datatable.

php artisan make:controller UserController

Now, the controller is ready. So, in the next step, let’s add the functionality to implement the datatable. In the controller, we will fetch the data from the users table.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\User;

class UserController extends Controller
{
    // ------------- [ Users ] --------------
    public function users() {
        $users      =       User::all();
        return view("users", compact("users"));
    }

}

Here, I have fetched the data from the users table and returned it to the users view. So, as per the requirement, we will create a view named users.blade.php.

In this view we will be implementing the datatable and column filter as well.

Create a View to Implement Yajra Datatable Column Filter

We will create a view named users.blade.php. In this view file, we will display the user’s data which are passed through the controller. Then, with that data, we will implement the yajra datatable along with the column filter. Now, in the view, just add the below snippet.

<!doctype html>
<html lang="en">
  <head>
    <title>Datatable Column Filter | Laravel 6 | Programming Fields</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.css">
    <style>
        thead input {
           width: 100%;
        }
    </style>
  </head>
  <body>
    <div class="container=fluid p-3">
        <h4 class="text-center"> Datatable Column Filter </h4>
        <table class="table table-bordered" id="mytable">
            <thead>
                <th> User Id</th>
                <th> Name  </th>
                <th> Email </th>
                <th> Phone no. </th>
            </thead>
            <tbody>
                @foreach($users as $user)
                <tr>
                    <td>{{$user->id}}</td>
                    <td>{{$user->name}}</td>
                    <td>{{$user->email}}</td>
                    <td>{{$user->phone}}</td>
                </tr>
                @endforeach
            </tbody>
        </table>
    </div>
    <script src="https://code.jquery.com/jquery-3.4.1.js" integrity="sha256-WpOohJOqMqqyKL9FccASB9O0KwACQJpFTUBLTYOVvVU=" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"></script>

    <script>
        $(document).ready(function() {
            $('#mytable thead tr').clone(true).appendTo( '#mytable thead' );
            $('#mytable thead tr:eq(1) th').each( function (i) {
                var title = $(this).text();
                $(this).html( '<input type="text" placeholder=" Search '+title+'" />' );

                $( 'input', this ).on( 'keyup change', function () {
                    if ( table.column(i).search() !== this.value ) {
                        table
                            .column(i)
                            .search( this.value )
                            .draw();
                    }
                });
            });

            var table = $('#mytable').DataTable( {
                orderCellsTop: true,
                fixedHeader: true
            });
        });
    </script>
  </body>
</html>

In the above blade file, I have iterated the data which are coming from the controller. Then the data is grouped into the table row and column. For the column search, I have appended one more table row. After that, created an input fields for the searching.

By using the keyup function, the data will be filtered out on the basis of typed letter/number.

Create Route

For the above controller function, I will create a web route.

Route::get("users", "UserController@users");

Save and run the project to see the result.

In the result, you can see we have the column search in the datatable.

Now, let’s try datatable column filter option by typing any letter.

Create a CRUD App in React.js Using Laravel 7 RESTful API

As a result, you can see there, out of 4762 rows only one record is filtered out using the column filter.

Conclusion

Yajra datatable provides the column filter option as well. We have already implemented the datatable pagination, sorting, searching, etc options. It customizes the large set of data by splitting into chunks. In this way, you can manage the large set of data with the filter option. The Yajra datatable already provides the global search option. By using the global search you can filter out from all the columns. But, if you want to search within a specific column then this will help you out.