Laravel Datatables Relationship with Filter Column Example

In this post, I will show you how to filter relation column with laravel yajra datatables.

In this example, we’ll install the yajra/laravel-datatables package via Composer. We’ll create users and roles tables, where each user is linked to a role using the role_id field in the users table. We’ll define a belongsTo relationship between User and Role, then display the data using DataTables with filtering options for roles. Let’s go through the example below:

Step for Laravel Datatables Relationship with Filter Column Example

• Step 1: Install Laravel
• Step 2: Install Yajra Datatable
• Step 3: Craete Migration
• Step 4: Create Controller
• Step 5: Create Route
• Step 6: Create Blade File
• Run Laravel App

Follow the below steps:

Step 1: Install Laravel 11

This step is not required; however, if you have not created the Laravel app, then you may go ahead and execute the below command:

composer create-project laravel/laravel example-app

Step 2: Install Yajra Datatable

In this step, we need to install Yajra Datatable via the Composer package manager, so open your terminal and fire the below command:

composer require yajra/laravel-datatables

Step 3: Craete Migration

In this step, we will create migration to create roles table and role_id in users table, so, let's create it and update the following code:

php artisan make:migration create_roles_table

database/migrations/2024_11_20_130849_create_roles_table.php

<?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');
    }
};

now, you can run the following command to run the migration:

php artisan migrate

You can also add some dummy records to users and roles table.

Step 4: Create Controller

In this point, now we should create a new controller as UserController. This controller will manage layout and handle data requests, returning responses. So, put the content below in the controller file:

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');
    }
}

Step 5: Add Route

In this step, we need to create a route for the DataTables layout file and another one for getting data. So open your "routes/web.php" file and add the following route.

routes/web.php

<?php
  
use Illuminate\Support\Facades\Route;
  
use App\Http\Controllers\UserController;
    
Route::get('users', [UserController::class, 'index'])->name('users.index');

Step 6: Create Blade File

In Last step, let's create users.blade.php(resources/views/users.blade.php) for layout and we will write design code here and put following code:

resources/views/users.blade.php

<!DOCTYPE html>
<html>
<head>
    <title>Laravel 11 Yajra Datatables Tutorial - ItSolutionStuff.com</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 Yajra Datatables with Filter - ItSolutionStuff.com</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 Laravel App:

All the required steps have been done, now you have to type the given below command and hit enter to run the Laravel app:

php artisan serve

Now, Go to your web browser, type the given URL and view the app output:

http://localhost:8000/users

I hope it can help you...