Datatables Bundle Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require omines/datatables-bundle
   

   Ensure DataTablesBundle is registered in config/bundles.php (Symfony Flex handles this automatically).

2. Assets:

   php bin/console assets:install
   

   Include DataTables CSS/JS in your template (see README).

3. First Controller:

   use Omines\DataTablesBundle\DataTableFactory;
   use Omines\DataTablesBundle\Column\TextColumn;
   
   public function index(Request $request, DataTableFactory $factory)
   {
       $table = $factory->create()
           ->add('name', TextColumn::class)
           ->createAdapter(\Omines\DataTablesBundle\Adapter\ArrayAdapter::class, [
               ['name' => 'Alice'], ['name' => 'Bob']
           ])
           ->handleRequest($request);
   
       return $this->render('index.html.twig', ['table' => $table]);
   }
   

4. Frontend:

   <div id="users"></div>
   <script>
       $(function() {
           $('#users').initDataTables({{ datatable_settings(table) }});
       });
   </script>
   

Implementation Patterns

1. Column Configuration

• Basic Columns:

  $table->add('name', TextColumn::class);
  

• Custom Fields:

  $table->add('fullName', TextColumn::class, [
      'field' => 'firstName', // Override field name
      'label' => 'Full Name', // Custom label
  ]);
  

• Nested Relationships (Doctrine ORM):

  $table->add('company', TextColumn::class, [
      'field' => 'company.name', // Dot notation for relationships
  ]);
  

2. Adapter Workflows

• ArrayAdapter (Simple Arrays):

  $table->createAdapter(ArrayAdapter::class, [['id' => 1, 'name' => 'Foo']]);
  

• Doctrine ORM (Entities):

  $table->createAdapter(\Omines\DataTablesBundle\Adapter\Doctrine\ORMAdapter::class, [
      'entity' => User::class,
      'queryBuilder' => $customQueryBuilder, // Optional
  ]);
  

• Elastica (Elasticsearch):

  $table->createAdapter(\Omines\DataTablesBundle\Adapter\ElasticaAdapter::class, [
      'client' => $elasticaClient,
      'index' => 'users',
  ]);
  

3. Request Handling

• Automatic Callback Handling:

  $table->handleRequest($request);
  if ($table->isCallback()) {
      return $table->getResponse(); // Return JSON for AJAX
  }
  

• Custom Criteria:

  $table->setCriteriaProvider(new CustomCriteriaProvider());
  

4. Twig Integration

• Pass Table to Template:

  return $this->render('template.html.twig', ['table' => $table]);
  

• Override Default Template:

  # config/packages/datatables.yaml
  datatables:
      template: 'AppBundle:DataTables:custom_template.html.twig'
  

5. Pagination & Sorting

• Default Sorting:

  $table->addOrderBy('name', DataTable::SORT_DESCENDING);
  

• Server-Side Processing: Enable in DataTables JS:

  initDataTables({ processing: true, serverSide: true });
  

Gotchas and Tips

Pitfalls

1. Doctrine ORM Joins:

   • If using nested fields (e.g., user.company.name), ensure the relationship is properly mapped in Doctrine. The AutomaticQueryBuilder may fail silently if joins are missing.
   • Fix: Explicitly define joins in a custom QueryBuilder:

     $qb = $entityManager->createQueryBuilder()
         ->leftJoin('u.company', 'c');
     $table->setQueryBuilder($qb);
     

2. Case Sensitivity in Search:

   • By default, SearchCriteriaProvider performs case-sensitive searches. Use LIKE with LOWER() for case-insensitive:

     $table->setCriteriaProvider(new CustomCriteriaProvider([
         'search' => function ($queryBuilder, $search) {
             return $queryBuilder->andWhere("LOWER(u.name) LIKE LOWER(:search)")
                 ->setParameter('search', "%$search%");
         }
     ]));
     

3. Circular References in JSON:

   • If using ArrayAdapter with complex objects, ensure no circular references exist (e.g., bidirectional relationships). Use json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) or a custom serializer.

4. Twig datatable_settings:

   • If datatable_settings(table) fails, verify:
     • The DataTable object is passed correctly to the template.
     • No circular references in the table’s configuration (e.g., recursive column definitions).

5. Elastica Pagination:

   • Elasticsearch’s from/size pagination differs from SQL. Ensure your ElasticaAdapter uses search() with from/size:

     $results = $client->getIndex('users')->search([
         'from' => $start,
         'size' => $length,
         'query' => $query,
     ]);
     

Debugging Tips

1. Log Raw Requests: Dump the DataTables request payload to debug issues:

   $requestData = json_decode($request->getContent(), true);
   file_put_contents('debug/datatables.json', json_encode($requestData));
   

2. QueryBuilder Debugging: For Doctrine ORM, log the generated SQL:

   $qb = $entityManager->getConnection()->getWrappedConnection()->prepare('...');
   $qb->execute();
   var_dump($qb->getSQL());
   

3. Adapter-Specific Issues:

   • ArrayAdapter: Validate input data structure matches column fields.
   • Doctrine ORM: Check for NotFoundException if the entity class is incorrect.
   • Elastica: Verify the index exists and mappings match your fields.

Extension Points

1. Custom Adapters: Implement Omines\DataTablesBundle\Adapter\AdapterInterface for new data sources (e.g., Redis, GraphQL):

   class RedisAdapter implements AdapterInterface {
       public function count($criteria) { ... }
       public function fetch($criteria) { ... }
   }
   

2. Criteria Providers: Extend Omines\DataTablesBundle\Criteria\CriteriaProviderInterface to modify search/sort logic:

   class CustomCriteriaProvider implements CriteriaProviderInterface {
       public function getCriteria(DataTable $table, Request $request) {
           $criteria = parent::getCriteria($table, $request);
           $criteria['customFilter'] = $request->query->get('filter');
           return $criteria;
       }
   }
   

3. Column Types: Create reusable column classes (e.g., DateColumn, BooleanColumn):

   class DateColumn extends TextColumn {
       public function getValue($data) {
           return (new \DateTime($data))->format('Y-m-d');
       }
   }
   

4. Twig Extensions: Override datatable_settings in your bundle’s Twig extension:

   $twig->addFunction(new \Twig\TwigFunction('custom_datatable_settings', function ($table) {
       return json_encode($table->getOptions() + ['customOption' => true]);
   }));
   

Performance Tips

1. Doctrine ORM:

   • Use DISTINCT for duplicate rows:

     $qb->distinct();
     

   • Limit loaded fields with select() to avoid N+1 queries:

     $qb->select('u.id', 'u.name', 'c.name AS company_name');
     

2. Elastica:

   • Use searchType: 'count' for large datasets to avoid fetching all results.
   • Cache frequent queries with Elastica\Scroll.

3. ArrayAdapter:

   • For large arrays, implement pagination manually:

     $adapter->setData(array_slice($data, $start, $length));