Doctrine Dql Operator Laravel Package

Getting Started

Minimal Setup

1. Install the package:

   composer require denisok94/doctrine-dql-operator
   

2. Configure Doctrine in config/packages/doctrine.yaml:

   doctrine:
       dbal:
           types:
               timestampt: Denisok94\DoctrineDqlOperator\DBAL\Timestampt
               timestamptz: Denisok94\DoctrineDqlOperator\DBAL\Timestamptz
               money: Denisok94\DoctrineDqlOperator\DBAL\MoneyType
       orm:
           dql:
               datetime_functions:
                   DATE: Denisok94\DoctrineDqlOperator\DQL\Date
                   DATE_TRUNC: Denisok94\DoctrineDqlOperator\DQL\DateTrunc
                   EXTRACT: Denisok94\DoctrineDqlOperator\DQL\Extract
                   DATE_PART: Denisok94\DoctrineDqlOperator\DQL\DatePart
               numeric_functions:
                   COALESCE: Denisok94\DoctrineDqlOperator\DQL\CoalesceFunction
               string_functions:
                   CAST: Denisok94\DoctrineDqlOperator\DQL\Cast
                   TO_CHAR: Denisok94\DoctrineDqlOperator\DQL\ToChar
               jsonb_functions:
                   JSONB_AGG: Denisok94\DoctrineDqlOperator\DQL\JsonbAgg
                   JSONB_HSTORE: Denisok94\DoctrineDqlOperator\DQL\JsonbHstore
                   JSONB_EX: Denisok94\DoctrineDqlOperator\DQL\JsonbEx
                   JSONB_IN: Denisok94\DoctrineDqlOperator\DQL\JsonbIn
   

3. First Use Case: Query a timestamp field with DATE_TRUNC:

   $qb = $entityManager->createQueryBuilder();
   $qb->select('u')
      ->from(User::class, 'u')
      ->where('DATE_TRUNC("u.createdAt", \'day\') = :date')
      ->setParameter('date', new \DateTime('2024-01-01'));
   

Implementation Patterns

Common Workflows

1. Date/Time Manipulation: Use DATE_TRUNC for grouping by time periods (day/month/year):

   $qb->select('DATE_TRUNC("o.orderDate", \'month\') as month, COUNT(o.id)')
      ->from(Order::class, 'o')
      ->groupBy('month');
   

2. JSONB Aggregations: Aggregate JSON data with JSONB_AGG:

   $qb->select('JSONB_AGG(JSONB_BUILD_OBJECT(\'id\', p.id, \'name\', p.name)) as products')
      ->from(Product::class, 'p')
      ->where('p.category = :category');
   

3. Type-Specific Queries: Filter timestampt/timestamptz fields:

   $qb->andWhere('u.lastLogin > :cutoff')
      ->setParameter('cutoff', new \DateTimeImmutable('2024-01-01'));
   

4. Coalesce for Null Handling: Replace NULL values in queries:

   $qb->select('COALESCE(u.discount, 0) as discount')
      ->from(User::class, 'u');
   

Integration Tips

• Custom DQL Functions: Extend the package by adding new functions to doctrine.orm.dql.*_functions in config.
• Entity Mapping: Use timestampt/timestamptz for PostgreSQL-specific timestamp types in @ORM\Column:

  /**
   * @ORM\Column(type="timestamptz")
   */
  private $createdAt;
  

• QueryBuilder Shortcuts: Create reusable methods for complex queries:

  public function getMonthlySales(QueryBuilder $qb, string $entityAlias) {
      return $qb->addSelect('DATE_TRUNC("' . $entityAlias . '.saleDate", \'month\') as month')
                ->addSelect('SUM("' . $entityAlias . '.amount) as total');
  }
  

Gotchas and Tips

Pitfalls

1. PostgreSQL-Specific:

   • timestampt/timestamptz and JSONB functions only work with PostgreSQL. Ensure your DBAL platform is configured correctly:

     doctrine:
         dbal:
             platform_service_name: postgresql
     

   • Test queries in a PostgreSQL-compatible environment first.

2. Case Sensitivity:

   • DQL function names in queries must match the config exactly (e.g., DATE_TRUNC, not date_trunc).

3. Parameter Binding:

   • Avoid binding dynamic SQL snippets (e.g., table/column names) to parameters. Use string concatenation for these:

     // ❌ Avoid
     $qb->where('DATE_TRUNC(:field, \'day\') = :date');
     
     // ✅ Correct
     $qb->where('DATE_TRUNC("user.createdAt", \'day\') = :date');
     

4. Money Type:

   • The money type requires PostgreSQL’s money type. Migrate existing columns if needed:

     ALTER TABLE orders ALTER COLUMN amount TYPE money USING amount::money;
     

Debugging

• Enable SQL Logging:

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

  Verify generated SQL matches expectations (e.g., DATE_TRUNC syntax).

• Check Platform Compatibility: If queries fail, confirm your DBAL\Platform is PostgreSQLPlatform:

  $platform = $entityManager->getConnection()->getDatabasePlatform();
  

Extension Points

1. Add Custom Functions: Extend the package by creating a new DQL function class (e.g., CustomFunction) and register it in doctrine.orm.dql.numeric_functions:

   doctrine:
       orm:
           dql:
               numeric_functions:
                   CUSTOM_FUNC: App\DQL\CustomFunction
   

2. Override Existing Functions: Replace default implementations by redefining the config key (e.g., for COALESCE):

   doctrine:
       orm:
           dql:
               numeric_functions:
                   COALESCE: App\DQL\CustomCoalesce
   

3. Type Mapping: Create custom DBAL types by extending Denisok94\DoctrineDqlOperator\DBAL\AbstractType for unsupported PostgreSQL types.

Performance Tips

• Indexing: Ensure columns used in DATE_TRUNC/EXTRACT are indexed for large datasets:

  CREATE INDEX idx_orders_truncated_date ON orders (DATE_TRUNC(created_at, 'day'));
  

• Batch Processing: Use JSONB_AGG for aggregating large JSON datasets in a single query instead of client-side processing.