Download the PHP package cryonighter/formula-doctrine without Composer

Formula Doctrine

Hibernate-style #[Formula] computed fields for Doctrine ORM 3 entities.

Adds support for read-only, SQL-computed entity properties populated via subqueries, aggregations and joins — without N+1 queries.

Example with native SQL subquery – must be enclosed in parentheses:

Example using DQL subquery – should not be enclosed in parentheses:

Requirements

• PHP >= 8.2.0 but the latest stable version of PHP is recommended

Install

Symfony

If you are using Symfony, install the bundle instead — it wires everything automatically via Symfony DI:

See cryonighter/formula-doctrine-bundle for installation and configuration instructions.

Standalone

If you use another framework or write in bare PHP:

Bootstrap the stack manually when creating your EntityManager:

That's it. Formula fields on your entities will be populated automatically on every query — DQL, find(), findBy(), eager associations and lazy proxies.

Usage

Basic example

Add #[Formula] to any property on a Doctrine entity. The property must not be mapped with #[ORM\Column].

SQL vs DQL expressions

#[Formula] accepts both native SQL and DQL expressions. The rule is simple:

• Native SQL — enclose the expression in parentheses: #[Formula('(SELECT ...)')]
• DQL — no parentheses: #[Formula('SELECT ...')]

In DQL, use entity class names and mapped field names instead of table and column names.

Fetching entities

No changes to your query code are needed. Formula fields are populated automatically on every DQL SELECT:

A single SQL query is executed — no N+1:

QueryBuilder

Works with QueryBuilder too:

And in the repositories too:

Methods find(), findBy(), findOneBy() and findAll() are also supported:

Using formula fields in queries

Formula fields can be used in WHERE, ORDER BY, GROUP BY and HAVING clauses just like regular entity properties:

WHERE clause

Filter entities by computed values:

ORDER BY clause

Sort by formula fields:

GROUP BY and HAVING clauses

Aggregate and filter by computed values:

Combined example

All clauses together in a single query:

Note: Formula fields work transparently in all query clauses. The SQL subquery is embedded only once per query, not per clause usage.

Aggregate functions

All DQL aggregate functions (e.g. COUNT, SUM, AVG, MIN, MAX) work with formula fields out of the box:

Note: MIN and MAX ignore NULL values — so nullable formula fields (e.g. ?float $maxOrderTotal) behave correctly even when some entities have no related records.

CASE WHEN expressions

Formula fields can be used inside CASE WHEN ... THEN ... END expressions directly in DQL — for categorisation, conditional sorting and custom labels:

Nullable fields

If a formula can return NULL (e.g. MAX on an empty set), declare the property as nullable — the type is inferred automatically:

The {this} placeholder

Use {this} to reference the root entity's table alias in the native SQL expression or root entity itself in the DQL expression.

In native SQL, {this} is resolved to the actual Doctrine-generated table alias (e.g. c0_):

In DQL, {this} refers to the root entity itself, so you compare against the entity reference directly — without a field suffix:

Do not hardcode the table name or alias directly — it will break when Doctrine generates a different alias.

Custom SELECT alias

By default the SQL column alias matches the property name. Override it with the alias parameter:

Use a custom alias only when you need to control the raw SQL column name, e.g. for compatibility with a specific reporting tool.

Nested Formulas

A #[Formula] expression can reference a formula field of another entity. The entire chain is resolved into a single SQL query.

Note: In a native SQL expression, reference another formula field by its alias if one is declared (e.g. c.total), or by the property name otherwise. In a DQL expression, always use the property name (e.g. c.orderCount).

UPDATE queries

Formula fields can be used in the WHERE clause of DQL UPDATE queries — filter which entities to update based on computed values:

Note: Formula fields are read-only and are never written to the database. They can only appear in WHERE clauses of UPDATE/DELETE — not in the SET clause.

DELETE queries

Formula fields work identically in DQL DELETE queries:

How it works

1. FormulaMetadataFactory reads #[Formula] attributes via PHP Reflection and builds FormulaMetadata value objects (SQL, PHP type inferred from type hint, alias, nullability).

2. FormulaMetadataRegistry caches the metadata per entity class — Reflection runs only once per class per process.

3. LoadClassMetadataListener registers formula fields as non-insertable, non-updatable mapped fields in Doctrine ClassMetadata when entity metadata is loaded. This allows the standard ObjectHydrator to populate them without a custom hydrator, while ensuring they never appear in INSERT or UPDATE statements.

4. PostGenerateSchemaListener removes formula fields from the generated database schema after SchemaTool builds it. Formula fields have no physical column — their value is computed by a SQL subquery at query time.

5. FormulaDoctrineConfigurator (a Symfony service configurator) registers FormulaSqlWalker as the default output walker and passes FormulaMetadataRegistry as a default query hint into every Doctrine Configuration instance.

6. FormulaSqlWalker (extends SqlWalker, implements OutputWalker) intercepts DQL-to-SQL generation. It scans all DQL aliases in the query — both the root entity and any eagerly joined entities — and replaces plain column references (e.g. c0_.orderCount) with the resolved subquery expressions directly in the generated SQL string.

   Supports Walker Chaining: if another output walker was already registered, FormulaSqlWalker delegates to it first and applies formula replacements on top of its output.

7. FormulaMiddleware (DBAL Middleware) intercepts SQL generated by BasicEntityPersister for find(), findBy(), findAll(), eager association loading and lazy proxy initialisation. It detects all table aliases present in the SQL (t0, t1, t4, etc.), matches formula column references for each, and replaces them with the resolved subquery expressions.

Limitations

Change log

Please see CHANGELOG for more information on what has changed recently.

Testing

Contributing

Please see CODE_OF_CONDUCT for details.

Security

If you discover any security related issues, please email [email protected] instead of using the issue tracker.

Credits

• Andrey Reshetchenko

License

The MIT License (MIT). Please see License File for more information.