Datatable provides an easy way to manage, filter, sort, and arrange the data in the form of a table. If I talk about the Jquery Datatables filter in Laravel 6 then I had already posted a tutorial on Yajra datatable implementation. In that tutorial, I had shown you the filter option which is provided by the datatable library. But in this post, I will gonna show you the column filter for each column. This will allow the specific filter according to the data of the column in the table.
Prerequisites
For creating this Laravel project, I am assuming you are ready with the below configuration in your system.
- PHP version >=7.3.9
- MySQL (version > 5)
- Apache/Nginx Server
- VS Code Editor
So, now I am ready for creating a new Laravel project setup for implementing datatable column filter.
Create New Laravel Project For Datatable Filter
Here, I am going to create a new project in Laravel using the composer. So open the terminal or command window and hit the below line.
composer create-project --prefer-dist laravel/laravel datatable-filter
It will start installing the Laravel libraries inside the folder named datatable-filter. This will be your project folder. So, wait until the installation finishes.
Laravel 6 Custom Login and Registration with Session
Create and Configure Database
Open the MySQL database and create a new database as showing below.
CREATE DATABASE laravel6_datatable_filter;Now, we have the database in MySQL. Now, let’s make some configuration inside the Laravel project.
Configure Database in Laravel 6
Open the newly created project inside your favorite editor then navigate to the .env file.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel6_datatable_filter
DB_USERNAME=root
DB_PASSWORD=rootSo, finally, we have the database connection succeeded. Now, move to the next step for implementing the functionalities.
RESTful APIs in Laravel 6 with Passport Authentication
Create Model and Migration in Laravel 6
For implementing the datatable column filter, I am going to create a Model for the employee. I will create some dummy data inside the employee table. Then, those data can be used for the column filter.
php artisan make:model employee --migrationThe above command will create a Model named Employee
How to Upload Files and Images in Laravel 6 with Validation
Create Table Schema in Migration File
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateEmployeesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('employees', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name');
$table->string('email');
$table->string('phone');
$table->string('address');
$table->string('designation');
$table->string('salary');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('employees');
}
}
Migrate Tables
Once the schema has been created, let’s do the migration. The migration will create the table with the defined schema inside the database.
php artisan migrateThe above command will generate the tables inside the database.
Add Mass Fillable Data in the Employee Model
For inserting the values into the database table, we will need to specify the fillable data inside the Model. Then this model will insert the data for which the column is defined. So, in this case, we will have to add the employee table fields in the Employee model.
So, inside the project folder, navigate to app\Employee.php and then paste the below fillable data.
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Employee extends Model
{
protected $fillable = [
"name", "email", "phone", "address", "designation", "salary"
];
}
After completing the above steps, move to the next step. Here, we will create some dummy records into the employee table.
How to Implement Google Autocomplete Address in Laravel 6
Create Tinker Factory For Inserting Dummy Records
In Laravel, it is possible to insert dummy records using the Tinker factory class.
php artisan make:factory EmployeeFactory --model=EmployeeHit the above command to create a factory class for the Employee Model. It will generate a file inside the factories folder named EmployeeFactory.php. Now, we will have to specify the fields and the data type for which we are going to add the dummy records.
<?php
/** @var \Illuminate\Database\Eloquent\Factory $factory */
use App\Employee;
use Faker\Generator as Faker;
use Illuminate\Support\Str;
$factory->define(Employee::class, function (Faker $faker) {
return [
'name' => $faker->name,
'email' => $faker->unique()->safeEmail,
'phone' => $faker->unique()->phoneNumber,
'address' => $faker->address,
'designation' => Str::random(5),
'salary' => $faker->numberBetween(1000, 10000)
];
});
Now, at the final step, we will have to hit a command for generating the dummy records.
php artisan tinker
factory(App\Employee::class,200)->create();It will generate 200 records inside the employee table.
You can check into the table, here the records have been inserted.
Laravel 6 REST API For ToDo Application with Passport Auth
Create Controller
After that, we can move into the datatable column filter implementation steps. Hence, we will need to create a controller file in which we will retrieve the data from the Employee table. So, hit the below command for creating the EmployeeController.
php artisan make:controller EmployeeControllerInside the controller, just add the below code with the function. In this function, simply I have retrieved all the data from the employees table. This is being done by Laravel Eloquent. So simply the model will fetch all the records from the table from that it is associated.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Employee;
class EmployeeController extends Controller
{
public function index() {
$employees = Employee::all();
return view('employee', compact("employees"));
}
}
After retrieving the data, it will return to a view named employee. So, here we will need to create a view named employee.blade.php. But before creating a view just define the Route for this function.
How to Use AJAX in Laravel 6 with ToDo Application
Create a Route
In the controller, we have the only one function. So, just need a single route inside the routes/web.php file.
Route::get('employee', 'EmployeeController@index');In the next step, we will create a view for the employee in which our controller will return with the data.
Create a View
Create a view inside the resources/views and name it employee.blade.php. Then simply add the below code there.
<!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> Emp Id</th>
<th> Name </th>
<th> Email </th>
<th> Phone no. </th>
<th> Address </th>
<th> Designation </th>
<th> Salary </th>
</thead>
<tbody>
@foreach($employees as $employee)
<tr>
<td>{{$employee->id}}</td>
<td>{{$employee->name}}</td>
<td>{{$employee->email}}</td>
<td>{{$employee->phone}}</td>
<td>{{$employee->address}}</td>
<td>{{$employee->designation}}</td>
<td>{{$employee->salary}}</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>
Now, start column filtering. Here, you can see the data has been filtered.
Load Datatable with Default Filter Value
Sometimes, we need to load the datatable with some default value. In that case, we can set the value for the column filter. Suppose, we want to filter the data according to a specific name. Then in the table column just specify the value. Here, is the snippet.
<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
});
// set default value in column 1
table.columns(1).search( "Lili" ).draw();
});
</script>In the above code, I have set a default value for column 1 that is Name. Now, on the page load, the datatable will be filtered according to this name.
As a result, you can see the record has been filtered according to the name that is Lili.
Conclusion
Finally, we have achieved the datatable column filter in Laravel 6. This will make more ease for you when you will have the requirement to manage this type of tabular data. I hope this will helpful for you.
Leave a Reply