Laravel Datatable

Laravel Datatable is a package designed to handle server-side logic for datatables in Laravel applications.

Key Features

• Standalone server-side solution for table-like data handling
• Compatible with various frontend table libraries (e.g., Material React Table)
• Support for multiple search logics (contains, equals, greater than, etc.) across different data types (numeric, text, date)
• Fine-grained control over searchable, sortable, and visible fields
• Ability to search through model relationships
• Customizable search logic (coming soon!)

Requirements

• PHP ≥ 8.1
• Laravel ≥ 9.0

Installation

You can install the package via composer:

composer require hamidrrj/laravel-datatable

After installation, publish the package's service provider using one of the following methods:

Option 1: Automatic Installation (Recommended)

Run the following Artisan command:

php artisan datatable:install

Option 2: Manual Installation

Publish the provider manually:

php artisan vendor:publish --tag="datatable-provider"

Then, add the following line to the providers array in config/app.php:

return [
    // ...
    'providers' => ServiceProvider::defaultProviders()->merge([
        // ...
        App\Providers\DatatableServiceProvider::class,
        // ...
    ])->toArray(),
    // ...
];

Usage

This section covers various use cases and features of Laravel Datatable. From basic querying to advanced filtering and relationship handling, you'll find examples to help you make the most of this package.

Table of Contents

• Method Parameters
• Filter Array Structure
• Return Data Structure
• Basic Usage
• Using Query Builder
• Advanced Filtering and Sorting
• Using between Search Function
• Filtering Model's Relationship

Method Parameters

The run method of DatatableFacade accepts the following parameters:

1. $mixed: Model instance or query builder instance to perform queries on.
2. $requestParameters: Contains parameters like filter, sorting, size, and start of required data.
3. $allowedFilters: (Optional) Specifies columns users are allowed to filter on.
4. $allowedSortings: (Optional) Specifies columns users are allowed to sort on.
5. $allowedSelects: (Optional) Specifies which columns users can actually see.
6. $allowedRelations: (Optional) Specifies which model relations users are allowed to filter on.

Filter Array Structure

Each filter in the filters array should have the following attributes:

• id: Name of the column to filter on. When filtering a relationship's attribute, use the format: relationName.attribute. (relationName must exist as a HasOne or HasMany relationship in the base Model, e.g., User model)
• value: Value of the filter
  • For most filter types: a single value
  • For fn = 'between': an array of two values, e.g., [min, max]
• fn: Type of filter to apply. Available options include:
  • contains
  • between
  • equals
  • notEquals
  • lessThan
  • lessThanOrEqual
  • greaterThan
  • greaterThanOrEqual
• datatype: Type of column. Options include:
  • text
  • numeric
  • date

Return Data Structure

The run method returns an array with the following structure:

[
    "data" => [
        // Array of matching records
    ],
    "meta" => [
        "totalRowCount" => 10 // Total count of matching records
    ]
]

Basic Usage

Here's a simple example of requesting a chunk of 10 users starting from the 11th record (i.e., page 2 of the datatable):

use \HamidRrj\LaravelDatatable\Facades\DatatableFacade;

$userModel = new User();

$requestParameters = [
    'start' => 10,
    'size' => 10,
    'filters' => [],
    'sorting' => []
];

$data = DatatableFacade::run(
    $userModel,
    $requestParameters
);

Using Query Builder

You can use a query builder instance instead of a model instance:

$query = User::query()->where('username', '!=', 'admin');

$data = DatatableFacade::run(
    $query,
    $requestParameters
);

Advanced Filtering and Sorting

Here's an example of filtering users whose ages are greater than 15, sorted by creation date in descending order:

$query = User::query();

$requestParameters = [
    'start' => 10,
    'size' => 10,
    'filters' => [
        [
            'id' => 'age',
            'value' => 15,
            'fn' => 'greaterThan',
            'datatype' => 'numeric'
        ]
    ],
    'sorting' => [
        [
            'id' => 'created_at',
            'desc' => true,
        ]
    ]
];

$allowedFilters = ['age'];
$allowedSortings = ['created_at'];

$data = DatatableFacade::run(
    $query,
    $requestParameters,
    $allowedFilters,
    $allowedSortings
);

Note: Ensure that columns used for filtering and sorting are included in the $allowedFilters and $allowedSortings arrays to avoid InvalidFilterException and InvalidSortingException.

Using between search function

Here's an example of filtering users whose creation dates are between two dates:

$query = User::query()

$requestParameters = [
        'start' => 0,
        'size' => 10,
        'filters' => [
            [
                'id' => 'created_at',
                'value' => ['2024-05-23 10:30:00', '2024-05-29 15:00:00'],
                'fn' => 'between',
                'datatype' => 'date'
            ]
        ],
        'sorting' => []
    ];

$allowedFilters = array('created_at');
$allowedSelects = array('username', 'age', 'created_at');

$data = (new Datatable())->run(
    $query,
    $requestParameters,
    $allowedFilters,
    allowedSelects: $allowedSelects
);

Note: Using $allowedSelects will only return specified columns in the query result:

[
    "data" => [
        [
            'username' => 'mwindler'
            'age' => 49
            'created_at' => '2024-05-23T12:00:00.000000Z' 
        ],
        // more matching records
    ],
    "meta" => [
        "totalRowCount" => 10 // Total count of matching records
    ]
]

Filtering Model's Relationship

In this example, we filter only users who have posts that contain 'my post' in their titles:

$query = User::query();

$requestParameters = [
    'start' => 0,
    'size' => 10,
    'filters' => [
        [
            'id' => 'posts.title',
            'value' => 'my post',
            'fn' => 'contains',
            'datatype' => 'text'
        ]
    ],
    'sorting' => []
];

$allowedFilters = array('posts.title');
$allowedRelations = array('posts');

$data = (new Datatable())->run(
    $query,
    $requestParameters,
    $allowedFilters,
    allowedRelations: $allowedRelations
);

Note:

• Use posts.title in id (the User model must have a posts relation defined in Models/User class)
• Using $allowedRelations loads each user's posts in the query result:

[
    "data" => [
        [
            'id' => 1,
            'username' => 'sth', 
            'posts' => [  // posts included in result
                [
                    'title' => 'wow! my post got 1k impressions!'
                ], 
                // ...
            ]
        ],
        // more matching records
    ],
    "meta" => [
        "totalRowCount" => 10 // Total count of matching records
    ]
]

Testing

composer test

Changelog

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

Contributing

Please see CONTRIBUTING for details.

Credits

• Hamidreza Ranjbarpour

License

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