Formula Doctrine Bundle Laravel Package

Getting Started

Minimal Setup

1. Install the Bundle

   composer require cryonighter/formula-doctrine-bundle
   

   Ensure Cryonighter\FormulaDoctrineBundle\CryonighterFormulaDoctrineBundle::class is registered in config/bundles.php.

2. First Use Case: Basic Formula Field Add a computed field to an entity using the #[Formula] attribute:

   #[ORM\Entity]
   class Product
   {
       #[ORM\Id, ORM\GeneratedValue]
       #[ORM\Column]
       public ?int $id = null;
   
       #[Formula('(SELECT AVG(rating) FROM reviews WHERE product_id = {this}.id)')]
       public float $averageRating = 0.0;
   }
   

   • No DB migration needed: The formula is resolved at query time.
   • No manual hydration: Values populate automatically when entities are loaded via Doctrine.

3. Verify Functionality Fetch an entity and inspect the computed field:

   $product = $entityManager->getRepository(Product::class)->find(1);
   // $product->averageRating now contains the computed value.
   

Implementation Patterns

Workflow: Integrating Formulas into Existing Entities

1. Identify Computed Fields

   • Use #[Formula] for read-only properties derived from SQL (e.g., aggregations, joins, or subqueries).
   • Example: Calculating a user’s total orders or a product’s stock level across warehouses.

2. Leverage Placeholders

   • Use {this} to reference the current entity’s columns (e.g., {this}.id).
   • Example:

     #[Formula('SUM(o.quantity) FROM order_items o WHERE o.order_id = {this}.id')]
     public int $totalOrderQuantity = 0;
     

3. Query Optimization

   • Avoid N+1: Formulas are resolved in a single query via Doctrine’s HYDRATE_ARRAY or custom hydration.
   • Selective Loading: Use Doctrine\ORM\QueryBuilder with addSelect() to explicitly include formulas:

     $qb = $entityManager->createQueryBuilder()
         ->select('p', 'f1.averageRating AS HIDDEN averageRating') // HIDDEN prevents column naming conflicts
         ->from(Product::class, 'p')
         ->leftJoin('p.formulas', 'f1');
     

4. Caching Strategies

   • Bundle-Supported Caching: Configure cryonighter_formula.doctrine.cache in config/packages/cryonighter_formula_doctrine.yaml to cache formula results (e.g., apcu or redis).

     cryonighter_formula:
         doctrine:
             cache: true
             cache_driver: apcu
     

   • TTL Control: Set a TTL for cached results (default: null = no caching):

     #[Formula('...', ttl: 3600)] // Cache for 1 hour
     

5. Integration with DTOs

   • Use formulas in API Platform or Symfony Serializer by projecting formulas into DTOs:

     #[ApiResource]
     class ProductDto
     {
         public function getAverageRating(): float {
             return $this->product->averageRating; // Assumes Product has #[Formula]
         }
     }
     

6. Testing

   • Mock the FormulaDoctrine\Formula\FormulaManager in tests to return deterministic values:

     $formulaManager = $this->createMock(FormulaManager::class);
     $formulaManager->method('getValue')->willReturn(4.5);
     $entityManager->getConnection()->getConfiguration()->setFormulaManager($formulaManager);
     

Gotchas and Tips

Pitfalls

1. Circular Dependencies

   • Issue: Formulas referencing other formulas (e.g., Formula A depends on Formula B which depends on Formula A) cause infinite recursion.
   • Fix: Restructure formulas to avoid circular references or use raw SQL with explicit joins.

2. Performance Overhead

   • Issue: Complex formulas (e.g., nested subqueries or joins) can bloat queries.
   • Fix:
     • Test query performance with EXPLAIN in your database.
     • Use #[ORM\Index] on frequently filtered columns in formulas.
     • Cache results aggressively for read-heavy workloads.

3. Database Compatibility

   • Issue: Some SQL dialects (e.g., SQLite) may not support all functions used in formulas.
   • Fix: Test formulas across target databases or use dialect-specific placeholders:

     #[Formula('CASE WHEN {dialect} = "mysql" THEN ... ELSE ... END')]
     

4. Lazy Loading Conflicts

   • Issue: Formulas may not populate if entities are loaded via find() without explicit SELECT.
   • Fix: Always use QueryBuilder or Criteria to ensure formulas are included:

     $entityManager->createQueryBuilder()
         ->select('e', 'f1.averageRating AS HIDDEN averageRating')
         ->from(Entity::class, 'e')
         ->leftJoin('e.formulas', 'f1')
         ->getQuery()
         ->getResult();
     

5. Serialization Warnings

   • Issue: Formulas may trigger "uninitialized property" warnings if not populated.
   • Fix: Initialize formula properties with default values (e.g., public float $averageRating = 0.0).

Debugging Tips

1. Enable SQL Logging Configure Doctrine to log generated SQL with formulas:

   # config/packages/dev/doctrine.yaml
   doctrine:
       dbal:
           logging: true
           profiling: true
   

   • Check logs for FORMULA placeholders in queries.

2. Inspect Metadata Dump the entity metadata to verify formula registration:

   $metadata = $entityManager->getClassMetadata(Product::class);
   print_r($metadata->formulaMappings); // Should list your #[Formula] attributes.
   

3. Override Formula Resolution For debugging, override the FormulaManager to log or mock formula execution:

   $entityManager->getConnection()->getConfiguration()->setFormulaManager(
       new class implements FormulaManagerInterface {
           public function getValue(Formula $formula, EntityManagerInterface $em, array $context): mixed {
               error_log("Executing formula: " . $formula->getExpression());
               return parent::getValue($formula, $em, $context);
           }
       }
   );
   

Extension Points

1. Custom Formula Types Extend the bundle to support custom formula types (e.g., JavaScript-based computations):

   // src/Formula/CustomFormula.php
   class CustomFormula implements FormulaInterface {
       public function getExpression(): string { ... }
       public function getValue(EntityManagerInterface $em, array $context): mixed {
           // Custom logic (e.g., call an external API).
       }
   }
   

   Register the type in the bundle’s configuration.

2. Event Listeners Hook into formula resolution via Doctrine events:

   $eventManager->addEventListener(ORM\Events::loadClassMetadata, function (LoadClassMetadataEvent $event) {
       $metadata = $event->getClassMetadata();
       if ($metadata->name === Product::class) {
           $metadata->setFormula('custom_field', 'SELECT ...');
       }
   });
   

3. Database-Specific Dialects Override the SQL dialect to handle database-specific syntax:

   # config/packages/cryonighter_formula_doctrine.yaml
   cryonighter_formula:
       doctrine:
           dialect: App\Doctrine\CustomFormulaDialect