Doctrine Orm Adapter Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require pagerfanta/doctrine-orm-adapter
   

   Ensure pagerfanta/pagerfanta is also installed (dependency).

2. Basic Usage

   use Pagerfanta\Pagerfanta;
   use Pagerfanta\Adapter\DoctrineORMAdapter;
   
   $queryBuilder = $entityManager->createQueryBuilder()
       ->select('u')
       ->from('App\Entity\User', 'u');
   
   $adapter = new DoctrineORMAdapter($queryBuilder);
   $pagerfanta = new Pagerfanta(new DoctrineORMAdapter($queryBuilder));
   $pagerfanta->setMaxPerPage(10); // Items per page
   

3. First Render

   // In a controller
   $pagerfanta = new Pagerfanta(new DoctrineORMAdapter($queryBuilder));
   $pagerfanta->setCurrentPage($request->query->getInt('page', 1));
   
   return $this->renderPagination($pagerfanta);
   

Where to Look First

• Docs: Pagerfanta Doctrine ORM Adapter (check the main Pagerfanta repo for broader concepts).
• Source: vendor/pagerfanta/doctrine-orm-adapter/src/Adapter.php for edge cases.
• Use Case: Replace native Doctrine pagination with Pagerfanta for advanced features (e.g., custom views, multi-source pagination).

Implementation Patterns

Workflows

1. QueryBuilder Integration

   // Standard usage with QueryBuilder
   $qb = $em->createQueryBuilder()
       ->select('p')
       ->from('App\Entity\Product', 'p')
       ->where('p.active = :active')
       ->setParameter('active', true);
   
   $adapter = new DoctrineORMAdapter($qb);
   $pager = new Pagerfanta($adapter);
   

2. Dynamic Filtering

   // Apply filters before pagination
   $qb = $em->createQueryBuilder()
       ->select('u')
       ->from('App\Entity\User', 'u');
   
   if ($request->has('search')) {
       $qb->andWhere('u.name LIKE :search')
          ->setParameter('search', "%{$request->get('search')}%");
   }
   
   $adapter = new DoctrineORMAdapter($qb);
   

3. Multi-Source Pagination

   // Combine multiple QueryBuilders (e.g., for joins or unions)
   $qb1 = $em->createQueryBuilder()->select('u')->from('App\Entity\User', 'u');
   $qb2 = $em->createQueryBuilder()->select('a')->from('App\Entity\Admin', 'a');
   
   $adapter = new DoctrineORMAdapter(
       $em->createQueryBuilder()
           ->select('u')
           ->from('(' . $qb1->getQuery()->getSQL() . ') as u')
           ->unionAll('(' . $qb2->getQuery()->getSQL() . ')')
   );
   

4. Lazy-Loading with Twig

   {% for user in pagerfanta %}
       {{ user.name }}
   {% endfor %}
   
   {{ knp_pagination_render(pagerfanta) }}
   

   (Use knplabs/knp-paginator-bundle for Twig integration.)

Integration Tips

• Symfony Dependency Injection:

  # config/services.yaml
  services:
      App\Service\PaginationService:
          arguments:
              $entityManager: '@doctrine.orm.entity_manager'
  

  // In service
  public function __construct(EntityManagerInterface $em) {
      $this->em = $em;
  }
  
  public function getPaginatedUsers(int $page, int $limit) {
      $qb = $this->em->createQueryBuilder()->select('u')->from('App\Entity\User', 'u');
      $adapter = new DoctrineORMAdapter($qb);
      $pager = new Pagerfanta($adapter);
      $pager->setMaxPerPage($limit)->setCurrentPage($page);
      return $pager;
  }
  

• API Responses:

  return response()->json([
      'data' => $pagerfanta->getCurrentPageResults(),
      'meta' => [
          'total' => $pagerfanta->getNbResults(),
          'pages' => $pagerfanta->getNbPages(),
          'current_page' => $pagerfanta->getCurrentPage(),
      ],
  ]);
  

Gotchas and Tips

Pitfalls

1. Count Query Mismatch

   • Pagerfanta runs a COUNT query separately. If your QueryBuilder has complex logic (e.g., DISTINCT, subqueries), ensure the count matches the actual results.
   • Fix: Use ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'Gedmo\\Sortable\\Query\\TreeWalker\\CountableWalker') if needed (for Gedmo extensions).

2. Memory Leaks with Large Datasets

   • Doctrine ORM loads all results into memory for counting. For large datasets (>10k records), use database-level pagination:

     $qb->setFirstResult($pagerfanta->getCurrentPage() * $pagerfanta->getMaxPerPage())
        ->setMaxResults($pagerfanta->getMaxPerPage());
     $adapter = new DoctrineORMAdapter($qb, false); // Disable count query
     

3. Detached Entities

   • If entities are detached after pagination (e.g., in a service), reattach them:

     $em->refresh($entity); // For single entity
     // OR
     $em->clear(); // Clear the entity manager (use cautiously)
     

4. Caching Issues

   • Pagerfanta caches results. Clear cache if data changes dynamically:

     $pagerfanta->setCache($cache); // Inject a PSR-6 cache
     $pagerfanta->clearCache();
     

Debugging

• Verify Count Query:

  $countQb = clone $qb;
  $countQb->select('COUNT(u.id)');
  $count = $countQb->getQuery()->getSingleScalarResult();
  var_dump($count); // Compare with Pagerfanta's count
  

• Log SQL:

  $qb->getQuery()->setSQLLogger(new \Doctrine\DBAL\Logging\EchoSQLLogger());
  

Extension Points

1. Custom Adapter Logic Override DoctrineORMAdapter for custom behavior:

   class CustomAdapter extends DoctrineORMAdapter {
       public function getNbResults() {
           // Custom count logic
           return $this->queryBuilder->getQuery()->getSingleScalarResult();
       }
   }
   

2. Event Listeners Use Pagerfanta events to modify pagination:

   $pagerfanta->addListener('post.initialize', function (Pagerfanta $pager) {
       if ($pager->getCurrentPage() > 10) {
           $pager->setCurrentPage(10);
       }
   });
   

3. Hybrid Pagination Combine with native Doctrine pagination for edge cases:

   $qb = $em->createQueryBuilder()
       ->select('u')
       ->from('App\Entity\User', 'u')
       ->setFirstResult($offset)
       ->setMaxResults($limit);
   
   $adapter = new DoctrineORMAdapter($qb, false); // Disable count
   $pager = new Pagerfanta($adapter);
   $pager->setMaxPerPage($limit);
   $pager->setCurrentPage(($offset / $limit) + 1);
   

Config Quirks

• Doctrine DQL vs. SQL: Pagerfanta works with DQL (Doctrine Query Language). Avoid raw SQL unless wrapped in a QueryBuilder.
• Parameter Binding: Ensure all QueryBuilder parameters are bound before passing to the adapter to avoid runtime errors.