Doctrine Query Creator Laravel Package

Getting Started

Minimal Setup

1. Installation Add the package via Composer:

   composer require dualmedia/doctrine-query-creator
   

   Ensure your project uses Doctrine ORM (Laravel's Eloquent is not compatible—use native Doctrine entities).

2. First Use Case Convert a simple criteria array into a QueryBuilder:

   use Dualmedia\DoctrineQueryCreator\QueryCreator;
   
   $criteria = [
       'where' => [
           ['field' => 'name', 'operator' => '=', 'value' => 'John'],
           ['field' => 'age', 'operator' => '>', 'value' => 30],
       ],
       'orderBy' => ['field' => 'createdAt', 'direction' => 'DESC'],
   ];
   
   $queryCreator = new QueryCreator($entityManager->getClassMetadata('App\Entity\User'));
   $queryBuilder = $queryCreator->createQueryBuilder($criteria);
   
   $results = $queryBuilder->getQuery()->getResult();
   

3. Where to Look First

   • QueryCreator class: Core logic for building queries.
   • createQueryBuilder() method: Accepts criteria arrays and returns a QueryBuilder.
   • README’s "Usage" section: Example criteria structure and edge cases.

Implementation Patterns

Common Workflows

1. Dynamic Filtering Use criteria arrays to build flexible queries (e.g., admin panels, search APIs):

   $criteria = [
       'where' => [
           ['field' => 'status', 'operator' => 'IN', 'value' => ['active', 'pending']],
           ['field' => 'deletedAt', 'operator' => 'IS NULL'],
       ],
   ];
   

2. Integration with Laravel Controllers Wrap the package in a service class to abstract Doctrine usage:

   class UserQueryService {
       public function __construct(private EntityManager $em) {}
   
       public function findByCriteria(array $criteria): array {
           $creator = new QueryCreator($this->em->getClassMetadata(User::class));
           return $creator->createQueryBuilder($criteria)->getQuery()->getResult();
       }
   }
   

3. Pagination Support Extend the criteria to include limit/offset:

   $criteria = [
       'limit' => 10,
       'offset' => 20,
       // ... other criteria
   ];
   $queryBuilder = $queryCreator->createQueryBuilder($criteria);
   

4. Reusable Criteria Builders Create helper methods for common queries:

   public function getActiveUsersCriteria(): array {
       return [
           'where' => [['field' => 'status', 'operator' => '=', 'value' => 'active']],
           'orderBy' => [['field' => 'name', 'direction' => 'ASC']],
       ];
   }
   

Integration Tips

• Validation: Validate criteria arrays before passing them to QueryCreator to avoid runtime errors.
• Performance: Use DISTINCT in criteria for large datasets:

  $criteria = ['distinct' => true, /* ... */];
  

• Joins: Support complex joins by extending the criteria structure:

  $criteria = [
      'joins' => [
          ['join' => 'user.orders', 'alias' => 'o', 'condition' => 'o.status = :status'],
      ],
      'parameters' => ['status' => 'shipped'],
  ];
  

Gotchas and Tips

Pitfalls

1. Entity Metadata Dependency

   • Issue: QueryCreator requires ClassMetadata upfront. If the entity changes (e.g., new fields), queries may fail.
   • Fix: Use a factory or cache metadata:

     $metadata = $em->getMetadataFactory()->getMetadataFor('App\Entity\User');
     

2. Operator Limitations

   • Issue: Not all Doctrine operators (e.g., LIKE, BETWEEN) are supported by default.
   • Fix: Extend the package or use raw DQL:

     $queryBuilder->andWhere('u.name LIKE :name');
     $queryBuilder->setParameter('name', '%John%');
     

3. Case Sensitivity

   • Issue: Field names in criteria must match the entity’s metadata exactly (e.g., created_at vs. createdAt).
   • Fix: Normalize field names or use the entity’s property names directly.

4. Nested Criteria

   • Issue: The package doesn’t natively support nested criteria (e.g., subqueries).
   • Workaround: Build subqueries manually and merge them into the QueryBuilder.

Debugging Tips

• Enable SQL Logging

  $queryBuilder->getQuery()->getSQL(); // Inspect the generated SQL
  

• Validate Criteria Add a helper to dump criteria before processing:

  dd($criteria); // Check for typos or invalid operators
  

Extension Points

1. Custom Operators Extend the QueryCreator to handle custom logic:

   $queryCreator->addCustomOperator('CUSTOM', function ($field, $value) use ($qb) {
       $qb->andWhere("UPPER($field) = UPPER(:value)");
       $qb->setParameter('value', $value);
   });
   

2. Plugin System Create a plugin interface to add pre/post-processing:

   interface QueryPlugin {
       public function apply(QueryBuilder $qb, array $criteria);
   }
   

3. Laravel-Specific Adaptations

   • Eloquent Bridge: If you must use Eloquent, wrap the package in a trait to convert Eloquent queries to Doctrine:

     use Doctrine\ORM\QueryBuilder;
     $queryBuilder = $this->getDoctrineQueryBuilder();
     

   • Service Provider: Register the QueryCreator as a singleton for global access.

Config Quirks

• Default Values: The package uses sensible defaults (e.g., AND for where clauses), but override them in criteria if needed:

  $criteria = [
      'where' => [
          ['field' => 'name', 'operator' => '=', 'value' => 'John'],
          ['field' => 'age', 'operator' => '>', 'value' => 30, 'group' => 'OR'], // Custom group
      ],
  ];