Column Sortable Laravel Package

Getting Started

1. Installation:

   composer require kyslik/column-sortable
   

   For Laravel ≥5.5, auto-discovery handles registration. For older versions, manually add Kyslik\ColumnSortable\ColumnSortableServiceProvider to config/app.php.

2. Publish Config:

   php artisan vendor:publish --provider="Kyslik\ColumnSortable\ColumnSortableServiceProvider" --tag="config"
   

3. First Use Case:

   • Add the Sortable trait to your Eloquent model:

     use Kyslik\ColumnSortable\Sortable;
     
     class User extends Model {
         use Sortable;
         public $sortable = ['id', 'name', 'email'];
     }
     

   • Use the sortable() method in your controller:

     $users = User::sortable()->paginate(10);
     

   • Display sortable columns in Blade:

     @sortablelink('name', 'Username')
     

Implementation Patterns

Basic Sorting Workflow

1. Model Setup: Define $sortable array in your model to specify sortable columns. This avoids runtime database checks for column existence.

2. Controller Integration:

   // Default sorting (ascending)
   $users = User::sortable()->paginate(10);
   
   // Explicit sorting
   $users = User::sortable('name')->paginate(10);
   $users = User::sortable(['name' => 'desc'])->paginate(10);
   

3. Blade Integration: Use @sortablelink() directive to generate clickable sort links:

   @sortablelink('name', 'Username', ['filter' => 'active'], ['class' => 'sort-link'])
   

   • Parameters:
     1. Column name (required).
     2. Display text (optional, defaults to column name).
     3. Query parameters (optional, appended to URL).
     4. HTML attributes (optional, e.g., classes, rel).

4. Pagination Handling: Preserve query parameters (except page) when rendering pagination:

   {!! $users->appends(\Request::except('page'))->render() !!}
   

Advanced Patterns

Relation Sorting

1. Define Relations:

   // User model
   public function detail() {
       return $this->hasOne(UserDetail::class);
   }
   

2. Configure $sortable:

   // UserDetail model
   public $sortable = ['phone_number'];
   

3. Sort via Relation:

   @sortablelink('detail.phone_number', 'Phone')
   

4. Custom Relation Queries: Override addressSortable() in your model for complex joins:

   public function addressSortable($query, $direction) {
       return $query->join('user_details', 'users.id', '=', 'user_details.user_id')
                   ->orderBy('address', $direction)
                   ->select('users.*');
   }
   

Aliasing Columns

1. Model Setup:

   public $sortableAs = ['nick_name'];
   

2. Controller:

   $users = User::select(['name as nick_name'])->sortable(['nick_name'])->paginate(10);
   

3. Blade:

   @sortablelink('nick_name', 'Nickname')
   

Default Sorting

Set default sorting in config/columnsortable.php:

'default' => [
    'column' => 'name',
    'direction' => 'asc',
],

Gotchas and Tips

Common Pitfalls

1. Invalid Relation Names:

   • Error: ColumnSortableException with code 1 if the relation doesn’t exist.
   • Fix: Ensure relation methods (e.g., hasOne, belongsTo) are correctly defined in the model.

2. Malformed Sort URLs:

   • Error: ColumnSortableException with code 0 if the sort parameter is malformed (e.g., sort=column..subcolumn).
   • Fix: Validate user input or sanitize the sort parameter in the controller.

3. Column Existence Checks:

   • Issue: Package checks Schema::hasColumn() if $sortable isn’t defined, adding overhead.
   • Fix: Always define $sortable in your model to avoid runtime checks.

4. Font Awesome Mismatch:

   • Issue: Icons may not render correctly if using Font Awesome 5 but config is set for FA 4.
   • Fix: Update asc_suffix and desc_suffix in config/columnsortable.php:

     'asc_suffix'  => '-up',
     'desc_suffix' => '-down',
     

5. Pagination Conflicts:

   • Issue: Sorting may break pagination if appends() doesn’t preserve all query parameters.
   • Fix: Use \Request::except('page') to exclude only the page parameter:

     {!! $users->appends(\Request::except('page'))->render() !!}
     

Debugging Tips

1. Log Sortable Queries: Temporarily add ->toSql() to debug generated queries:

   $query = User::sortable('name')->toSql();
   

2. Check Config Values: Verify config/columnsortable.php for custom separators or icon classes:

   'uri_relation_column_separator' => '.',
   

3. Test Relation Sorting: Ensure relations are eager-loaded to avoid N+1 queries:

   $users = User::with('detail')->sortable()->paginate(10);
   

Extension Points

1. Custom Sortable Logic: Override the sortable() method in your model for bespoke behavior:

   public function sortable($columns = null) {
       return $this->orderBy('custom_logic', 'asc')->getQuery();
   }
   

2. Dynamic $sortable: Populate $sortable dynamically based on user roles or permissions:

   public $sortable = function() {
       return auth()->user()->can('admin') ? ['id', 'name', 'email', 'deleted_at'] : ['id', 'name'];
   };
   

3. Global Middleware: Add middleware to validate or transform sort parameters:

   public function handle($request, Closure $next) {
       $request->merge(['sort' => $this->sanitizeSort($request->sort)]);
       return $next($request);
   }
   

4. Localization: Customize the @sortablelink title formatting by extending the Blade directive:

   Blade::extend(function($view) {
       $view->share('sortableTitle', function($title) {
           return trans('labels.' . $title);
       });
   });
   

Performance Tips

1. Avoid Schema::hasColumn: Define $sortable statically to skip runtime checks.

2. Select Specific Columns: Reduce query size by selecting only needed columns:

   $users = User::select(['id', 'name', 'email'])->sortable()->paginate(10);
   

3. Cache Config: Publish and cache the config to avoid repeated file reads:

   php artisan config:cache
   

4. Use withCount Efficiently: For withCount() sorting, alias columns to avoid ambiguity:

   $users = User::withCount('posts')->select(['id', 'name', 'posts_count'])->sortable(['posts_count'])->paginate(10);