Laravel Datatables Oracle Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require yajra/laravel-datatables-oracle:"^13"
   

   For Laravel 5.5+, auto-discovery handles providers/aliases. Otherwise, manually register in config/app.php:

   'providers' => [
       Yajra\DataTables\DataTablesServiceProvider::class,
   ],
   'aliases' => [
       'DataTables' => Yajra\DataTables\Facades\DataTables::class,
   ]
   

2. Publish Config (optional):

   php artisan vendor:publish --provider="Yajra\DataTables\DataTablesServiceProvider"
   

3. First Use Case: Create a route and controller method:

   use Yajra\DataTables\Facades\DataTables;
   
   public function anyData(Request $request)
   {
       return DataTables::eloquent(User::query())->make(true);
   }
   

   Add this to your Blade view:

   <table id="users-table" class="display">
       <thead>
           <tr><th>ID</th><th>Name</th><th>Email</th></tr>
       </thead>
   </table>
   

   Initialize DataTables with AJAX:

   $(function() {
       $('#users-table').DataTable({
           processing: true,
           serverSide: true,
           ajax: '/users/data'
       });
   });
   

Implementation Patterns

Core Workflows

1. Eloquent Integration:

   // Basic usage
   return DataTables::eloquent(User::query())
       ->addColumn('action', function($row) {
           return '<a href="/users/'.$row->id.'">Edit</a>';
       })
       ->make(true);
   
   // Custom query builder
   return DataTables::eloquent(
       User::where('active', 1)->with('roles')
   )->make(true);
   

2. Query Builder Integration:

   return DataTables::query(DB::table('users'))
       ->editColumn('name', function($user) {
           return ucfirst($user->name);
       })
       ->make(true);
   

3. Collection Integration:

   return DataTables::collection(User::all())
       ->addColumn('full_name', function($user) {
           return $user->first_name . ' ' . $user->last_name;
       })
       ->make(true);
   

Advanced Patterns

1. Server-Side Processing:

   return DataTables::eloquent(User::query())
       ->select(['id', 'name', 'email'])
       ->filter(function($query) {
           if ($this->get('search.value')) {
               $query->where(function($q) {
                   $search = $this->get('search.value');
                   $q->where('name', 'like', "%{$search}%")
                     ->orWhere('email', 'like', "%{$search}%");
               });
           }
       })
       ->make(true);
   

2. Column Customization:

   return DataTables::eloquent(User::query())
       ->editColumn('created_at', function($user) {
           return $user->created_at->format('M d, Y H:i');
       })
       ->addColumn('status', function($user) {
           return '<span class="badge '.($user->active ? 'badge-success' : 'badge-danger').'">'.$user->active.'</span>';
       })
       ->make(true);
   

3. Oracle-Specific Features:

   // Use Oracle-specific query builder
   return DataTables::query(DB::connection('oracle')->table('users'))
       ->make(true);
   
   // Handle Oracle date formats
   return DataTables::eloquent(User::query())
       ->editColumn('birth_date', function($user) {
           return \Carbon\Carbon::parse($user->birth_date)->format('Y-m-d');
       })
       ->make(true);
   

4. API Integration:

   // For API responses (without HTML)
   return DataTables::eloquent(User::query())
       ->make(true, 'json');
   

Integration Tips

• Blade Integration:

  {!! DataTables::table(['id', 'name', 'email'], 'users-table') !!}
  

• Resource Controller:

  public function index()
  {
      return DataTables::eloquent(UserResource::collection(User::query()))
          ->make(true);
  }
  

• Livewire/Alpine.js: Use wire:model or x-data to bind DataTables to reactive components.

Gotchas and Tips

Common Pitfalls

1. Oracle-Specific Issues:

   • Date Handling: Oracle dates may require explicit casting:

     ->editColumn('created_at', function($user) {
         return \Carbon\Carbon::createFromFormat('Y-m-d H:i:s', $user->created_at)->format('M d, Y');
     })
     

   • Case Sensitivity: Oracle queries are case-sensitive by default. Use ILIKE for case-insensitive searches:

     ->filter(function($query) {
         $query->where('name', 'ILIKE', "%{$this->get('search.value')}%");
     })
     

2. Performance:

   • N+1 Queries: Always eager-load relations:

     DataTables::eloquent(User::with('roles')->query())
     

   • Select Clause: Explicitly define columns to reduce payload:

     ->select(['id', 'name', 'email'])
     

3. Debugging:

   • Enable APP_DEBUG=true to log queries and inputs.
   • Check for ambiguous column names (common in Oracle joins):

     ->select(['users.id', 'users.name as user_name', 'roles.name as role_name'])
     

4. Configuration Quirks:

   • Column Aliases: Oracle may require explicit aliasing:

     ->addColumn('full_name', function($user) {
         return $user->first_name . ' ' . $user->last_name;
     })->setColumnAlias('full_name', 'FULL_NAME');
     

   • Reserved Words: Escape Oracle reserved words (e.g., order, user):

     ->select(['"order" as order_id'])
     

Extension Points

1. Custom Request Handling: Override the request class to add custom logic:

   use Yajra\DataTables\DataTables;
   
   DataTables::of(User::query())
       ->setRequest($request)
       ->addCustom('custom_field', function($query) {
           $query->where('custom_field', $request->input('custom_field'));
       });
   

2. Middleware Integration: Protect DataTables routes with middleware:

   Route::get('/admin/users', [UserController::class, 'index'])
       ->middleware('auth.admin')
       ->name('admin.users');
   

3. Event Listeners: Listen to DataTables events for logging or analytics:

   DataTables::listen('pre-query', function($query, $request) {
       \Log::info('DataTables query', ['query' => $query->toSql(), 'bindings' => $query->getBindings()]);
   });
   

4. Testing: Use TestingMode for unit tests:

   use Yajra\DataTables\TestingMode;
   
   public function testDataTable()
   {
       TestingMode::enabled();
       $response = $this->get('/users/data');
       TestingMode::disabled();
       $response->assertJsonStructure(['data', 'recordsTotal', 'recordsFiltered']);
   }
   

Pro Tips

• Dynamic Columns: Use getColumns() to dynamically define columns based on user roles:

  $columns = auth()->user()->can('view_advanced') ? ['id', 'name', 'email', 'created_at'] : ['id', 'name'];
  return DataTables::eloquent(User::query())->make(true, 'json')->setColumns($columns);
  

• Caching: Cache frequent queries:

  return DataTables::eloquent(Cache::remember('users_datatable', 60, function() {
      return User::query();
  }))->make(true);
  

• Localization: Override default messages:

  DataTables::of(User::query())
      ->setLanguage([
          'sEmptyTable' => 'No users found',
          'sProcessing' => 'Loading...',
      ]);
  

• Oracle-Specific Optimizations: Use Oracle-specific query hints for large datasets:

  ->modifyQuery(function($query) {
      return $query->toSql() . ' /*+ FIRST_ROWS(100) */';
  });