Laravel Datatables Relationship with Filter Column
Hi there! If you're working on a Laravel project and need to display data from related models in a dynamic table, Laravel Datatables is the perfect tool for the job. It not only makes creating interactive tables easy but also allows you to add filter columns for better user experience.
In this guide, I’ll show you how to set up Laravel Datatables with relationships and create custom filter columns.
Laravel Datatables Relationship with Filter Column
In this step, we'll install laravel 11 application using the following command.
composer create-project laravel/laravel example-app
Next, we'll install yajra datatable using the following command.
composer require yajra/laravel-datatables
Then, we'll create migration using the following command.
php artisan make:migration create_roles_table
Migration:
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*/
public function up(): void
{
Schema::create('roles', function (Blueprint $table) {
$table->id();
$table->string("name");
$table->timestamps();
});
Schema::table('users', function (Blueprint $table) {
$table->integer("role_id")->default(1);
});
}
/**
* Reverse the migrations.
*/
public function down(): void
{
Schema::dropIfExists('roles');
}
};
Migrate the table into the database using the following command.
php artisan migrate
Next, we'll create a controller and add the logic for datatable with a filter.
app/Http/Controllers/UserController.php
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\User;
use DataTables;
class UserController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index(Request $request)
{
if ($request->ajax()) {
$data = User::with("role");
return Datatables::of($data)
->addIndexColumn()
->addColumn('status', function($row){
if($row->status){
return 'Active';
}else{
return 'Inactive';
}
})
->addColumn('created_at', function ($row) {
return $row->created_at->format('Y-m-d H:i:s');
})
->addColumn('action', function($row){
$btn = 'View';
return $btn;
})
->filterColumn("role", function($query, $value){
$query->whereHas("role", fn($q) => $q->where("name", "LIKE", "%$value%"));
})
->rawColumns(['action', 'status'])
->make(true);
}
return view('users');
}
}
Then, we'll define the routes into the web.php file
routes/web.php
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\UserController;
Route::get('users', [UserController::class, 'index'])->name('users.index');
Next, we'll create a blade file and add the following HTML code to that file.
resources/views/users.blade.php
<!DOCTYPE html>
<html>
<head>
<title>Laravel Datatables Relationship with Filter Column - Techsolutionstuff</title>
<link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.0.1/css/bootstrap.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/1.11.4/css/dataTables.bootstrap5.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.11.4/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.11.4/js/dataTables.bootstrap5.min.js"></script>
</head>
<body>
<div class="container">
<div class="card mt-5">
<h3 class="card-header p-3">Laravel Datatables Relationship with Filter Column - Techsolutionstuff</h3>
<div class="card-body">
<table class="table table-bordered data-table">
<thead>
<tr>
<th>No</th>
<th>Name</th>
<th>Email</th>
<th>Created At</th>
<th>Status</th>
<th>Role</th>
<th width="100px">Action</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
</div>
</body>
<script type="text/javascript">
$(function () {
var table = $('.data-table').DataTable({
processing: true,
serverSide: true,
ajax: "{{ route('users.index') }}",
columns: [
{data: 'id', name: 'id'},
{data: 'name', name: 'name'},
{data: 'email', name: 'email'},
{data: 'created_at', name: 'created_at'},
{data: 'status', name: 'status'},
{data: 'role.name', name: 'role'},
{data: 'action', name: 'action', orderable: false, searchable: false},
]
});
});
</script>
</html>
Run the laravel 11 application using the following command.
php artisan serve
You might also like:
- Read Also: Laravel 11 Replicate Model with Relationships
- Read Also: Laravel 11 Datatable Server Side Pagination
- Read Also: How to Add Bootstrap 5 Datatable Example
- Read Also: How to Add Row in Datatable on Click of Button
