Laravel Datatable Ssp Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require syamsoul/laravel-datatable-ssp
   

   Ensure Laravel 9+ and PHP 8.0+ are used.

2. Basic Controller Setup:

   use SoulDoit\DataTable\SSP;
   
   public function getData(SSP $ssp) {
       $ssp->setColumns([
           ['label' => 'ID', 'db' => 'id'],
           ['label' => 'Name', 'db' => 'name'],
       ]);
       $ssp->setQuery(\App\Models\User::query());
       return $ssp->response()->json();
   }
   

3. Blade Integration:

   <table id="users-table"></table>
   <script>
       $(document).ready(function() {
           $('#users-table').DataTable({
               processing: true,
               serverSide: true,
               ajax: "{{ route('users.data') }}",
               columns: [
                   { data: 'id', name: 'id' },
                   { data: 'name', name: 'name' }
               ]
           });
       });
   </script>
   

First Use Case

Create a server-side paginated table for User records with search, sorting, and pagination. Use the SSP class to handle DataTables' SSP (Server-Side Processing) requests.

Implementation Patterns

Core Workflow

1. Controller Layer:

   • Inject SSP via dependency injection or instantiate manually.
   • Configure columns, query, and frontend settings.
   • Return JSON response via $ssp->response()->json().

   public function __construct() {
       $this->ssp = new SSP();
       $this->ssp->setColumns($columns)
                 ->setQuery($query)
                 ->frontend()
                     ->setFramework('datatablejs')
                     ->setInitialItemsPerPage(10);
   }
   

2. Query Configuration:

   • Use closures to dynamically build queries based on selected columns.
   • Apply filters, joins, and raw SQL as needed.

   $ssp->setQuery(function ($selected_columns) {
       return User::select($selected_columns)
           ->where('active', true)
           ->leftJoin('roles', 'users.id', '=', 'roles.user_id');
   });
   

3. Column Customization:

   • Define label, db, formatter, and class for each column.
   • Use formatters to transform data (e.g., dates, buttons, or computed fields).

   $ssp->setColumns([
       ['label' => 'Full Name', 'db' => 'name', 'formatter' => function ($value, $model) {
           return ucfirst($value);
       }],
       ['label' => 'Actions', 'db' => 'id', 'formatter' => function ($value, $model) {
           return '<button onclick="edit(' . $value . ')">Edit</button>';
       }],
   ]);
   

4. Frontend Integration:

   • Pass frontend settings to Blade views for JavaScript initialization.
   • Use chaining methods for fluent configuration.

   $fe_settings = $ssp->frontend()
       ->setResponseDataRoute('users.data')
       ->setInitialSorting('created_at', true)
       ->getSettings();
   

   <script>
       $('#table').DataTable({!! $fe_settings !!});
   </script>
   

Advanced Patterns

1. Dynamic Column Selection:

   • Use DB::raw() or subqueries for complex columns.
   • Example: Grouped or calculated fields.

   $ssp->setColumns([
       ['label' => 'Total Orders', 'db' => DB::raw('(SELECT COUNT(*) FROM orders WHERE user_id = users.id)')],
   ]);
   

2. Export Functionality:

   • Leverage built-in CSV export with custom filenames.
   • Example: Export all records or filtered results.

   $ssp->allowExportAllItemsInCsv();
   return $ssp->response()->csv('users_export_' . now()->format('Y-m-d'));
   

3. Caching Responses:

   • Cache JSON responses to reduce database load.
   • Example: Cache for 120 seconds.

   return $ssp->response()->json(120); // Cache for 120 seconds
   

4. Search Customization:

   • Specify db_for_search to control which columns are searchable.
   • Example: Search only by name and email.

   $ssp->setColumns([
       ['label' => 'Name', 'db' => 'name', 'db_for_search' => 'name'],
       ['label' => 'Email', 'db' => 'email', 'db_for_search' => 'email'],
   ]);
   

5. Left Joins:

   • Add joins dynamically or statically.
   • Example: Join roles table.

   $ssp->leftJoin('roles', 'users.id', '=', 'roles.user_id', 'role_name', 'roles.name');
   

Gotchas and Tips

Pitfalls

1. Column Name Conflicts:

   • If using left joins, ensure column names are aliased to avoid SQL conflicts.
   • Example: Use DB::raw('roles.name as role_name').

2. Formatter Issues:

   • Formatters must return a string or HTML. Non-string returns (e.g., null or objects) may break rendering.
   • Always check if $model exists before accessing its properties.

   'formatter' => function ($value, $model) {
       return $model ? $model->name : 'N/A';
   }
   

3. Searchability:

   • By default, all columns are searchable unless explicitly disabled.
   • Use 'searchable' => false to exclude a column from search.

   ['label' => 'Actions', 'db' => 'id', 'searchable' => false]
   

4. Pagination Edge Cases:

   • When itemsPerPage === -1, the package returns all items. Ensure your query and application can handle large datasets.
   • Test with -1 to verify export functionality.

5. Caching Quirks:

   • Caching (response()->json($cache_timeout)) uses Laravel's cache driver.
   • Set $cache_timeout = 0 to disable caching entirely.

6. Route vs. URL:

   • Prefer setResponseDataRoute() over hardcoding URLs for maintainability.
   • Example: $ssp->frontend()->setResponseDataRoute('users.data').

7. Large Datasets:

   • For tables with >10,000 rows, optimize queries with indexes and avoid SELECT *.
   • Use DB::connection()->disableQueryLog() in development to reduce overhead.

Debugging Tips

1. Query Logging:

   • Enable Laravel's query logging to inspect generated SQL.

   DB::enableQueryLog();
   $ssp->response()->json();
   dd(DB::getQueryLog());
   

2. Frontend Validation:

   • Use browser DevTools to check if DataTables receives the correct JSON structure.
   • Validate keys: draw, recordsTotal, recordsFiltered, data.

3. Formatter Debugging:

   • Temporarily return raw values to isolate issues.

   'formatter' => function ($value, $model) {
       return print_r($model->toArray(), true); // Debug model data
   }
   

4. Column Aliasing:

   • If a column fails to render, verify its db key matches the query's selected columns.
   • Use DB::raw() for complex expressions:

   ['db' => DB::raw('CONCAT(first_name, " ", last_name) as full_name')]
   

Extension Points

1. Custom Validation:

   • Extend ValidationException for custom error handling.
   • Example: Override handle() in a service provider.

2. Frontend Framework:

   • Supports datatablejs (default) and others (custom frameworks).
   • Disable fetch-on-init for lazy-loading:

   $ssp->frontend()->disableFetchOnInit();
   

3. Response Modification:

   • Extend the Response class to add custom fields or modify the JSON structure.
   • Example: Add a server_time field.

   $ssp->response()->addField('server_time', now()->toDateTimeString());
   

4. Search Keyword Formatting:

   • Customize how search keywords are displayed (e.g., highlight matches).

   $ssp->searchKeywordFormatter(function ($keyword) {
       return '<strong>' . htmlspecialchars($keyword) . '</strong>';
   });
   

5. MySQL Variables:

   • Initialize MySQL session variables for large datasets.

   $ssp->setMysqlVariables(['max_allowed_packet' => '256M']);
   

Performance Tips

1. Selective Column Loading:
   • Only select columns needed for display to reduce payload size.

   $ssp->setQuery(function ($selected_columns) {
       return User::select(array