Doctrine Query Paginator Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require aelfannir/doctrine-query-paginator
   

   For Symfony Flex projects, this auto-registers the bundle. For non-Flex projects, manually add to config/bundles.php:

   AElfannir\DoctrineQueryPaginator\DoctrineQueryPaginatorBundle::class => ['all' => true],
   

2. First Use Case: Inject the paginator service into a controller/service:

   use AElfannir\DoctrineQueryPaginator\Paginator;
   
   public function __construct(private Paginator $paginator) {}
   
   public function index(Request $request) {
       $queryBuilder = $this->entityManager->getRepository(Entity::class)->createQueryBuilder('e');
       $filters = $this->buildFiltersFromRequest($request); // Custom logic
       $paginator = $this->paginator->paginate($queryBuilder, $filters, $request->query->getInt('page', 1), 10);
       return $this->render('index.html.twig', ['paginator' => $paginator]);
   }
   

3. Key Files:

   • src/Paginator.php: Core logic for pagination and filtering.
   • src/Filter/FilterBuilder.php: Helper for constructing filter objects.

Implementation Patterns

Common Workflows

1. Basic Pagination with Filters

$queryBuilder = $entityManager->getRepository(User::class)->createQueryBuilder('u');
$filters = [
    ['property' => 'name', 'operator' => '=', 'value' => 'John'],
    ['property' => 'active', 'operator' => '!=', 'value' => false]
];
$paginator = $this->paginator->paginate($queryBuilder, $filters, $page, $perPage);

2. Dynamic Filtering from Request

public function buildFilters(Request $request): array {
    $filters = [];
    if ($request->query->has('search')) {
        $filters[] = [
            'property' => 'name',
            'operator' => 'LIKE',
            'value' => '%'.$request->query->get('search').'%'
        ];
    }
    return $filters;
}

3. Compound Filters (AND/OR Logic)

$filters = [
    'AND' => [
        ['property' => 'status', 'operator' => '=', 'value' => 'active'],
        'OR' => [
            ['property' => 'createdAt', 'operator' => '>', 'value' => new \DateTime('-7 days')],
            ['property' => 'updatedAt', 'operator' => '>', 'value' => new \DateTime('-1 day')]
        ]
    ]
];

4. Integration with Symfony Forms

Use the paginator in a form type to enable filtered searches:

$form = $this->createFormBuilder()
    ->add('search', TextType::class, ['required' => false])
    ->getForm();

5. Custom Operators

Extend the Operator class to add domain-specific logic (e.g., CONTAINS for arrays):

use AElfannir\DoctrineQueryPaginator\Filter\Operator;

class ContainsOperator extends Operator {
    public function apply($value, $field, QueryBuilder $qb, $alias) {
        $qb->andWhere($alias.'.'.$field.' LIKE :value')
           ->setParameter('value', '%'.$value.'%');
    }
}

Gotchas and Tips

Pitfalls

1. QueryBuilder Modification:

   • The paginator modifies the QueryBuilder in-place. Avoid reusing the same QueryBuilder instance for unrelated queries after pagination.
   • Fix: Clone the QueryBuilder if needed:

     $clone = clone $queryBuilder;
     $this->paginator->paginate($clone, $filters, $page, $perPage);
     

2. Operator Limitations:

   • Not all Doctrine operators are supported out-of-the-box (e.g., IN for arrays requires custom logic).
   • Workaround: Extend the Operator class or use raw SQL via ExpressionBuilder.

3. Performance with Complex Filters:

   • Deeply nested compound filters can generate inefficient SQL. Test with EXPLAIN in your database.
   • Tip: Use DISTINCT in the QueryBuilder for large datasets:

     $queryBuilder->distinct();
     

4. Case Sensitivity:

   • LIKE operators may behave differently across databases (e.g., MySQL vs. PostgreSQL collations).
   • Tip: Use LOWER() in custom operators for case-insensitive searches:

     $qb->andWhere("LOWER(".$alias.'.'.$field.') LIKE LOWER(:value)')
        ->setParameter('value', '%'.$value.'%');
     

5. Pagination with Joins:

   • Paginating joined tables can lead to incorrect row counts. Use COUNT(DISTINCT) or GROUP BY in subqueries.
   • Example:

     $queryBuilder->select('COUNT(DISTINCT u.id)')
                  ->from('App\Entity\User', 'u')
                  ->leftJoin('u.orders', 'o');
     

Debugging Tips

1. Log Generated SQL: Enable Doctrine SQL logging in config/packages/doctrine.yaml:

   doctrine:
       dbal:
           logging: true
           profiling: true
   

   Check logs for the final query structure.

2. Validate Filters: Use FilterBuilder::validate() to ensure filters are correctly structured before passing to the paginator:

   $this->paginator->getFilterBuilder()->validate($filters);
   

3. Test Edge Cases:

   • Empty filter arrays.
   • Non-existent properties (throws InvalidArgumentException).
   • Circular references in compound filters (e.g., AND/OR nesting loops).

Extension Points

1. Custom Filter Builders: Override FilterBuilder to add domain-specific validation or transformation:

   class CustomFilterBuilder extends FilterBuilder {
       public function transform($filters) {
           // Add custom logic (e.g., convert snake_case to camelCase)
           return parent::transform($filters);
       }
   }
   

2. Pagination Metadata: Extend the Paginator class to add custom metadata (e.g., filter summary):

   class ExtendedPaginator extends Paginator {
       public function paginate(QueryBuilder $qb, array $filters, int $page, int $limit) {
           $result = parent::paginate($qb, $filters, $page, $limit);
           $result->setFilterSummary($this->buildFilterSummary($filters));
           return $result;
       }
   }
   

3. Integration with API Platform: Use the paginator in ApiFilter or ApiResource classes to enable filtered pagination in GraphQL/REST APIs:

   use AElfannir\DoctrineQueryPaginator\Filter\FilterBuilder;
   
   public function applyToCollection(Collection $collection, QueryBuilder $qb, array $arguments) {
       $filters = $this->filterBuilder->fromArguments($arguments['filters']);
       $this->paginator->applyFilters($qb, $filters);
   }