DT

Datatables PHP Library
Star

Group by statement

Aggregate queries with proper paging

It is tricky to use GROUP BY with server-side processing because of paging.
The library handles it transparently — no extra configuration needed.
GenreTotal Length
Loading…
 
<?php
use Ozdemir\Datatables\Datatables;
use Ozdemir\Datatables\DB\SQLite;

Route::get('/ajax/group-by', function () {
    $path = dirname(__DIR__).'/database/Chinook_Sqlite_AutoIncrementPKs.sqlite';

    $dt = new Datatables(new SQLite($path));
    $dt->query('Select Genre.Name as genre_name, sum(Milliseconds)/(1000*60) as total_length
                from Genre
                join Track on Track.GenreId = Genre.GenreId
                group by Genre.GenreId');

    $dt->edit('total_length', function ($data) {
        return $data['total_length'].' minutes';
    });

    return $dt->generate();
});