Doctrine Extensions Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require oro/doctrine-extensions
   

   Add to config/packages/doctrine.yaml (Symfony) or equivalent for your framework:

   doctrine:
       orm:
           dql:
               datetime_functions:
                   date: Oro\ORM\Query\AST\Functions\SimpleFunction
               numeric_functions:
                   timestampdiff: Oro\ORM\Query\AST\Functions\Numeric\TimestampDiff
               string_functions:
                   group_concat: Oro\ORM\Query\AST\Functions\String\GroupConcat
   

2. First Use Case: Query for records where the day of the month is between 10-15:

   $qb = $entityManager->createQueryBuilder();
   $qb->select('u')
      ->from(User::class, 'u')
      ->where('DAY(u.createdAt) BETWEEN :start AND :end')
      ->setParameter('start', 10)
      ->setParameter('end', 15);
   

Where to Look First

• DQL Functions List: README.md#dql-functions-list
• Field Types: README.md#field-types
• Doctrine ORM 3.0 Migration Guide: release/3.0.md

Implementation Patterns

Common Workflows

1. Date/Time Manipulation:

   // Extract year from a date field
   $qb->select('YEAR(u.birthDate) as birthYear')
      ->from(User::class, 'u');
   
   // Calculate age
   $qb->select('TIMESTAMPDIFF(YEAR, u.birthDate, CURRENT_DATE) as age')
      ->from(User::class, 'u');
   

2. Aggregation with GROUP_CONCAT:

   $qb->select('GROUP_CONCAT(DISTINCT u.tags) as userTags')
      ->from(User::class, 'u')
      ->groupBy('u.id');
   

3. Type Casting:

   $qb->select('CAST(u.id as string) as userIdString')
      ->from(User::class, 'u');
   

4. String Operations:

   $qb->select('CONCAT_WS(", ", u.firstName, u.lastName) as fullName')
      ->from(User::class, 'u');
   

Integration Tips

• Custom Functions: Extend Oro\ORM\Query\AST\Functions\SimpleFunction for single-argument functions or create platform-specific implementations for complex logic.
• Field Types: Use MoneyType and PercentType for financial data:

  /**
   * @ORM\Column(type="money")
   */
  private $price;
  

• Platform Awareness: Functions automatically adapt to MySQL/PostgreSQL via platform-specific implementations.

QueryBuilder Usage

// Dynamic date filtering
$qb->andWhere('MONTH(u.createdAt) = :month')
   ->setParameter('month', $month);

// Timezone conversion
$qb->andWhere('CONVERT_TZ(u.eventTime, "+00:00", "+05:30") > :time')
   ->setParameter('time', $time);

Gotchas and Tips

Common Pitfalls

1. Doctrine ORM 3.0 Migration:

   • Update custom function implementations to use new method signatures (e.g., isSupportedType() instead of checkType()).
   • Ensure PHP 8.1+ compatibility (e.g., named arguments, strict types).

2. Platform-Specific Behavior:

   • GROUP_CONCAT syntax differs between MySQL and PostgreSQL. Use the library’s built-in implementation for consistency.
   • TIMESTAMPDIFF units must match the database’s supported units (e.g., YEAR, MONTH).

3. Performance:

   • Avoid complex DQL functions in WHERE clauses for large datasets (e.g., DATE_FORMAT with wildcards).
   • Prefer database-level operations (e.g., YEAR()) over application-side calculations.

4. Field Type Quirks:

   • MoneyType/PercentType require Doctrine ORM 3.0+ and PHP 8.1+.
   • Ensure your database driver supports the field types (e.g., PostgreSQL’s numeric for MoneyType).

Debugging Tips

• SQL Generation: Use Doctrine\ORM\Query::getSQL() to inspect the generated query:

  $query = $qb->getQuery();
  dump($query->getSQL());
  

• Function Registration: Verify functions are registered in your Doctrine config. Use:

  $config = $entityManager->getConfiguration();
  dump($config->getCustomDatetimeFunctions());
  

• Platform Mismatch: If queries fail, check the active platform:

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

Extension Points

1. Adding Custom Functions:

   • For simple functions, extend Oro\ORM\Query\AST\Functions\SimpleFunction and register it in your config.
   • For complex functions (e.g., multi-argument), create:
     • A DQL parser (extend Oro\ORM\Query\AST\Functions\AbstractFunctionNode).
     • Platform-specific SQL generators (e.g., Oro\ORM\Query\AST\Platform\Functions\MySql\MyCustomFunction).

2. Custom Field Types:

   • Extend Doctrine\DBAL\Types\Type for new field types. Example for MoneyType:

     class MoneyType extends Type {
         public function getSQLDeclaration(array $column, AbstractPlatform $platform) {
             return $platform->getDecimalTypeDeclarationSQL($column);
         }
         // ... other required methods
     }
     

3. Testing:

   • Use Oro\Tests\ORM\Query\AST\Functions\FunctionTestCase as a base for custom function tests.
   • Test both MySQL and PostgreSQL platforms if supported.

Configuration Quirks

• Symfony Flex: If using Symfony Flex, ensure the bundle is auto-discovered. Add to config/bundles.php:

  return [
      // ...
      Oro\DoctrineExtensionsBundle\OroDoctrineExtensionsBundle::class => ['all' => true],
  ];
  

• Doctrine Cache: Clear the Doctrine cache after adding new functions:

  php bin/console doctrine:cache:clear-metadata
  php bin/console doctrine:cache:clear-query
  php bin/console doctrine:cache:clear-result