README

A fluent datatable query builder for Laravel with Yajra Datatables. Supports MySQL and PostgreSQL with automatic driver detection.

Requirements

• PHP >= 8.1
• Laravel >= 10.0
• yajra/laravel-datatables-oracle >= 10.0

Installation

composer require lutfi-nur/query-builder

No service provider registration or config publishing required. This package is zero overhead — classes are only loaded when dtBuilder() is called.

Quick Start

1. Add the Trait to Your Model

use LutfiNur\QueryBuilder\Traits\HasDatatableQuery;

class User extends Model
{
    use HasDatatableQuery;

    const tableName = 'users';
    protected $fillable = ['name', 'email', 'birth_date', 'is_blocked'];
}

2. Create a Datatable Method

use Illuminate\Http\Request;

class User extends Model
{
    use HasDatatableQuery;

    const tableName = 'users';
    protected $fillable = ['name', 'email', 'birth_date', 'is_blocked'];

    public static function datatable(Request $request): mixed
    {
        $table = static::tableName;
        $dt = static::dtBuilder($table);

        // Date columns (auto-converted for PostgreSQL)
        $dt->addDateColumn('created_at', '%d-%b-%Y', 'created');
        $dt->addDateColumn('updated_at', '%W, %d %M %Y %H:%i:%s', 'updated_str');

        // Boolean columns (generates {col}_str + {col}_class)
        $dt->addBoolColumn('is_blocked', 'Yes', 'No', 'danger', 'success');

        // File link with fallback (IFNULL / COALESCE)
        $dt->addFileColumn('avatar_link', url('upload/avatar') . '/', "$table.avatar", url('img/default.png'));

        // CONCAT without fallback
        $dt->addConcatColumn('doc_link', url('upload/doc') . '/', "$table.document");

        // Alias from joined table
        $dt->addAliasColumn('province_name', 'prov.name');

        // Raw expression (driver-aware via grammar)
        $grammar = $dt->grammar();
        $dt->addRawColumn('status_label', $grammar->conditional("$table.status = 1", "'Active'", "'Inactive'"));

        // Select-only column (not included in global search)
        $dt->addRawColumn('status_class', $grammar->conditional("$table.status = 1", "'success'", "'danger'"), searchable: false);

        // Build select with joins
        $model = $dt->buildSelect()
            ->leftJoin('provinces as prov', "$table.province_id", '=', 'prov.id');

        // Apply filters
        $dt->applyRangeFilter($model, $request, 'birth_date');
        $dt->applyExactFilters($model, $request, ['province_id', 'is_blocked']);
        $dt->applyNullFilter($model, $request, 'email', 'has_email');

        // Build & return datatable response
        return $dt->buildDatatable($model);
    }
}

3. Define a Route

Route::get('/users/datatable', function (Request $request) {
    return User::datatable($request);
});

API Reference

Column Builders

Filters

All filters read from $request->input('filter').

Build

Getters

Date Format

Format strings use MySQL specifiers as the standard. For PostgreSQL, conversion is automatic.

Database Support

The database driver is automatically detected from the active Laravel database connection.

Custom Grammar

To support additional drivers (e.g., SQL Server), create a class that implements GrammarInterface and register it in your AppServiceProvider::boot():

use LutfiNur\QueryBuilder\Contracts\GrammarInterface;
use LutfiNur\QueryBuilder\DatatableQueryBuilder;

class SqlServerGrammar implements GrammarInterface
{
    // Implement all methods: dateFormat, conditional, ifNull, concat, like, getDriverName
}

// In AppServiceProvider::boot()
DatatableQueryBuilder::registerGrammar('sqlsrv', SqlServerGrammar::class);

Generated SQL Examples

MySQL

SELECT users.*,
       (DATE_FORMAT(users.created_at, '%d-%b-%Y')) as created,
       (IF(users.is_blocked = 1, 'Yes', 'No')) as is_blocked_str,
       (IF(users.is_blocked = 1, 'danger', 'success')) as is_blocked_class,
       IFNULL(CONCAT('http://localhost/upload/avatar/', users.avatar), 'http://localhost/img/default.png') as avatar_link
FROM users
WHERE ... LIKE '%search%'

PostgreSQL

SELECT users.*,
       (TO_CHAR(users.created_at, 'DD-Mon-YYYY')) as created,
       (CASE WHEN users.is_blocked = 1 THEN 'Yes' ELSE 'No' END) as is_blocked_str,
       (CASE WHEN users.is_blocked = 1 THEN 'danger' ELSE 'success' END) as is_blocked_class,
       COALESCE(CONCAT('http://localhost/upload/avatar/', users.avatar), 'http://localhost/img/default.png') as avatar_link
FROM users
WHERE ...::text ILIKE '%search%'

How It Works

1. No ServiceProvider — Pure PSR-4 autoload. Classes are only loaded by PHP when first used.
2. Auto driver detection — Grammar is resolved from DB::connection()->getDriverName() at build time.
3. Global search — Covers all computed (searchable) columns + model $fillable columns.
4. Parameterized queries — All search and filter values use parameter binding (SQL injection safe).
5. searchable flag — Columns added with searchable: false are included in SELECT but excluded from global search. Useful for IDs, CSS classes, or display-only expressions.

License

MIT