0 Shares 1036 Views

Laravel – Yajra Datatable Operations

In this article, I would like to talk about the yajra/laravel” datatables library that provides us a lot of conveniences when creating a table. With this library, we get rid of complex database queries, paging operations, and the hassle of searching through data.

First of all, if you have not done it before and you are complaining about the slow work of the composer, let’s start by switching to the composer 2.0 version. Let’s run the code below from your terminal screen.

#/bin/bash
_> composer self-update

Let’s continue our project by adding the library. For this project, I will be using Laravel 8.x version. But the library has been fully supported since Laravel 4.2. Let’s start!

composer require yajra/laravel-datatables:^1.5

We are adding our library to our project.
* Note: You can use Html, button, etc. on the project. I installed all the packages as I will explain in the add-ons. The PHP ext-zip extension is required for this setup. You can use the kernel version if you wish.

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

Let’s make our service link.

# config/app.php'providers' => [
    // ...
     Yajra\DataTables\DataTablesServiceProvider::class,
     Yajra\DataTables\HtmlServiceProvider::class,
     Yajra\DataTables\ButtonsServiceProvider::class,
     Yajra\DataTables\EditorServiceProvider::class,],

To transfer the settings and files, we need to publish.

php artisan vendor:publish --tag=datatables
php artisan vendor:publish --tag=datatables-buttons

 

Now we can start using our library.

Before I start using the library, I’ll be using the “factory design pattern” to manage dependencies more easily. You can have more detailed information about the subject by researching design patterns beforehand. First, let’s create our folders. It should look like this

|_ App
|__ Example
|--- Business
|---- Abstraction
|---- Concrete
|--- DataAccess
|---- Abstraction
|---- Concrete

Instead of Example, you can make a name by yourself. As an example, we will make a Customers list. First of all, we create a model, migration, factory, and seeder for demo content.

php artisan make:model Customers -mcsf-m : migration
-c : controller
-s : seeder
-f : factory

Migration file

Schema::create('customers', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email');
    $table->string('phone');
    $table->string('slug')->unique();
    $table->text('address');
    $table->softDeletes();
    $table->timestamps();
});

Seeder file

# database/seeders/CustomersSeeder.php
 Customers::factory()->count(500 )->create();

# database/seeders/DatabaseSeeder.php
$this->call([
    CustomersSeeder::class
]);

Factory dosyası

$name = $this->faker->name;
return [
    'name'  => $name,
    'email' => $this->faker->unique()->safeEmail,
    'phone' => $this->faker->phoneNumber,
    'slug' => Str::slug( $name ),
    'address' =>  $this->faker->address
];

and then after entering database information on .env

php artisan migrate --seed || php artisan migrate:fresh --seed

 

By writing, we will add 500 demo customers to our database. Now we can do all the library related operations.

First, let’s create an ICustomersService file under “Business / Abstraction”, then create a file named CustomersManager under “Business / Concrete”.

 

Let’s edit the ICustomerService file as follows.

namespace App\Example\Business\Abstraction;

interface ICustomersService
{
    public function listForDatatable();
}

Let’s organize the inside of the CustomerManager file as follows.

<?php

namespace App\Example\Business\Concrete;

use App\Example\Business\Abstraction\ICustomersService;

class CustomersManager implements ICustomersService
{

    public function listForDatatable()
    {
        // TODO: Implement listForDatatable() method.
    }
}

Next, let’s create two files named ICustomersDataService and CustomersDataManager in the Data layer. Let’s arrange them as follows.

# CustomersDataService 

<?php

namespace App\Example\DataAccess\Abstraction;

interface ICustomersDataService
{
    public function listForDatatable();
}

# CustomersDataManager<?php
namespace App\Example\DataAccess\Concrete;

use App\Example\DataAccess\Abstraction\ICustomersDataService;
class CustomersDataManager implements ICustomersDataService
{
    public function listForDatatable()
    {
        // TODO: Implement listForDatatable() method.
    }
}

 

Now let’s determine the route in which we list the customers.

# routes/web.php
Route::group(['prefix'=>'musteri'], static function(){
   Route::get('liste', 'CustomersController@list')
             ->name('get.customers.list');
});

We can now start adding on Yajra.

Usage: php artisan datatables:make Customers
Output: App\DataTables\CustomersDataTable

First of all, our file should look like this.

<?php

namespace App\DataTables;

use App\Models\Customer;
use Yajra\DataTables\Html\Button;
use Yajra\DataTables\Html\Column;
use Yajra\DataTables\Html\Editor\Editor;
use Yajra\DataTables\Html\Editor\Fields;
use Yajra\DataTables\Services\DataTable;

class CustomersDataTable extends DataTable
{
    /**
     * Build DataTable class.
     *
     * @param mixed $query Results from query() method.
     * @return \Yajra\DataTables\DataTableAbstract
     */
    public function dataTable($query)
    {
        return datatables()
            ->eloquent($query)
            ->addColumn('action', 'customers.action');
    }

    /**
     * Get query source of dataTable.
     *
     * @param \App\Models\Customer $model
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function query(Customer $model)
    {
        return $model->newQuery();
    }

    /**
     * Optional method if you want to use html builder.
     *
     * @return \Yajra\DataTables\Html\Builder
     */
    public function html()
    {
        return $this->builder()
                    ->setTableId('customers-table')
                    ->columns($this->getColumns())
                    ->minifiedAjax()
                    ->dom('Bfrtip')
                    ->orderBy(1)
                    ->buttons(
                        Button::make('create'),
                        Button::make('export'),
                        Button::make('print'),
                        Button::make('reset'),
                        Button::make('reload')
                    );
    }

    /**
     * Get columns.
     *
     * @return array
     */
    protected function getColumns()
    {
        return [
            Column::computed('action')
                  ->exportable(false)
                  ->printable(false)
                  ->width(60)
                  ->addClass('text-center'),
            Column::make('id'),
            Column::make('add your columns'),
            Column::make('created_at'),
            Column::make('updated_at'),
        ];
    }

    /**
     * Get filename for export.
     *
     * @return string
     */
    protected function filename()
    {
        return 'Customers_' . date('YmdHis');
    }
}

I will summarize the connection with Ajax as I will do Server-Side Rendering. First of all, we will have to make some adjustments. We organize the file as follows.

 

<?php

namespace App\DataTables;

use App\Models\Customers;
use Yajra\DataTables\DataTableAbstract;
use Yajra\DataTables\Html\Builder;
use Yajra\DataTables\Html\Button;
use Yajra\DataTables\Html\Column;
use Yajra\DataTables\Services\DataTable;

class CustomersDataTable extends DataTable
{
    /**
     * Build DataTable class.
     *
     * @param mixed $query Results from query() method.
     * @return \Illuminate\Http\JsonResponse
     */
    public function dataTable($query)
    {
        return datatables()
            ->eloquent($query)
            ->addColumn('action', static function (Customers $customer) {
                return "<a href='/customers/detail/{$customer->slug}' class='btn btn-success btn-sm w-100'>Detail</a>";
            })
            ->toJson();
    }

    /**
     * Get query source of dataTable.
     *
     * @param Customers $model
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function query(Customers $model)
    {
        return $model->newQuery();
    }

    /**
     * Optional method if you want to use html builder.
     *
     * @return Builder
     */
    public function html()
    {
        $columns = $this->getColumns();
        return $this->builder()
            ->setTableId('customers-table')
            ->columns($columns)
            ->language( "//cdn.datatables.net/plug-ins/1.10.21/i18n/English.json" )
            ->minifiedAjax()
            ->orderBy(0)
            ->buttons(
                Button::make('reset')
            )
            ->ajax([
                'url' => '/customers/list-datatable',
                "type" => 'POST',
            ]);
    }

    /**
     * Get columns.
     *
     * @return array
     */
    protected function getColumns()
    {
        return [
            Column::make('id')->title('ID'),
            Column::make('name')->title('Name'),
            Column::make('email')->title('E-Mail'),
            Column::make('phone')->title('Phone Number'),
            Column::make('address')->title('Address'),
            Column::make('action')->title('#'),

        ];
    }

    /**
     * Get filename for export.
     *
     * @return string
     */
    protected function filename()
    {
        return 'Customers_' . date('YmdHis');
    }
}

 

Let’s explain its content.

<code>

public function dataTable($query)
{
    return datatables()
        ->eloquent($query)
        ->addColumn('action', 
                     static function (Customers $customer) {
            return "<a href='/customers/detail/{$customer->slug}' class='btn btn-success btn-sm w-100'>Detail</a>";
        })
        ->toJson();
}

</code>

If we want to add a column in addition to our existing data, we add this section with addColumn. The first parameter is naming and the second parameter refers to the content of the data we will add to this column.

public function html()
{
    return $this->builder()
        ->setTableId('customers-table')
        ->columns( $this->getColumns() )
        ->language( "//cdn.datatables.net/plug-ins/1.10.21/i18n/English.json" )
        ->minifiedAjax()
        ->orderBy(0)
        ->buttons(
            Button::make('reset')
        )
        ->ajax([
            'url' => '/customers/list-datatable',
            "type" => 'POST',
            "headers" => [
                'X-CSRF-TOKEN'=> csrf_token()
            ]
        ]);
}

This is the part that forms the template of our table.

 

setTableId : Used to add an id in addition to our table.
columns    : Determines the columns of our table.
language   : Refers to the language of the table. (Default: English)
minifiedAjax :Removes unnecessary queries.
orderBy    : Refers to the sort type of the specified column.
buttons    :  Buttons to be added to the table (print, export, reset)
ajax.url   :  The URL to request
ajax.type  : Request type
ajax.header.x-csrf-token : The token required for our post request

*Also, you can pass the parameters valid for Ajax.

 

protected function getColumns()
{
    return [
        Column::make('id')->title('ID'),
        Column::make('name')->title('Name'),
        Column::make('email')->title('E-Mail'),
        Column::make('phone')->title('Phone Number'),
        Column::make('address')->title('Address'),
        Column::make('action')->title('#'),

    ];
}/**
 * Make :  It refers to the name of our column in the database
 * If you use relationship, you need to edit additionally.
 *  For example: Column :: make ('authorized_person.0.full_name')
 * ->name('authorized_person.full_name')
 * ->title('Authorized Person')
 * If your relationship data is Array, you must specify "name".
 * Otherwise, you will get a Database query error while searching.
 * 
 * title: The name we want it to be written at the Top of the Table.
 */

Now let’s make arrangements in Controller. First of all, let’s make our View screen.

# CustomersController 
...
use App\DataTables\CustomersDataTable;
...
public function list(CustomersDataTable $dataTable)
{
    return $dataTable->render('app.customer.list');
}

What you should pay attention to here is that we are calling the render function over the CustomerDataTable class that we add as a dependency instead of the return view () we normally use. In this way, the public/app/customer / list.blade.php said that should run. I add only the parts related to datatable into the blade. You arrange it according to your theme.

 

@extends('layouts.app')

@section('title', 'Müşteri Listele')

@section('content')
    <section class="section">
        <div class="section-header">
            <h1>@yield('title')</h1>
        </div>
        <div class="section-body">
            <div class="row">
                <div class="col-12">
                    <div class="card">
                        <div class="card-body">
                            <div class="table-responsive">
                                {!! $dataTable->table() !!}
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </section>
@endsection@push('stylesheet')
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.22/css/dataTables.bootstrap4.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.0.3/css/buttons.dataTables.min.css">
@endpush
@push('javascript')
    <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.22/js/dataTables.bootstrap4.min.js"></script>
    <script src="https:///cdn.datatables.net/select/1.3.0/js/select.bootstrap4.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.0.3/js/dataTables.buttons.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.5.6/js/dataTables.buttons.min.js"></script>
    <script src="/vendor/datatables/buttons.server-side.js"></script>
    {!! $dataTable->scripts() !!}
@endpush{!! $dataTable->table() !!} // The part that makes up our table
{!! $dataTable->scripts() !!}  // Required Js files

When we visit /customers/list via the browser, we will get an error like this.

Laravel Yajra - DataTables route error

Laravel – Yajra Datatables Router Error

The reason for this error is that it cannot access the route we set in ajax. Now we will edit that part. We come to the part where our job is simplest, we organize the library’s routes/web.php as follows.

..... 
public function listForDatatable(
                 Request $request,
                CustomersDataTable $dataTable,
                ICustomersService $service
)
{
             if ($request->ajax()) {
                  return $dataTable->dataTable( 
                     $service->listForDatatable() 
                  );
             }
}
....

 

Now we are making arrangements in the business layer.

namespace App\Example\Business\Concrete;

use App\Example\Business\Abstraction\ICustomersService;
use App\Example\DataAccess\Abstraction\ICustomersDataService;

class CustomersManager implements ICustomersService
{

    protected $dataService;
    public function __construct(ICustomersDataService $dataService)
    {
        $this->dataService = $dataService;
    }

    public function listForDatatable()
    {
        return $this->dataService->listForDatatable();
    }
}

We added the Data Layer to our system on the constructor method and moved it to a variable.
Thus, we will be able to access from within the function we want in the Business layer.

Next is to edit the Data layer.

namespace App\Example\DataAccess\Concrete;


use App\DataTables\CustomersDataTable;
use App\Example\DataAccess\Abstraction\ICustomersDataService;
use App\Models\Customers;

class CustomersDataManager implements ICustomersDataService
{
    protected $model;
    protected $dataTable;
    public function __construct(CustomersDataTable $dataTable)
    {
        $this->model = new Customers();
        $this->dataTable = $dataTable;
    }

    public function listForDatatable()
    {
        return $this->dataTable->query( $this->model );
    }
}
/**
  $this->model = new Customers (); We are creating a new model instance. Also, we add our DataTable class here as a dependency and send the instance into the query class.
*/

 

</code>

As you can see, adding a limit to the query for pagination and transmitting the page number completely saves us from writing expressions such as were like for the searched word, we just send it to our model library, it takes careof the rest 🙂 Let’s refresh our page and look at the result.

  • Target [App\\Example\\Business\\Abstraction\\ICustomersService] is not instantiable.

we got the error. The reason for this is that we did not specify which classes the interfaces we use in the Work and Data layers are dependent on. In that

php artisan make:provider Example

Then we define the provider to the system.

#config/app.php'providers' => [
...
    App\Providers\Example::class
];

and then we make connections to the provider part. For details, you can review the provider’s section from Laravel documentation.

# ExampleProvider public function register()
{

    $this->app->singleton('App\Example\Business\Abstraction\ICustomersService','App\Example\Business\Concrete\CustomersManager');
    $this->app->singleton('App\Example\DataAccess\Abstraction\ICustomersDataService', 'App\Example\DataAccess\Concrete\CustomersDataManager');

}

In this way, we delete the automatically created definitions and cache after they are linked together.

composer dump-autoload
php artisan optimize:clear

 

and Conclusion

Laravel Example Yajra DataTables Medianova

Laravel – Yajra Datatables example App

Laravel example filter Yajra DataTables medianova

Laravel – Yajra Datatables example App

Good and Error Free Coding for Everyone 🙂

You can access the codes of the project on Github.
https://github.com/tolgakarabulut/laravel-yajra-datatables-example

 

You may be interested

The Ultimate CDN (Content Delivery Network) Guide
CDN
3012 views
CDN
3012 views

The Ultimate CDN (Content Delivery Network) Guide

Nadia Benslimane - August 3, 2020

The Ultimate CDN Guide- Everything About Content Delivery Network You probably know what CDN (Content Delivery Network) stands for. You may also be aware of its full definition,…

The Essential CDN Glossary
CDN
8479 views
CDN
8479 views

The Essential CDN Glossary

Nadia Benslimane - November 21, 2019

Why Have We Decided To Create a CDN Glossary? Whether you are new to the world of CDN, or have been involved in it for years, there…

What Is The Difference Between Hosting And CDN?
CDN
251 views
CDN
251 views

What Is The Difference Between Hosting And CDN?

Tushar Sonal - January 12, 2021

The Difference Between Hosting and Content Delivery Network The traditional web hosting architecture has been crucial to the growth and success of the worldwide web over the…

Software Quality Criteria And How We Can Digitize Them
Software Learning Center
260 views
Software Learning Center
260 views

Software Quality Criteria And How We Can Digitize Them

Emir Turna - January 4, 2021

What Are Software Quality Criteria And How Can We Digitize Them? The software world is growing more and more every day, and it is becoming a sector…

The Video-First Era Has Started: Why Video Streaming Is Here To Stay
CDN
568 views
CDN
568 views

The Video-First Era Has Started: Why Video Streaming Is Here To Stay

Tushar Sonal - December 22, 2020

Video Streaming Is The Present and The Future Of Our Generation The Covid19 crisis has brought video platforms into the front and center of global attention. The…

Most from this category

The Ultimate CDN (Content Delivery Network) Guide
CDN
3012 views
3012 views

The Ultimate CDN (Content Delivery Network) Guide

Nadia Benslimane - August 3, 2020
The Essential CDN Glossary
CDN
8479 views
8479 views

The Essential CDN Glossary

Nadia Benslimane - November 21, 2019
Training? Yes please! –
HR
501 views
501 views

Training? Yes please! –

Nurdan Altunköse - December 7, 2020
Support: A Building Block of Medianova CDN
CDN
393 views
393 views

Support: A Building Block of Medianova CDN

Tushar Sonal - December 4, 2020