DEV Community: WAIYL KARIM

Datatables: Laravel Jetsteam Inertia

WAIYL KARIM — Mon, 26 Jul 2021 19:08:31 +0000

When I first was trying to set my project using Laravel Jetstream with Inertia and VueJs I spent so much time trying to figure out how to implement jQuery Datatables in my project. In this article I'm going to show you how to do just that step by step.

Set up your Laravel Project

composer create-project laravel/laravel my-crm

or via laravel command if you have that installed

laravel new my-crm

Next let's install Jetstream

composer require laravel/jetstream

Install Inertia with Vuejs

php artisan jetstream:install inertia

Install & build NPM dependencies

npm install

npm dev

Migrate your database

php artisan migrate

Install the amazing datatables laravel package by Yajra

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

Install Datatables

npm install datatables.net-dt

npm i jquery

Implement Datatables

On your web.php routes file, let's add necessary routes to load our resources

Route::resource('users', UsersController::class);

Route::name('users.datatables')->get('users', [UsersController::class, 'datatables']);

Now, create a UsersController and add the following

<?php

namespace App\Http\Controllers;

use App\Models\User;
use Illuminate\Http\Request;
use Inertia\Inertia;

class UsersController extends Controller
{
    /**
     * Datatable Columns Array
     *
     * @var Array
     */
    private $datatableColumns;

    /**
     * Datatable Headers Array
     *
     * @var Array
     */
    private $datatableHeaders;

    /**
     * Datatables Data URL
     *
     * @var String
     */
    private $datatableUrl;

    /**
     * Controller constructor
     *
     * @return void
     */
    public function __construct() {     
        $this->datatableHeaders = [
            'ID', 
            'Name', 
            'Email'
        ];

        $this->datatableColumns = [
            ['data' => 'id'],
            ['data' => 'name'],
            ['data' => 'email']
        ];

        $this->datatableUrl = route('users.datatables');
    }

    /**
     * Get datatables JSON Response
     *
     * @return \Illuminate\Http\Response
     */
    public function datatables() {
        $datatables = datatables()
            ->of(User::query())
            ->addColumn('id', fn($user) => $user->id)
            ->addColumn('name', fn($user) => $user->name)
            ->addColumn('email', fn($user) => $user->email)
            ->toArray();

        return response()->json($datatables);
    }

    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {
        return Inertia::render('Users/Index')
            ->with('datatableUrl', $this->datatableUrl)
            ->with('datatableColumns', $this->datatableColumns)
            ->with('datatableHeaders', $this->datatableHeaders);
    }

Now that we've got that out of the way, let's build our Vuejs components.

The first component we will be creating is the Users/Index.vue component. Inside your resources/js folder create a new folder called Users and inside this folder create a .vue file called Index.vue

// resources/js/Pages/Users/Index.vue
<template>
        <div class="bg-white rounded-lg shadow-md mx-4 p-6">
                 <!-- we will display our datatable here -->
        </div>

</template>

<script>
    export default {

    }
</script>

We will get back and change this file later.

Now let's create our Datatable component.

Again inside resources/js add a new folder called Shared and inside this folder create file called Datatable.vue

// resources/js/Shared/Datatable.vue

<template>
    <div>
        <table class="table table-hover table-bordered" id="dt-users">
            <thead class="border-1 rounded-sm text-white border-gray-800 bg-gray-900">
                <tr>
                    <th v-for="header in headers" :key="header.id">{{ header }}</th>
                </tr>
            </thead>
        </table>
    </div>
</template>

<script>
    import 'jquery/dist/jquery.min.js';
    import "datatables.net-dt/js/dataTables.dataTables"
    import $ from 'jquery'; 

    export default {
        props: ['url', 'columns', 'headers'],
        mounted(){
            let datatable = $('#dt-users').on('processing.dt', function(e, settings, processing) {
                    if (processing) {
                        $('table').addClass('opacity-25');
                    }else {
                        $('table').removeClass('opacity-25');
                    }
                }).DataTable({
                ajax: {
                    url: this.url,
                },
                serverSide: true,
                processing: true,
                paging: true,
                columns: this.columns,
            });

        },
    }
</script>

Go back to resources/js/Pages/Users/Index.vue and import this file as followed:

// final version of resources/js/Users/Index.vue

<template>
    <div class="bg-white rounded-lg shadow-md mx-4 p-6">
        <Datatable 
            :url="datatableUrl" 
            :headers="datatableHeaders"
            :columns="datatableColumns"
        />
    </div>
</template>

<script>
    import Datatable from '@/Shared/Datatable'

    export default {
        components: {
            Datatable,
        },
        props: ['datatableUrl', 'datatableColumns', 'datatableHeaders'],
    }
</script>

And that's it! Leave questions in the comment section.

Happy coding.

Cover image credit pikisuperstar

Multi-tenancy with Laravel (dynamic databases)

WAIYL KARIM — Sun, 18 Jul 2021 18:19:19 +0000

Like many developers before me when it comes to a web project, PHP with Laravel is my go to technology for it is not only making my life as a developer a lot easier but fun as well.

However when my team and I have been working on a project for one of our clients that requires dynamic database creation we found ourselves between doing it the Laravel way or doing our way.

I will take the chance in this flash article to explain the problem with doing it the traditional Laravel way and doing it your own way and I will be expecting the experts amongst you to share in the comments section other ways they would use to tackle this challenge.

Say we have a web-based app whose master will be required to create a new instance of the app for every client. All of these instances are being hosted on the same server which means every instance should have its own database hence multi-tenant.

If you wish to do it the Laravel way you need to manually add something like this for every database in config/database.php:

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => 'database1',
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

Then you need to store the name of the database somewhere on your main database to call every time this client requests its instance via ModelName::connection($databaseName).

This process will result in having one big giant config/database.php if you have for example more than 100 clients. And it is kind of tedious to repeat every time you have a new client.

You could make a script that automatically adds the client's database required config in config/database.php but this won't make your file less big.

The solution we came out with is simple and straightforward. All you need to do is create a table on your main database where you will define every client and their database name. Create a script either using PHP and a web interface or use a custom Artisan command.

The custom Artisan command will store the client in the main database (which is the only database defined on config/database.php), create the client database and finally migrate all migrations. All of that using only one simple artisan command line. Something like php artisan client:make Client1. The name of the database will be Client1 in this case.

Then instead of calling ModelName::connection($clientDatabaseName), you create a Trait where you will define the following:

$clientDatabaseName = MainDatabase::where('client_name', $request->client_name)->value('database_name');

config(['database.connections.mysql.database' => $clientDatabaseName]);

\DB::purge('mysql');

\DB::reconnect('mysql');

To use this Trait either you call it on your models or you use it inside AppServiceProvider.

Using this simple approach you won't need to store everything inside config/database.php and you won't have to use connection($db) every time you need to instantiate a Model. Only with a simple artisan command line, you take care of creating the database and its migrations and seeders if you have any.

I hope this flash article helps, I would like to know in the comments how would you solve this.

If you'd like me to create a full series of how to implement this approach, leave that in the comments section.

Happy coding!

Cover image credit: lavarmsg

Laravel: Softdelete Eloquent Relationships onDelete Cascade

WAIYL KARIM — Fri, 10 Jul 2020 01:07:15 +0000

Eloquent is one of many Laravel features that you just can't deny how cool and useful they are. However, sometimes even the very best things have their flaws. And one of Eloquent's is not being able to autodelete related objects through SoftDelete when a model has a relationship to another with onDelete('cascade').

For this article, I am supposing that you are using Laravel 5. So let's say you have your default users table related to the User model and you have another table called invoices which is related to the Invoice model. User and Invoice are linked through an Eloquent relationship as followed:

// app/User.php
<?php

namespace App;

class User extends Authenticatable {

    // ...

    public function invoices() {
        return $this->hasMany(Invoice::class);
    }
}

I think the code above does not need any explanations. It's a simple Eloquent relationship linking User and Invoice.

// app/User.php

<?php

namespace App;

class User extends Model {

    // ...

    /**
     * Holds the methods' names of Eloquent Relations 
     * to fall on delete cascade or on restoring
     * 
     * @var array
     */
    protected static $relations_to_cascade = ['invoices']; 

    protected static function boot()
    {
        parent::boot();

        static::deleting(function($resource) {
            foreach (static::$relations_to_cascade as $relation) {
                foreach ($resource->{$relation}()->get() as $item) {
                    $item->delete();
                }
            }
        });

        static::restoring(function($resource) {
            foreach (static::$relations_to_cascade as $relation) {
                foreach ($resource->{$relation}()->get() as $item) {
                    $item->withTrashed()->restore();
                }
            }
        });
    }
}

Let's now break down this code:

protected static $relations_to_cascade = ['invoices']; in this array we store the methods' names or relationships we wish to delete (or restore) when a SoftDelete occurs. If we have more than one relationship, we should push them to the array. For instance:

protected static $relations_to_cascade = ['invoices', 'roles']; Note that we must have a roles() relationship declared in our Invoice model as well in this case or our app won't recognize the roles method later on the code.

Now that we have defined the Eloquent relationships we want deleted/restored we move on to actually deleting them and/or restoring them.

        static::deleting(function($resource) {
            foreach (static::$relations_to_cascade as $relation) {
                foreach ($resource->{$relation}()->get() as $item) {
                    $item->delete();
                }
            }
        });

This piece of code calls the deleting method. Inside this method, We loop through our $relations_to_cascade array. We then get all the concerned items from our database and delete them.

Now once you understand this process, the next block will make sense


        static::restoring(function($resource) {
            foreach (static::$relations_to_cascade as $relation) {
                foreach ($resource->{$relation}()->get() as $item) {
                    $item->withTrashed()->restore();
                }
            }
        });

Yes exactly, it does the same thing but this time retrieves the concerned item withTrashed() (because it was deleted and must include trashed items, duh!) and finally restore the item.

And voilà, c'est fait!

You can repeat the same logic for other models and other relationships.

If you are starting a new Laravel project, it would be a good idea to keep this in mind and apply this paradigm with every model you create in order not to have to get back and change every model of your code — things can get messy, and trust me they will!