Formula Doctrine Bundle Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require cryonighter/formula-doctrine-bundle
   

   Add the bundle to config/bundles.php:

   return [
       // ...
       Cryonighter\FormulaDoctrineBundle\FormulaDoctrineBundle::class => ['all' => true],
   ];
   

2. First Use Case: Add a computed field to an existing Doctrine entity (e.g., Customer):

   #[ORM\Entity]
   class Customer
   {
       #[Formula('(SELECT COUNT(*) FROM orders o WHERE o.customer_id = {this}.id)')]
       public int $orderCount = 0;
   }
   

   Clear cache:

   php bin/console cache:clear
   

3. Verify: Fetch a Customer entity via Doctrine (e.g., in a controller or repository):

   $customer = $entityManager->getRepository(Customer::class)->find($id);
   // $customer->orderCount will now be populated automatically
   

Implementation Patterns

Common Workflows

1. DQL vs. Native SQL:

   • Use DQL (without parentheses) for entity-aware queries (e.g., SELECT COUNT(o) FROM App\Entity\Order o WHERE o.customer = {this}).
   • Use native SQL (with parentheses) for raw SQL (e.g., (SELECT COUNT(*) FROM orders o WHERE o.customer_id = {this}.id)).

2. Integration with Repositories:

   • Formula fields are populated automatically during find(), findBy(), or getReference() calls. No manual hydration needed.
   • Example:

     $customers = $repository->findBy(['active' => true]);
     // All formula fields (e.g., $customer->orderCount) are populated.
     

3. Combining with QueryBuilder:

   • Formula fields do not appear in QueryBuilder results unless explicitly selected. Use addSelect():

     $query = $repository->createQueryBuilder('c')
         ->addSelect('c.orderCount') // Explicitly include formula field
         ->where('c.active = :active')
         ->setParameter('active', true);
     

4. Caching Strategies:

   • Formula results are not cached by default. For performance, implement a custom FormulaDriver (see Extension Points).

5. Type Safety:

   • Cast formula results to the correct type in the attribute (e.g., int, string, DateTime). The bundle handles basic type conversion.

Integration Tips

1. Symfony Forms:

   • Formula fields are read-only by default. Exclude them from form handling:

     $builder->remove('orderCount'); // Explicitly exclude from form
     

   • Or use DataTransformer to handle computed values in forms.

2. API Platform:

   • Formula fields are serialized automatically if the entity is exposed via API Platform. No additional configuration is needed.

3. Doctrine Lifecycle Events:

   • Formula fields are populated after loadClassMetadata and before postLoad. Use postLoad to react to computed values:

     #[ORM\PostLoad]
     public function postLoad(): void {
         if ($this->orderCount > 10) {
             $this->setVipStatus(true);
         }
     }
     

4. Testing:

   • Mock the FormulaDriver in unit tests to avoid hitting the database:

     $driver = $this->createMock(FormulaDriver::class);
     $driver->method('getValue')->willReturn(42);
     $entityManager->getConnection()->getConfiguration()->setFormulaDriver($driver);
     

Gotchas and Tips

Pitfalls

1. Parentheses in DQL:

   • Do not enclose DQL formulas in parentheses. This will cause a syntax error:

     // ❌ Wrong (extra parentheses)
     #[Formula('(SELECT COUNT(o) FROM App\Entity\Order o WHERE o.customer = {this})')]
     

   • Correct:

     // ✅ Right (no parentheses for DQL)
     #[Formula('SELECT COUNT(o) FROM App\Entity\Order o WHERE o.customer = {this}')]
     

2. N+1 Queries:

   • Formula fields do not trigger N+1 queries if used in find() or findBy(). However, manual queries (e.g., createQueryBuilder()) may require explicit JOIN or addSelect():

     // ❌ May cause N+1 if orderCount is accessed later
     $query->where('c.id = :id');
     
     // ✅ Safe (explicitly include formula)
     $query->addSelect('c.orderCount');
     

3. Circular Dependencies:

   • Avoid formulas that reference other formula fields (e.g., #[Formula('{this}.fieldA + {this}.fieldB')] where fieldB is also a formula). This can lead to infinite recursion.

4. Database-Specific SQL:

   • Native SQL formulas may not be portable across databases. Test on your target DBMS (e.g., MySQL, PostgreSQL).

5. Case Sensitivity:

   • Entity property names in formulas (e.g., {this}.id) are case-sensitive. Use the exact property name defined in the entity.

Debugging

1. Enable SQL Logging:

   • Check if formulas are being executed by enabling Doctrine SQL logging:

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

   • Look for SELECT queries generated by the bundle.

2. Check FormulaDriver:

   • If formulas aren’t working, verify the FormulaDriver is registered:

     php bin/console debug:container cryonighter.formula_doctrine.driver
     

   • Ensure no custom FormulaDriver is overriding the default behavior.

3. Metadata Cache:

   • Clear metadata cache if formulas stop working after changes:

     php bin/console doctrine:cache:clear-metadata
     

Extension Points

1. Custom FormulaDriver:

   • Override the default driver to add caching or logging:

     # config/services.yaml
     services:
         App\Formula\CustomFormulaDriver:
             decorates: 'cryonighter.formula_doctrine.driver'
             arguments:
                 $decorated: '@.inner'
     

   • Implement Cryonighter\FormulaDoctrine\Driver\FormulaDriverInterface.

2. Event Subscribers:

   • Listen to onFlush or postLoad to modify formula behavior:

     #[AsEventSubscriber]
     class FormulaSubscriber implements EventSubscriber
     {
         public static function getSubscribedEvents(): array
         {
             return [
                 Events::postLoad,
             ];
         }
     
         public function postLoad(LifecycleEventArgs $args): void
         {
             $entity = $args->getObject();
             if ($entity instanceof Customer && $entity->orderCount > 0) {
                 $entity->setHasOrders(true);
             }
         }
     }
     

3. Custom SQL Walker:

   • Extend the SQL walker to support complex formula logic (advanced use case). See cryonighter/formula-doctrine for details.

Performance Tips

1. Selective Loading:

   • Use DISTINCT or JOIN in formulas to avoid redundant calculations:

     #[Formula('SELECT COUNT(DISTINCT o.id) FROM App\Entity\Order o WHERE o.customer = {this}')]
     

2. Indexing:

   • Ensure foreign keys (e.g., customer_id) in formula subqueries are indexed for performance.

3. Materialized Views:

   • For frequently used formulas, consider denormalizing data into a materialized view and referencing it in the formula:

     #[Formula('(SELECT order_count FROM customer_order_counts WHERE customer_id = {this}.id)')]