Laravel 7 Datatables with Relationship Example

Laravel dataTables with relationship example tutorial. Here you will learn how to display and filter column in yajra dataTables with relationship.

Suppose you have two tables first one is posts and the second one is users and you creates relationship each table. Then you want to display and filter posts title and who write these posts (author name). At that time you need to use laravel yajra dataTables columns with a relationship.

As well as, this tutorial demonstrates to you how to use a filter on dataTables columns with relationships.

Laravel DataTables with Relationship Example

Follow the following steps and use relationship on laravel yajra dataTables:

Step 1: Download Laravel Fresh App
Step 2: Setup Database Configuration
Step 3: Install Yajra DataTable
Step 4: Create Migration and Modal
Step 5: Create route
Step 6: Generate Controller by Command
Step 7: Create Blade File
Step 8: Run Development Server

Step 1: Download Laravel Fresh App

First of all, open your terminal and use the following command to download or install laravel fresh setup:

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

Step 2: Setup Database Configuration

After that, open “.env” file and update the database name, username and password in the 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

Step 3: Install Yajra DataTable

Install yajra datatable via composer package, so open your terminal and run the following command:

composer require yajra/laravel-datatables-oracle

After that, you need to set providers and alias.

config/app.php

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

Step 4: Create Migration and Modal

In this blog, We will create post table migration and create Post Modal using bellow command:

php artisan nake:modal Post -m

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

Now run the following command

php artisan migrate

After you have to put bellow code in your Post model file for create posts table.

Next open app/Post.php model and create relationship in this model as follow:

app/Post.php

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

Step 5: Create route

Next, open your “routes/web.php” file and add the following route:

Route::get('posts','PostController@index')->name('posts.index');

Step 6: Generate Controller by Command

Next step, open your terminal and run the following command:

php artisan make:controller PostController

This command will create PostController by the artisan command.

Next, go to app/http/controller/PostController.php. and update the following methods into your controller file:

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\User;
use App\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');
}
}

Step 7: Create Blade File

Next step, Go to resources/views and create new file users.blade.php.

After that, update the following html and javascript code into your blade view file:

<!DOCTYPE html>
<html>
<head>
<title>Laravel Datatables with Relationship Tutorial - Tutsmake.com</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 - Tutsmake.com</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>

Step 8: Run Development Server

In this step, use the following php artisan serve command to start your server locally:

php artisan serve

Now we are ready to run our example so run bellow command to quick run.

http://127.0.0.1:8000/posts

Conclusion

In this tutorial, you have learned how to use relationship on dataTables and as well as how to datatables filter column on a relationship.