Tuesday 23 January 2018

Datatables Server Side Processing in Laravel



This post covered Datatables Server Side Processing using Ajax in Laravel Framework. How to Implementing JQuery Datatables in Laravel with Ajax Server Side processing. In this post we have describe this things step by step. Because Jquery Datatables will add some advance features like quick search of table data, it will make automatic pagination without write of code, table column ordering, sorting of table column and many more. And if this features we have use laravel application then it will add robust feature to your HTML table data. For Implement Datatables in Laravel we will use yajra/laravel-datatables-oracle package. By using this package we can implement Jquery Datatables plugin in Larvel Application.
First we want to download Laravel clone application, so we have to go to command prompt in which composer must be installed and we have to write following command.


composer create-project --prefer-dist laravel/laravel student_crud1 "5.4.*"


This command will download Laravel Clone application and installed in specified directory. After download Laravel first we want to make database connection, so we have to open .env file and in that file we have to define database configuration details.


DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=laravel_testing
DB_USERNAME=root
DB_PASSWORD=


After this we have to define mysql database configuration details in config/database.php.


'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'laravel_testing'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],


After making of database connection now we want to create model for create student table and for database operation. For this we have to write following command in cmd.


php artisan make:model Student -m





This command will create Student Model in app/Student.php folder and it will also create database migration file in database/migrations folder. In migration file we have add some code for create table in Mysql database.


<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateStudentsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('students', function (Blueprint $table) {
            $table->increments('id');
            $table->string('first_name');
            $table->string('last_name');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('students');
    }
}


Now we want to migrate this code into Mysql database, so we have go to command prompt and write following command, this command will migrate student table in Mysql database and create student table in laravel_testing database.


php artisan migrate


Now We want to make one controller for manage data request and layout. For create new controller we should go to cmd prompt and write following command.


php artisan make:controller AjaxdataController


This command will create AjaxdataController.php file under app/Http/Controllers folder. After this we have create ajaxdata.blade.php view file for display output in browser. And in that file we have create one table in which we want to load data from student table by using datatables.


<!DOCTYPE html>
<html>
<head>
    <title>Datatables Server Side Processing in Laravel</title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
    <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>       
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
</head>
<body>

<div class="container">
    <br />
    <h3 align="center">Datatables Server Side Processing in Laravel</h3>
    <br />
    <table id="student_table" class="table table-bordered" style="width:100%">
        <thead>
            <tr>
                <th>First Name</th>
                <th>Last Name</th>
            </tr>
        </thead>
    </table>
</div>

</body>
</html>


Now we want to load this view file in browser, so for this we have to go to AjaxdataController.php file and under this we have to write following code.


<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

class AjaxdataController extends Controller
{
    function index()
    {
     return view('student.ajaxdata');
     //http://127.0.0:8000/ajaxdata
    }
}


Now we want set route for this method, so we have to go to routes/web.php file and write following code for set route for index method.


Route::get('ajaxdata', 'AjaxdataController@index')->name('ajaxdata');


Now we want to download yajra/laravel-datatables-oracle package for use datatables in Laravel. For this we have to go to command prompt and write following command.


composer require yajra/laravel-datatables-oracle:"~7.0"


This command will download yajra/laravel-datatables-oracle package which we can found under vendor folder. Now we want to add service provider and alias details under config/app.php file.


'providers' => [
        ............
        Yajra\Datatables\DatatablesServiceProvider::class,

    ],



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

    ],


After adding service provider and alias details, now we want to publish following package under this application, so we can use this package under Laravel. So for this we have to go to command prompt and write following command.


php artisan vendor:publish --provider=Yajra\Datatables\DataTablesServiceProvider


After writing this command, now we can use this package in Laravel Application. For use this package first we want to add this package class under AjaxdataController.php file. Then after we have make one method for get data from student table and by using this package class method we can convert into format as per require in JQuery Datatables plugin which we can see below.


<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Student;
use Datatables;

class AjaxdataController extends Controller
{
    function index()
    {
     return view('student.ajaxdata');
     //http://127.0.0:8000/ajaxdata
    }

    function getdata()
    {
     $students = Student::select('first_name', 'last_name');
     return Datatables::of($students)->make(true);
    }
}


After this we want to set route for this new method, so we have go to route/web.php file and add following code.


Route::get('ajaxdata/getdata', 'AjaxdataController@getdata')->name('ajaxdata.getdata');


Lastly we want to send ajax request from ajaxdata.blade.php file to this getdata() method of AjaxdataController.php file. Below we can ajax request code.


<!DOCTYPE html>
<html>
<head>
    <title>Datatables Server Side Processing in Laravel</title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
    <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>       
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
</head>
<body>

<div class="container">
    <br />
    <h3 align="center">Datatables Server Side Processing in Laravel</h3>
    <br />
    <table id="student_table" class="table table-bordered" style="width:100%">
        <thead>
            <tr>
                <th>First Name</th>
                <th>Last Name</th>
            </tr>
        </thead>
    </table>
</div>

<script type="text/javascript">
$(document).ready(function() {
     $('#student_table').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "{{ route('ajaxdata.getdata') }}",
        "columns":[
            { "data": "first_name" },
            { "data": "last_name" }
        ]
     });
});
</script>
</body>
</html>


Lastly, we want to show output in browser, so we have to go to command prompt and write this command for run laravel application.


php artisan serve


This command will start Laravel Development Server and give you one url like . We can run laravel application by copy this url in browser and press enter, suppose we want to run tutorial which we have discuss in this post, so at that time we have write http://127.0.0:8000/ajaxdata. This url will directly load AjaxdataController.php file index() method in browser.

11 comments:

  1. nice tutorial clearly explained ...

    ReplyDelete
  2. Do you got eshop for laravel?

    ReplyDelete
  3. simple and clear tutorial.but how can i use other futures like Copy, CSV, Excel, PDF, and Print.

    ReplyDelete
  4. im getting this warning or error DataTables warning: table id=student_table - Ajax error. For more

    ReplyDelete
  5. hi im only getting json response instead of table.

    ReplyDelete
  6. if the first load user table , all of users get ?
    and just show 10 number of all of them?

    ReplyDelete
  7. Nice article. But what if we want to build datatable with only datatable libraries (through CDN) without any 3rd party package like yajra or other package?

    ReplyDelete
  8. thanks sir how to Angular js CRUD operations application using laraval datatable

    ReplyDelete
  9. it is showing me DataTables warning: table id=student_table - Ajax error.

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete