Laravel 9 Datatables Column Relationship Search Tutorial

In this tutorial you will learn about the Laravel 9 Datatables Column Relationship Search Tutorial and its application with practical example.

In this Laravel 9 Datatables Column Relationship Search Tutorial I will show you how to display and search column in yajra dataTables with relationship in laravel 9. In this tutorial you will learn to display and filter column in yajra dataTables with relationship. In this article I will share example to display and filter column in yajra dataTables with relationship in laravel 9.

Table Of Contents−

Laravel 9 Datatables Column Relationship Search Tutorial

Let suppose you have two database tables first is posts and the other one is users and you have relationship in each table. Now you want to display and filter posts title and who write these posts (author name). This would require you to use laravel yajra dataTables columns with a relationship.

In this step by step tutorial I’ll demonstrates you to how to use a filter on dataTables columns with relationships. Please follow the instruction given below:

Install Laravel 9
Connecting App to Database
Install Yajra DataTable

Create Migration and Modal
Add route
Create Controller by Artisan Command

Create Blade File
Run Development Server

Install Laravel 9

First of all we need to create a fresh laravel project, download and install Laravel 9 using the below command

composer create-project --prefer-dist laravel/laravel blog

1	composer create-project --prefer-dist laravel/laravel blog

Connecting App to database

Now, lets create a MySQL database and connect it with laravel application. After creating database we need to set database credential in application’s .env file.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=Enter_Your_Database_Name
DB_USERNAME=Enter_Your_Database_Username
DB_PASSWORD=Enter_Your_Database_Password

DB_CONNECTION=mysql

DB_HOST=127.0.0.1

DB_PORT=3306

DB_DATABASE=Enter_Your_Database_Name

DB_USERNAME=Enter_Your_Database_Username

DB_PASSWORD=Enter_Your_Database_Password

Install Yajra DataTable

In this step, we will install Yajra Datatables Package via the composer dependency manager. Use the following command to install Yajra Datatables Package.

composer require yajra/laravel-datatables-oracle

1	composer require yajra/laravel-datatables-oracle

After Installing Yajra Datatables package, we need to add service provider and alias in config/app.php file as following.

config/app.php

.....
'providers' => [
    ....
    Yajra\DataTables\DataTablesServiceProvider::class,
]
'aliases' => [
    ....
    'DataTables' => Yajra\DataTables\Facades\DataTables::class,
]
.....

.....

'providers' => [

....

Yajra\DataTables\DataTablesServiceProvider::class,

]

'aliases' => [

....

'DataTables' => Yajra\DataTables\Facades\DataTables::class,

]

.....

Now let’s create post table migration and create Post Modal using following artisan command:

php artisan make:modal Post -m

1	php artisan make:modal Post -m

his will create migration, open migration file and put the following code in it:

database/migrations/2020_05_20_070104_create_posts_table.php

<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreatePostsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->id();
            $table->integer('user_id');
            $table->string('title');
            $table->timestamps();
        });
    }
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('posts');
    }
}

<?php

use Illuminate\Database\Migrations\Migration;

use Illuminate\Database\Schema\Blueprint;

use Illuminate\Support\Facades\Schema;

class CreatePostsTable extends Migration

{

/**

* Run the migrations.

* @return void

public function up()

{

Schema::create('posts', function (Blueprint $table) {

$table->id();

$table->integer('user_id');

$table->string('title');

$table->timestamps();

});

}

/**

* Reverse the migrations.

* @return void

public function down()

{

Schema::dropIfExists('posts');

}

Now, run the migration to create database table using following artisan command:

php artisan migrate

1	php artisan migrate

Now update following code in your Post model file to create a posts table.

<?php
 
namespace App\Models;
 
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use App\Models\User;
 
class Post extends Model
{
    use HasFactory;
     
    protected $fillable = ['title'];
 
    public function users()
    {
        return $this->belongsTo(User::class,'user_id','id');
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;

use Illuminate\Database\Eloquent\Model;

use App\Models\User;

class Post extends Model

{

use HasFactory;

protected $fillable = ['title'];

public function users()

{

return $this->belongsTo(User::class,'user_id','id');

}

Add route

After this, we need to define routes in “routes/web.php” file. Lets open “routes/web.php” file and add the following routes in it.

routes/web.php

use App\Http\Controllers\PostController;

Route::get('posts', [PostController::class, 'index'])->name('posts.index');

use App\Http\Controllers\PostController;

Route::get('posts', [PostController::class, 'index'])->name('posts.index');

Create Controller by Artisan Command

Now, lets create a controller named PostController using command given below –

php artisan make:controller PostController

1	php artisan make:controller PostController

Once the above command executed, it will create a controller file PostController.php in app/Http/Controllers/ directory. Open the PostController.php file and put the following code in it.

app/Http/Controllers/PostController.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\User;
use App\Models\Post;
use DataTables;
class PostController extends Controller
{
    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Contracts\Support\Renderable
     */
    public function index(Request $request)
    {
        if ($request->ajax()) {
            $model = Post::with('users');
                return DataTables::eloquent($model)
                ->addColumn('users', function (Post $post) {
                    return $post->users->name;
                })
                ->toJson();
        }
        return view('users');
    }
}

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Models\User;

use App\Models\Post;

use DataTables;

class PostController extends Controller

{

/**

* Show the application dashboard.

* @return \Illuminate\Contracts\Support\Renderable

public function index(Request $request)

{

if ($request->ajax()) {

$model = Post::with('users');

return DataTables::eloquent($model)

->addColumn('users', function (Post $post) {

return $post->users->name;

})

->toJson();

}

return view('users');

}

Create Blade File

In this step we will create blade file. Go to resources/views directory and create new file users.blade.php. After that, put the following html and javascript code into your blade view file:

<!DOCTYPE html>
<html>
<head>
<title>Laravel 9 Datatables with Relationship Tutorial</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
<link href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/1.10.19/css/dataTables.bootstrap4.min.css" rel="stylesheet">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.js"></script>  
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.0/jquery.validate.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap4.min.js"></script>
<style type="text/css">
.paginate_button{
padding: 0px !important;
}
</style>
</head>
<body>
<div class="container" style="margin-top: 100px;margin-bottom: 100px; ">
<div class="row justify-content-center">
<div class="col-md-8">
<div class="card">
<div class="card-header bg-info text-white">Laravel Datatables with Relationship Tutorial</div>
<div class="card-body">
@if (session('status'))
<div class="alert alert-success" role="alert">
{{ session('status') }}
</div>
@endif
<table class="table table-bordered data-table">
<thead>
<tr>
<th>No</th>
<th>Title</th>
<th>Auther</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
<script type="text/javascript">
$(document).ready(function() {
$('.data-table').DataTable({
processing: true,
serverSide: true,
ajax: "{{ route('posts.index') }}",
columns: [
{data: 'id', name: 'id'},
{data: 'title', name: 'title'},
{data: 'users', name: 'users.name'},
]
});
});
</script>
</body>
</html>

<!DOCTYPE html>

<html>

<head>

<title>Laravel 9 Datatables with Relationship Tutorial</title>

.paginate_button{

padding: 0px !important;

}

</style>

</head>

<body>

<div class="card-header bg-info text-white">Laravel Datatables with Relationship Tutorial</div>

@if (session('status'))

</div>

@endif

<thead>

<tr>

<th>Title</th>

<th>Auther</th>

</tr>

</thead>

<tbody>

</tbody>

</table>

</div>

$(document).ready(function() {

$('.data-table').DataTable({

processing: true,

serverSide: true,

ajax: "{{ route('posts.index') }}",

columns: [

{data: 'id', name: 'id'},

{data: 'title', name: 'title'},

{data: 'users', name: 'users.name'},

]

});

</script>

</body>

</html>

Run Development Server

Now we are ready to run our example so lets start the development server using following artisan command –

php artisan serve

1	php artisan serve

Now, open the following URL in browser to see the output –

http://127.0.0.1:8000/posts

1	http://127.0.0.1:8000/posts

AngularJS

CodeIgniter

Laravel

Node Js

Wordpress

More Blog

Learn

ABOUT PAGES

Laravel 9 Datatables Column Relationship Search Tutorial