Complex

id
ID
Planet
Region
Name
Planet Mates
Car
Post Count
Weapon Names
Weapon Count
Computed (php closure)
Computed (raw SQL)
Email Verified
DOB
Birthday
Age
Orbital period
native_age
Bedtime
Go to bed
Email
Bio
Role
Weapons
Light Saber
0
SELECT ALL
Tund
Wild Spa...
90qsfqsdfsdqasda...
0
0
User 1961 hails from Tund
User 1961 hails from Tund
12/04/1980
12th April
43
1770
20:24
Aut tenetur dicta te...
Purge Trooper
Dorin
Wild Spa...
Albin Waelchi, A...
0
0
User 1999 hails from Dorin
User 1999 hails from Dorin
24/05/1947
24th May
76
409
03:08
Velit odit et dolor...
Shock Trooper
Naboo
Wild Spa...
Mueller III a,...
0
0
User 1975 hails from Naboo
User 1975 hails from Naboo
08/12/1927
8th December
95
312
01:46
Sit quibusdam quos i...
Shock Trooper
Naboo
Wild Spa...
Mueller III a,...
0
0
User 1996 hails from Naboo
User 1996 hails from Naboo
10/12/1985
10th December
37
312
19:19
Velit aspernatur vol...
AT-ST Driver
Umbara
Wild Spa...
Anahi Nitzsche,...
0
0
User 1879 hails from Umbara
User 1879 hails from Umbara
07/11/1927
7th November
95
00:32
Omnis porro dicta cu...
Umbara
Wild Spa...
Anahi Nitzsche,...
0
0
User 1897 hails from Umbara
User 1897 hails from Umbara
23/05/1996
23rd May
27
21:28
Aut qui architecto d...
Jumptrooper
Tund
Wild Spa...
90qsfqsdfsdqasda...
0
0
User 1896 hails from Tund
User 1896 hails from Tund
04/04/1959
4th April
64
1770
00:28
Architecto consectet...
Imperial Guard
Umbara
Wild Spa...
Anahi Nitzsche,...
0
0
User 1925 hails from Umbara
User 1925 hails from Umbara
29/05/1951
29th May
72
21:01
Consequatur vero qua...
Dorin
Wild Spa...
Albin Waelchi, A...
0
0
User 1946 hails from Dorin
User 1946 hails from Dorin
13/10/1958
13th October
64
409
20:43
Et qui consequuntur...
Jumptrooper
Ryloth
Wild Spa...
566, Arno Kuphal...
0
0
User 1894 hails from Ryloth
User 1894 hails from Ryloth
23/08/2003
23rd August
19
305
20:21
Veritatis labore qui...
AT-AT Pilot
Previous
Results 1 - 10 of 2000
Query Builder
Array
(
)
<livewire:complex-demo-table />
<?php

namespace App\Http\Livewire;

use App\User;
use App\Planet;
use App\Region;
use App\Weapon;
use Illuminate\Support\Str;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Mediconesystems\LivewireDatatables\Column;
use Mediconesystems\LivewireDatatables\DateColumn;
use Mediconesystems\LivewireDatatables\TimeColumn;
use Mediconesystems\LivewireDatatables\NumberColumn;
use Mediconesystems\LivewireDatatables\BooleanColumn;
use Mediconesystems\LivewireDatatables\Http\Livewire\LivewireDatatable;

class ComplexDemoTable extends LivewireDatatable
{
    public $hideable = 'select';
    public $exportable = true;
    public $complex = true;
    public $persistComplexQuery = true;
    public $afterTableSlot = 'components.selected';

    public function builder()
    {
        return User::query()->leftJoin('planets', 'planets.id', 'users.planet_id');
    }

    public function columns()
    {
        return [
            Column::checkbox(),

            NumberColumn::name('id')
                ->label('ID')
                ->filterable()
                ->linkTo('user', 6),

            Column::name('planets.name')
                ->label('Planet')
                ->filterable($this->planets)->alignRight(),

            Column::name('planet.region.name')
                ->label('Region')
                ->defaultSort('desc')
                ->filterable($this->regions)
                ->truncate(8)
                ->searchable(),

            Column::name('name')
                ->filterable()
                ->editable(),

            Column::name('comrades.name')
                ->label('Planet Mates')
                ->truncate()
                ->filterable(),

            Column::name('car.model')
                ->label('Car')
                ->alignCenter()
                ->filterable(['Audi', 'BMW', 'Caterham', 'Dodge', 'Ferrari', 'Jaguar', 'Lamborghini', 'Porsche']),

            NumberColumn::name('posts.id:count')
                ->label('Post Count')
                ->filterable()
                ->alignRight(),

            Column::name('weapons.name')
                ->filterable($this->weapons->pluck('name'))
                ->label('Weapon Names'),

            NumberColumn::name('weapons.id')
                ->filterable()
                ->exportCallback(function ($value) {
                    return (string) $value;
                })
                ->label('Weapon Count'),

            Column::callback(['id', 'planet.name'], function ($id, $planetName) {
                return "User $id hails from $planetName";
            })->label('Computed (php closure)')
                ->filterOn('planets.name')
                ->filterable($this->planets),

            Column::raw('CONCAT("User ", users.id, " hails from ", planets.name) AS planetName')
                ->label('Computed (raw SQL)')
                ->filterable(),

            BooleanColumn::name('email_verified_at')
                ->label('Email Verified')
                ->filterable(),

            DateColumn::name('dob')
                ->label('DOB')
                ->filterable(),

            DateColumn::raw('dob AS dob2')
                ->label('Birthday')
                ->format('jS F')
                ->sortBy(DB::raw('DATE_FORMAT(users.dob, "%m%d%Y")')),

            NumberColumn::raw('FLOOR(DATEDIFF(NOW(), users.dob)/365) AS Age')
                ->filterable(),

            NumberColumn::name('planet.orbital_period')
                ->filterable(),

            Column::raw("IF(planets.orbital_period REGEXP '^-?[0-9]+$', CONCAT(ROUND(DATEDIFF(NOW(), users.dob) / planets.orbital_period, 1), ' ', planets.name, ' years'), '---') AS native_age")
                ->filterable()
                ->hide(),

            TimeColumn::name('bedtime')
                ->filterable(),

            Column::callback('bedtime', 'computeBedtime')
                ->label('Go to bed')
                ->hide(),

            Column::name('email')
                ->searchable()
                ->filterable()
                ->view('components.email'),

            Column::name('bio')
                ->truncate(20)
                ->filterable(),

            Column::name('role')
                ->searchable()
                ->filterable([
                    'Stormtrooper',
                    'AT-AT Pilot',
                    'AT-ST Driver',
                    'Imperial Guard',
                    'Shock Trooper',
                    'Shadow Trooper',
                    'Purge Trooper',
                    'Jumptrooper'
                ]),

            Column::scope('selectGroupedWeaponNames', 'Weapons')
                ->filterable($this->weapons, 'filterWeaponNames'),

            BooleanColumn::scope('hasLightSaber', 'Light Saber')
                ->filterable(null, 'filterHasLightSaber')
        ];
    }

    public function getPlanetsProperty()
    {
        return Planet::pluck('name');
    }

    public function getRegionsProperty()
    {
        return Region::pluck('name');
    }

    public function getWeaponsProperty()
    {
        return Weapon::all();
    }

    public function computeBedtime($date)
    {
        if (!$date) {
            return;
        }
        return Carbon::parse($date)->isPast()
            ? Carbon::parse($date)->addDay()->diffForHumans(['parts' => 2])
            : Carbon::parse($date)->diffForHumans(['parts' => 2]);
    }

    public function rowClasses($row, $loop)
    {
        return 'divide-x divide-gray-100 text-sm text-gray-900 ' . ($this->rowIsSelected($row) ? 'bg-yellow-100' : ($row->{'car.model'} === 'Ferrari' ? 'bg-red-500' : ($loop->even ? 'bg-gray-100' : 'bg-gray-50')));
    }

    public function cellClasses($row, $column)
    {
        return 'text-sm ' . ($this->rowIsSelected($row) ? ' text-gray-900' : ($row->{'car.model'} === 'Ferrari' ? ' text-white' : ' text-gray-900'));
    }
}
<?php

namespace App;

use App\Planet;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;

class User extends Authenticatable
{
    use Notifiable, SoftDeletes;

    protected $fillable = [
        'name', 'email', 'password',
    ];

    protected $hidden = [
        'password', 'remember_token',
    ];

    protected $casts = [
        'email_verified_at' => 'datetime',
    ];

    public function car()
    {
        return $this->hasOne(Car::class);
    }

    public function comrades()
    {
        return $this->hasManyThrough(User::class, Planet::class, 'id', 'planet_id', 'planet_id', 'id');
    }

    public function posts()
    {
        return $this->hasMany(Post::class);
    }

    public function weapons()
    {
        return $this->belongsToMany(Weapon::class);
    }

    public function planet()
    {
        return $this->belongsTo(Planet::class);
    }

    public function region()
    {
        return $this->hasOneThrough(Region::class, Planet::class, 'id', 'id', 'planet_id', 'region_id');
    }

    public function scopeSelectGroupedWeaponNames($query, $alias)
    {
        $query->addSelect([
            $alias => Weapon::selectRaw('GROUP_CONCAT(name SEPARATOR " | ")')
                ->leftJoin('user_weapon', 'user_weapon.weapon_id', 'weapons.id')
                ->whereColumn('user_weapon.user_id', 'users.id')
        ]);
    }

    public function scopeFilterWeaponNames($query, $value)
    {
        $query->whereHas('weapons', function ($query) use ($value) {
            $query->where('weapons.id', $value);
        });
    }

    public function scopeHasLightSaber($query, $alias)
    {
        $query->addSelect([
            $alias => Weapon::selectRaw('IF(COUNT(weapons.id), 1, 0)')
                ->leftJoin('user_weapon', 'user_weapon.weapon_id', 'weapons.id')
                ->whereColumn('user_weapon.user_id', 'users.id')
                ->where('weapons.name', 'Light Saber')
        ]);
    }

    public function scopeFilterHasLightSaber($query, $value)
    {
        $query->whereHas('weapons', function ($query) use ($value) {
            $query->where('weapons.name', $value ? '=' : '<>', 'Light Saber');
        });
    }
}