Doctrine Utils Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require ecommit/doctrine-utils
   

   Ensure your Laravel project uses Doctrine DBAL or Doctrine ORM (via doctrine/dbal or doctrine/orm packages).

2. First Use Case: Counting Results (ORM Example):

   use Ecommit\DoctrineUtils\Paginator\DoctrinePaginatorBuilder;
   use Doctrine\ORM\EntityManagerInterface;
   
   $queryBuilder = $entityManager->getRepository(Product::class)->createQueryBuilder('p');
   $count = DoctrinePaginatorBuilder::countQueryBuilder([
       'query_builder' => $queryBuilder,
       'behavior' => 'orm' // Default, leverages Doctrine's native counting
   ]);
   

   Pagination (DBAL Example):

   use Ecommit\DoctrineUtils\Paginator\DoctrineDBALPaginator;
   use Doctrine\DBAL\Query\QueryBuilder;
   
   $queryBuilder = $connection->createQueryBuilder();
   $paginator = new DoctrineDBALPaginator([
       'query_builder' => $queryBuilder,
       'page' => 1,
       'max_per_page' => 20
   ]);
   $results = $paginator->getResults();
   

3. Where to Look First:

   • Documentation: Start with count.md and paginator.md for core use cases.
   • Filters: Explore filters.md for dynamic query filtering (e.g., QueryBuilderFilter).
   • API: Reference PaginatorInterface for consistent pagination methods.

Implementation Patterns

1. Pagination Workflow

ORM Integration:

// In a Laravel controller/service
public function index(EntityManagerInterface $em, Request $request)
{
    $qb = $em->getRepository(Product::class)->createQueryBuilder('p');
    $paginator = new DoctrineORMPaginator([
        'query_builder' => $qb,
        'page' => (int) $request->query('page', 1),
        'max_per_page' => 15,
        'count' => ['behavior' => 'orm'] // Auto-counts results
    ]);

    return view('products.index', [
        'products' => $paginator->getResults(),
        'paginator' => $paginator // For pagination links
    ]);
}

DBAL Integration:

public function listUsers(Connection $connection, Request $request)
{
    $qb = $connection->createQueryBuilder();
    $paginator = new DoctrineDBALPaginator([
        'query_builder' => $qb,
        'page' => $request->query('page', 1),
        'max_per_page' => 10,
        'by_identifier' => 'id' // Optimize for large datasets
    ]);

    return $paginator->getResults();
}

2. Dynamic Filtering

Use QueryBuilderFilter for reusable conditions:

use Ecommit\DoctrineUtils\QueryBuilderFilter;

$filter = new QueryBuilderFilter($qb);
$filter->add('p.price > :min', ['min' => 100]); // Add WHERE clause
$filter->add('p.category = :category', ['category' => 'Electronics']);

Laravel Service Container: Bind the filter to Laravel’s IoC:

// config/app.php
'bindings' => [
    Ecommit\DoctrineUtils\QueryBuilderFilter::class => function ($app) {
        return new QueryBuilderFilter($app->make(QueryBuilder::class));
    },
];

3. Performance Optimization

• by_identifier: For large datasets, use by_identifier to fetch only IDs first, then hydrate:

  $paginator = new DoctrineORMPaginator([
      'query_builder' => $qb,
      'by_identifier' => 'id' // Reduces memory usage
  ]);
  

• Simplified Counting: Disable ORDER BY for counts:

  $count = DoctrinePaginatorBuilder::countQueryBuilder([
      'query_builder' => $qb,
      'behavior' => 'orm',
      'simplified_request' => true
  ]);
  

4. Integration with Laravel Eloquent

Bridge Doctrine ORM with Eloquent (if needed):

// Convert Eloquent Query Builder to Doctrine ORM QB
$doctrineQb = $entityManager->getConnection()->getQueryBuilder();
$eloquentQb = Product::query()->getQuery();
// Manually translate conditions (e.g., using QueryBuilderFilter)

Gotchas and Tips

Pitfalls

1. ORM vs. DBAL Confusion:

   • Error: Using DoctrineDBALPaginator with an ORM QueryBuilder.
   • Fix: Match paginator class to your query builder type (e.g., DoctrineORMPaginator for ORM).

2. Counting Behavior Quirks:

   • Issue: count_by_sub_request requires a connection parameter but may fail silently.
   • Debug: Check if the connection is properly injected:

     $count = DoctrinePaginatorBuilder::countQueryBuilder([
         'query_builder' => $qb,
         'behavior' => 'count_by_sub_request',
         'connection' => $entityManager->getConnection() // Explicitly pass
     ]);
     

3. by_identifier Limitations:

   • Problem: Only works with primary keys or unique identifiers.
   • Workaround: Use composite keys with DISTINCT ON (PostgreSQL) or custom logic.

4. Laravel Caching:

   • Caveat: Paginated results aren’t cached by default. Use Laravel’s cache middleware:

     Route::get('/products', function () {
         return Cache::remember('products_page_1', now()->addHours(1), function () {
             return $paginator->getResults();
         });
     });
     

Debugging Tips

1. Log QueryBuilders:

   $qb->getSQL(); // Inspect raw SQL
   $qb->getParameters(); // Check bound parameters
   

2. Enable Doctrine Logging:

   $entityManager->getConnection()->getConfiguration()->setSQLLogger(new \Doctrine\DBAL\Logging\EchoSQLLogger());
   

3. Validate Count Options:

   • Ensure alias is provided for count_by_alias:

     $count = DoctrinePaginatorBuilder::countQueryBuilder([
         'query_builder' => $qb,
         'behavior' => 'count_by_alias',
         'alias' => 'p' // Must match your query alias
     ]);
     

Extension Points

1. Custom Paginator: Extend DoctrineORMPaginator to add Laravel-specific features:

   class LaravelDoctrinePaginator extends DoctrineORMPaginator
   {
       public function toJson()
       {
           return response()->json([
               'data' => $this->getResults(),
               'meta' => [
                   'total' => $this->getTotalResults(),
                   'page' => $this->getCurrentPage()
               ]
           ]);
       }
   }
   

2. Filter Chaining: Create a fluent interface for QueryBuilderFilter:

   $filter->where('p.price > :min')->andWhere('p.category = :category');
   

3. Hybrid Pagination: Combine with Laravel’s LengthAwarePaginator:

   $items = $paginator->getResults();
   $paginated = new LengthAwarePaginator(
       $items,
       $paginator->getTotalResults(),
       $paginator->getMaxPerPage(),
       $paginator->getCurrentPage(),
       ['path' => LaravelPagination::resolveCurrentPath()]
   );
   

Configuration Quirks

1. Default Values:

   • max_per_page defaults to 100 (adjust in your paginator constructor).
   • simplified_request is true for ORM unless by_identifier is used.

2. Connection Handling:

   • For DBAL, ensure the connection parameter is passed when using count_by_sub_request.
   • For ORM, the EntityManager connection is auto-resolved.

3. Distinct Behavior:

   • distinct_alias is true by default for count_by_alias. Set to false for non-distinct counts:

     'distinct_alias' => false