Formula Doctrine Bundle Laravel Package

Technical Evaluation

Architecture Fit

• Pros:

  • Aligns with Symfony/Doctrine ecosystems, reducing friction for teams already using these tools.
  • Enables declarative computed fields (Hibernate-style #[Formula]) without manual query logic in repositories/services, improving separation of concerns.
  • Avoids physical column bloat in the database while supporting complex aggregations, joins, and subqueries (e.g., COUNT, SUM, AVG).
  • Read-only by design, preventing accidental writes and ensuring data consistency.
  • Leverages Doctrine’s event system and DBAL middleware, which are battle-tested in Symfony applications.

• Cons:

  • Tight coupling to Doctrine ORM: Not suitable for projects using native SQL, Eloquent (Laravel), or non-Doctrine ORMs.
  • Performance implications:
    • Computed fields trigger additional SQL queries per entity load (unless optimized via batching or DQL hints).
    • Subqueries may increase query complexity and locking contention in high-concurrency scenarios.
  • Limited write support: Only read-only fields are supported; no support for updatable computed columns (unlike PostgreSQL’s GENERATED ALWAYS AS).
  • No built-in caching layer: Repeated loads of the same entity with formulas may re-execute SQL unless cached manually (e.g., via Symfony Cache or Redis).

Integration Feasibility

• Symfony/Doctrine Compatibility:
  • Works seamlessly with Doctrine ORM 2.10+ and Symfony 5.4+ (based on formula-doctrine dependency).
  • Requires Doctrine Metadata Listeners and SQL Walker configuration, which the bundle automates via DI.
  • No PHP version constraints beyond Doctrine’s requirements (PHP 8.0+ recommended).
• Database Support:
  • Universal SQL compatibility: Works with MySQL, PostgreSQL, SQLite, etc. as long as the SQL expressions are valid for the target DB.
  • No vendor-specific features: Unlike PostgreSQL’s GENERATED columns, this is pure SQL, so portability is high.
• Existing Code Impact:
  • Minimal changes: Only requires adding #[Formula] attributes to entities.
  • Potential breaking changes:
    • If entities are hydrated via EntityManager::find() or Repository::find(), formulas will automatically execute.
    • Custom DQL queries (e.g., SELECT e FROM Entity e) must ensure formulas are included in the result set or handled separately.

Technical Risk

• Query Performance:
  • Risk: N+1 queries if formulas are used in collections (e.g., Customer::getOrders() where orderCount is a formula).
  • Mitigation: Use DQL JOIN FETCH or batch loading (HydrationMode::HYDRATE_ARRAY + manual hydration).
• SQL Injection:
  • Risk: Malicious #[Formula] values could inject SQL if not sanitized.
  • Mitigation: Bundle uses Doctrine’s parameter binding, but custom SQL (e.g., dynamic table names) may still be vulnerable.
• Debugging Complexity:
  • Risk: Debugging nested subqueries or failed SQL in formulas can be challenging without clear error messages.
  • Mitigation: Enable Doctrine SQL logging (doctrine.dbal.logging_enabled: true) for troubleshooting.
• Testing Overhead:
  • Risk: Unit tests may need mocked Doctrine events or database transactions to verify formulas.
  • Mitigation: Use Doctrine’s EntityManager mocks or database test containers.

Key Questions

1. Performance Requirements:
   • How many formula fields per entity are planned? Will this trigger query explosion?
   • Are there high-read, low-write use cases where formulas are ideal, or high-write scenarios where physical columns are better?
2. Database Compatibility:
   • Does the target DB support complex subqueries (e.g., PostgreSQL vs. MySQL’s subquery limits)?
   • Are there legacy queries that might break when formulas introduce new SQL patterns?
3. Caching Strategy:
   • Will formulas be cached at the entity level (e.g., via Symfony Cache) to avoid repeated SQL?
   • Is stale data acceptable (e.g., cached orderCount not reflecting real-time changes)?
4. Migration Path:
   • How will existing computed properties (e.g., manually calculated in services) be refactored to use #[Formula]?
   • Are there critical paths where formulas cannot fail (e.g., API responses requiring 100% uptime)?
5. Monitoring:
   • How will slow formula queries be detected and alerted (e.g., via Doctrine profiling or New Relic)?
6. Team Adoption:
   • Is the team familiar with Doctrine events and SQL tuning? If not, will training be needed?
   • Are developers comfortable with declarative SQL in entity annotations vs. imperative logic in services?

Integration Approach

Stack Fit

• Primary Fit:
  • Symfony 5.4+ with Doctrine ORM 2.10+.
  • Projects using declarative entity mappings (annotations/YAML/XML) over programmatic configurations.
  • Applications requiring read-only computed fields without database schema changes.
• Secondary Fit:
  • Legacy Symfony apps migrating from manual computed properties to a more maintainable approach.
  • Multi-tenant apps where tenant-specific aggregations (e.g., tenantOrderCount) are needed.
• Non-Fit:
  • Laravel/Eloquent: Requires Doctrine ORM; no native integration.
  • Native SQL apps: Overkill if using raw PDO/QueryBuilder without ORM.
  • High-write workloads: Physical columns or triggers may be better.

Migration Path

1. Assessment Phase:
   • Audit existing computed properties (e.g., getOrderCount() in entities/services).
   • Identify candidates for #[Formula] (read-only, SQL-expressible logic).
   • Benchmark current query performance (e.g., N+1 risks with manual counts).
2. Pilot Implementation:
   • Start with non-critical entities (e.g., Customer.orderCount).
   • Test with simple formulas (e.g., COUNT, SUM) before complex subqueries.
   • Verify Doctrine profiling shows expected SQL execution.
3. Incremental Rollout:
   • Replace manual computed properties with #[Formula] in small batches.
   • Update repository methods to ensure formulas are included in DQL where needed.
   • Deprecate old computed methods via PHP 8.2+ attributes or deprecated annotations.
4. Optimization:
   • Implement caching for formula-heavy entities (e.g., @Cache on repositories).
   • Use DQL JOIN FETCH or batch loading to mitigate N+1 issues.
   • Add Doctrine event listeners to log slow formula queries.

Compatibility

• Doctrine Version:
  • Tested with Doctrine ORM 2.10+; may need adjustments for older versions.
  • Doctrine DBAL 3.0+ required for middleware features.
• Symfony Version:
  • Symfony 5.4+ (for DI autoconfiguration).
  • Flex 2.0+ (if using Symfony Flex for bundle installation).
• Database Dialects:
  • PostgreSQL: Best support for complex subqueries.
  • MySQL: May hit subquery limits; use JOINs instead where possible.
  • SQLite: Works but lacks some advanced SQL features.
• Existing Extensions:
  • Doctrine Extensions (e.g., Sluggable, Tree) may conflict if they also modify metadata.
  • Custom Doctrine events could interfere with the bundle’s listeners.

Sequencing

1. Prerequisites:
   • Ensure Doctrine ORM is properly configured (no custom SQL walkers breaking metadata).
   • Backup database schema before testing formulas with complex queries.
2. Bundle Installation:

   composer require cryonighter/formula-doctrine-bundle
   

   • Register the bundle in config/bundles.php:

     Cryonighter\FormulaDoctrineBundle\FormulaDoctrineBundle::class => ['all' => true],
     

3. Entity Migration:
   • Add #[Formula] attributes to entities:

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