0 Shares 10393 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
4488 views
CDN
4488 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
9258 views
CDN
9258 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…

How CDN supports the Travel and Hospitality Business
CDN
227 views
CDN
227 views

How CDN supports the Travel and Hospitality Business

Tushar Sonal - July 28, 2021

Travel and Hospitality are making big waves, with people thronging to popular tourist spots in droves as the Covid19 recedes. With this shift towards the online sphere,…

A Story About The Battle of Logging
CDN
270 views
CDN
270 views

A Story About The Battle of Logging

Elif Ak - July 12, 2021

Networking and computing systems generate a deluge of logs in seconds. Analyzing these voluminous logs without human interference plays a crucial role in autonomous management. Just as…

AVIF: All You Need To Know About This New Image File Format
New Features
466 views
New Features
466 views

AVIF: All You Need To Know About This New Image File Format

Nilgün Ünsal - June 25, 2021

AVIF: The New Image Format Today, everyone has thousands of pictures on their phones and computers. In e-commerce or fashion sites, this number reaches up to millions.…

Most from this category