README

A lightweight, server-side datatable query layer for Laravel. Wrap any Eloquent or Query Builder instance and get standardized JSON pagination, search, sorting and filtering driven by HTTP request parameters — with hooks to override each step.

Support us

We invest a lot of resources into creating best in class open source packages. You can support us by buying one of our paid products.

We highly appreciate you sending us a postcard from your hometown, mentioning which of our package(s) you are using. You'll find our address on our contact page. We publish all received postcards on our virtual postcard wall.

Requirements

• PHP ^8.4
• Laravel ^11.0 || ^12.0 || ^13.0

Installation

Install via Composer:

composer require alemian95/laraveldatatable

The service provider is auto-discovered (AleMian95\Datatable\DatatableServiceProvider) — no manual registration required.

Optionally, publish the config file to override defaults:

php artisan vendor:publish --tag="laraveldatatable-config"

This is the contents of the published config/laraveldatatable.php:

return [
    'default' => [
        // Default page size used when the request omits "per_page".
        'per_page' => 15,

        // Hard upper bound: a larger "per_page" is clamped down to this cap.
        'max_per_page' => 100,
    ],

    'debug' => [
        // When true, the interpolated SQL of each query is logged at "info".
        // Off by default: the SQL can contain the raw search term (PII).
        'log_sql' => false,
    ],

    'search' => [
        // When true: fall back to Schema introspection if no whitelist is declared.
        // When false: declaring HasSearchableColumns or withSearchableColumns() is mandatory.
        'auto_discover_columns' => true,

        // Column names / wildcard patterns always excluded from auto-discovery.
        'auto_discovery_blacklist' => [
            'password', 'remember_token', 'api_token',
            '*_token', '*_secret', '*_hash', '*_key',
        ],
    ],
];

Usage

The main entry point is AleMian95\Datatable\DatatableApi. It implements JsonSerializable, so returning an instance directly from a controller produces a paginated JSON response automatically.

Quick start

use AleMian95\Datatable\DatatableApi;
use App\Models\User;

public function index()
{
    return new DatatableApi()
        ->fromQuery(User::query());
}

That single call already supports search, sort and pagination via the HTTP query string described below.

HTTP request contract

DatatableRequest parses the following parameters from the incoming request:

Example request:

GET /api/users?search=jane&search_columns=first_name,last_name,email&sort_by=created_at&sort_order=desc&per_page=25

Response shape

Without a resource, the response is a standard Laravel length-aware paginator:

{
  "current_page": 1,
  "data": [ { "id": 1, "name": "Jane Doe", "...": "..." } ],
  "first_page_url": "...",
  "from": 1,
  "last_page": 5,
  "last_page_url": "...",
  "links": [ /* ... */ ],
  "next_page_url": "...",
  "path": "...",
  "per_page": 15,
  "prev_page_url": null,
  "to": 15,
  "total": 75
}

When returnResource(ResourceClass::class) is used, the paginator is wrapped in ResourceClass::collection($paginator), producing the conventional { "data": [...], "links": {...}, "meta": {...} } envelope.

Customizing the pipeline

Each builder method below returns $this, so they can be chained freely.

• fromQuery(Builder $query): self — accepts an Eloquent builder, a Relation, or a base QueryBuilder. Required.
• withCustomSearch(Closure $search): self — overrides the default LIKE/auto-column search. The closure receives ($builder, string $term) and is responsible for the full search clause.
• withCustomSorts(array $sorts): self — map of sort_by value → Closure($builder, string $direction). Triggered only when the incoming sort_by matches a key; otherwise the default sort logic runs.
• withSortableColumns(array $columns): self — authoritative whitelist for the sort_by parameter (dot-notation entries included, e.g. author.name). A sort_by outside the list is dropped with a warning instead of reaching the database; keys declared via withCustomSorts() are always allowed. Leave it unset to keep the default behavior of sorting by any client-supplied column.
• withCustomFilters(array $filters): self — array of Closure($builder) applied sequentially. Useful for hard-coded business filters (active scope, tenant scope, etc.) that should not be controllable from the client.
• withSearchableColumns(array $columns): self — declares the authoritative whitelist of columns the search can target for this instance. Wins over the HasSearchableColumns contract on the model and is the only way to enable search on a raw QueryBuilder when auto_discover_columns is false. When set, search_columns from the request is intersected against this whitelist.
• returnResource(string $resourceClass): self — fully-qualified API Resource class name. Output is wrapped via Resource::collection($paginator).

Full chained example:

use AleMian95\Datatable\DatatableApi;
use App\Http\Resources\UserResource;
use App\Models\User;

public function index()
{
    return new DatatableApi()
        ->fromQuery(
            User::query()->with('profile', 'role')
        )
        ->withCustomSorts([
            'full_name' => fn ($builder, $direction) =>
                $builder->orderByRaw("CONCAT(first_name, ' ', last_name) {$direction}"),
        ])
        ->withCustomFilters([
            fn ($builder) => $builder->where('active', true),
        ])
        ->returnResource(UserResource::class);
}

Searchable columns

The set of columns that can be searched is resolved in this order:

1. DatatableApi::withSearchableColumns(['col_a', 'col_b']) — wins over everything.
2. Model implements HasSearchableColumns — the contract returns the whitelist (the trait Concerns\HasSearchableColumns reads a protected array $searchable = [...] property by default).
3. Auto-discovery via Schema::getColumnListing — fallback only when config('laraveldatatable.search.auto_discover_columns') is true (default for backward compatibility). Filters out non-string columns and applies the auto_discovery_blacklist. When the request supplies search_columns in this branch, they are intersected against the auto-discovery result — so the type filter and the blacklist also protect against client-supplied column names.

When a whitelist is declared, search_columns from the HTTP request is intersected against it: the client can never broaden it. An empty whitelist (withSearchableColumns([]) or protected array $searchable = []) is treated as an authoritative signal to omit the search clause entirely — no LIKE is applied, the dataset is returned unfiltered by the search term (pagination, sorting and other filters still apply), and there is no fallback to the next source. When no source can satisfy the request and auto-discovery is off, a SearchColumnsNotConfiguredException is thrown.

Example with the trait:

use AleMian95\Datatable\Contracts\HasSearchableColumns;
use AleMian95\Datatable\Concerns\HasSearchableColumns as HasSearchableColumnsTrait;

class User extends Model implements HasSearchableColumns
{
    use HasSearchableColumnsTrait;

    protected array $searchable = ['first_name', 'last_name', 'email', 'profile.bio'];
}

Example with the per-request override (works for both Eloquent and raw QueryBuilder):

return new DatatableApi()
    ->fromQuery(DB::table('users'))
    ->withSearchableColumns(['name', 'email']);

To make declaration mandatory project-wide, set in config/laraveldatatable.php:

'search' => [
    'auto_discover_columns' => false,
],

Resolver lifecycle. The SearchColumnResolver is bound to the container as a scoped instance, so each HTTP request / queue job receives a fresh resolver built from the current laraveldatatable.search.* values. This means multi-tenant setups that swap the config per request get the expected per-tenant behavior with no extra work. For the rare case of changing the config mid-request (e.g. inside tests), call app()->forgetInstance(\AleMian95\Datatable\Contracts\SearchColumnResolver::class) after the change to force re-resolution.

Relational search

When search_columns contains a dot — author.name, tags.label — the package needs to know how to resolve the relation segment (author, tags) into SQL.

On Eloquent builders the relation is auto-discovered from the model. No extra configuration is needed:

return new DatatableApi()
    ->fromQuery(Book::query())
    ->withSearchableColumns(['title', 'author.name', 'tags.label']);

Supported relation types via auto-discovery: BelongsTo, HasOne, HasMany, BelongsToMany. Other relation types (MorphTo, HasManyThrough, …) need an explicit declaration via withRelationSearch() using RelationSearch::custom().

On a raw QueryBuilder there is no model to introspect, so the relation must be declared explicitly:

use AleMian95\Datatable\Search\RelationSearch;

return new DatatableApi()
    ->fromQuery(DB::table('books'))
    ->withSearchableColumns(['title', 'author.name'])
    ->withRelationSearch([
        'author' => RelationSearch::belongsTo('authors'),
    ]);

Smart defaults follow the Laravel conventions: belongsTo('authors') assumes author_id and id. Override only when the schema diverges:

->withRelationSearch([
    'author'    => RelationSearch::belongsTo('writers', localKey: 'written_by', remoteKey: 'uuid'),
    'publisher' => RelationSearch::hasOne('publishers', foreignKey: 'book_isbn', localKey: 'isbn'),
    'tags'      => RelationSearch::belongsToMany('tags', pivot: 'book_tag'),
])

A declared spec wins over Eloquent auto-discovery for the same relation key, which is the right tool to inject custom scopes (soft-delete filtering, tenant constraints) without rewriting the whole search:

->withRelationSearch([
    'author' => RelationSearch::custom(function ($query, $remoteColumn, $term) {
        $query->orWhereExists(fn ($sub) =>
            $sub->from('authors')
                ->whereColumn('authors.id', 'books.author_id')
                ->whereNull('authors.deleted_at')
                ->whereLike("authors.{$remoteColumn}", "%{$term}%")
        );
    }),
])

Multi-hop dotted paths (book.author.country.name) are resolved automatically on Eloquent via the existing orWhereHas chain. On raw QueryBuilder multi-hop is unsupported in v1 — the column is dropped with a Log::warning.

Generated SQL uses orWhereExists with explicit key joins (and an inner join for belongsToMany). Columns are always qualified table.column to avoid ambiguity with the base table.

Migrating from older versions that already used Eloquent dot-notation search: the row set returned by the package is unchanged. The SQL emitted internally changes from orWhereHas(...) to orWhereExists(...); this is visible in query logs and any test that asserts on the raw SQL text, but is invisible at the result-set level.

Known limits

1. Multi-hop dot-notation on raw QueryBuilder. Single-hop paths (author.name) work on both Eloquent and raw queries — see Relational search. Multi-hop paths (author.country.name) are supported only on Eloquent (resolved via orWhereHas); on a raw QueryBuilder they are dropped with a Log::warning.

2. Relational sorting supports BelongsTo only. For sort_by=author.name, SortApplier performs a leftJoin on each BelongsTo segment and then orders by the joined column. For any other relation type (or any segment that is not a BelongsTo) it falls back to a plain orderBy('author.name', ...), which will fail at the SQL layer because that column does not exist on the base table. Either expose such sorts via withCustomSorts(...) or restrict the client to BelongsTo paths.

3. SQL logging outside production. While app()->isProduction() is false, every assembled query is written to the application log via Log::info($builder->toRawSql()). This is intentional for local debugging — be aware of it in staging environments where it can produce noisy logs.

Testing

composer test

Changelog

Please see CHANGELOG for more information on what has changed recently.

Contributing

Please see CONTRIBUTING for details.

Security Vulnerabilities

Please review our security policy on how to report security vulnerabilities.

Credits

• Alessandro Mian
• All Contributors

License

The MIT License (MIT). Please see License File for more information.