Formula Doctrine Bundle

Symfony bundle for integrating cryonighter/formula-doctrine into Symfony applications.

It enables Hibernate-style #[Formula] computed fields for Doctrine ORM entities and wires the required Doctrine metadata listeners, SQL walker configuration and DBAL middleware automatically through Symfony's dependency injection container.

Use it when you want read-only entity properties whose values are computed by SQL expressions, subqueries, aggregations or joins — without adding physical database columns and without introducing N+1 queries.

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

With this bundle installed, formula fields are populated automatically when entities are loaded through Doctrine in a Symfony application. The bundle keeps your entity code focused on the #[Formula] attributes while taking care of registering the integration services needed by cryonighter/formula-doctrine.

Requirements

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

Install

Via Composer

composer require cryonighter/formula-doctrine-bundle

The bundle will be automatically registered in config/bundles.php:

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

Bundle Registration Order

If you use other bundles that extend Doctrine ORM with custom SQL walkers (e.g. Gedmo DoctrineExtensions, API Platform), register FormulaDoctrineBundle last in config/bundles.php:

php
return [
    // ... other bundles ...
    Stof\DoctrineExtensionsBundle\StofDoctrineExtensionsBundle::class => ['all' => true],
    Cryonighter\FormulaDoctrine\FormulaDoctrineBundle::class => ['all' => true], // ← last
];

FormulaDoctrineBundle automatically detects and chains with any previously registered output walker, so both transformations are applied to every query.

If another bundle is registered after FormulaDoctrineBundle and also sets a custom output walker globally, you may need to manually call FormulaDoctrineConfigurator::configure() in your application's bundle.

Usage

Basic example

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

use Cryonighter\FormulaDoctrine\Attribute\Formula;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
#[ORM\Table(name: 'customers')]
class Customer
{
    #[ORM\Id, ORM\Column, ORM\GeneratedValue]
    public int $id;

    #[ORM\Column]
    public string $name;

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

    #[Formula('(SELECT COALESCE(SUM(oi.price), 0) FROM order_items oi JOIN orders o ON oi.order_id = o.id WHERE o.customer_id = {this}.id)')]
    public float $totalRevenue = 0.0;

    #[Formula('(SELECT MAX(o.created_at) FROM orders o WHERE o.customer_id = {this}.id)')]
    public ?string $lastOrderDate = null;
}

Fetching entities

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

$customers = $entityManager
    ->createQuery('SELECT c FROM App\Entity\Customer c')
    ->getResult();

foreach ($customers as $customer) {
    echo $customer->orderCount;    // populated from subquery
    echo $customer->totalRevenue;  // populated from subquery
}

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

SELECT c0_.id,
       c0_.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c0_.id) AS orderCount,
       (SELECT COALESCE(SUM(...), 0) FROM ...) AS totalRevenue,
       (SELECT MAX(...) FROM ...) AS lastOrderDate
FROM customers c0_

QueryBuilder

Works with QueryBuilder too:

$customers = $entityManager
    ->createQueryBuilder()
    ->select('c')
    ->from(Customer::class, 'c')
    ->where('c.name LIKE :name')
    ->setParameter('name', '%Acme%')
    ->getQuery()
    ->getResult();

And in the repositories too:

class CustomerRepository extends ServiceEntityRepository
{
    public function findTopCustomers(int $limit): array
    {
        return $this->createQueryBuilder('c')
            ->orderBy('c.id', 'ASC')
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
        // $result[0]->totalRevenue is populated automatically
    }
}

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

$customerRepository = $this->em->getRepository(Customer::class);

$customers = $customerRepository->findAll();

echo $customer[0]->orderCount;    // populated from subquery
echo $customer[0]->totalRevenue;  // populated from subquery

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:

// DQL
$customers = $entityManager
    ->createQuery('SELECT c FROM App\Entity\Customer c WHERE c.orderCount > :minOrders')
    ->setParameter('minOrders', 5)
    ->getResult();

// QueryBuilder
$customers = $entityManager
    ->createQueryBuilder()
    ->select('c')
    ->from(Customer::class, 'c')
    ->where('c.totalRevenue >= :minRevenue')
    ->setParameter('minRevenue', 1000.0)
    ->getQuery()
    ->getResult();

// Repository findBy()
$customers = $customerRepository->findBy(['orderCount' => 10]);

ORDER BY clause

Sort by formula fields:

// DQL
$customers = $entityManager
    ->createQuery('SELECT c FROM App\Entity\Customer c ORDER BY c.totalRevenue DESC')
    ->getResult();

// QueryBuilder
$customers = $entityManager
    ->createQueryBuilder()
    ->select('c')
    ->from(Customer::class, 'c')
    ->orderBy('c.orderCount', 'DESC')
    ->getQuery()
    ->getResult();

// Repository findBy() with ordering
$customers = $customerRepository->findBy(
    [],
    ['totalRevenue' => 'DESC']
);

GROUP BY and HAVING clauses

Aggregate and filter by computed values:

// Group customers by order count and filter groups
$result = $entityManager
    ->createQuery('
        SELECT c.orderCount, COUNT(c.id) as customerCount, AVG(c.totalRevenue) as avgRevenue
        FROM App\Entity\Customer c
        GROUP BY c.orderCount
        HAVING c.orderCount >= :minOrders AND COUNT(c.id) > :minCustomers
        ORDER BY c.orderCount DESC
    ')
    ->setParameter('minOrders', 3)
    ->setParameter('minCustomers', 1)
    ->getResult();

// Result example:
// [
//   ['orderCount' => 10, 'customerCount' => 5, 'avgRevenue' => 15000.50],
//   ['orderCount' => 7,  'customerCount' => 3, 'avgRevenue' => 8500.25],
//   ...
// ]

Combined example

All clauses together in a single query:

$result = $entityManager
    ->createQuery('
        SELECT c.orderCount, COUNT(c.id) as total
        FROM App\Entity\Customer c
        WHERE c.totalRevenue > :minRevenue
        GROUP BY c.orderCount
        HAVING c.orderCount BETWEEN :minOrders AND :maxOrders
        ORDER BY c.orderCount DESC
    ')
    ->setParameter('minRevenue', 500.0)
    ->setParameter('minOrders', 2)
    ->setParameter('maxOrders', 10)
    ->getResult();

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

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:

#[Formula('(SELECT MAX(o.total) FROM orders o WHERE o.customer_id = {this}.id)')]
public ?float $maxOrderTotal = null;

The `{this}` placeholder

Use {this} to reference the root entity's table alias in the SQL expression. It is resolved to the actual Doctrine-generated alias (e.g. c0_) at query time.

// {this} will become the real SQL alias, e.g. c0_
#[Formula('(SELECT COUNT(*) FROM orders o WHERE o.customer_id = {this}.id)')]
public int $orderCount = 0;

Do not hardcode the table name 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:

#[Formula(
    sql: '(SELECT COUNT(*) FROM orders o WHERE o.customer_id = {this}.id)',
    alias: 'total_orders',
)]
public int $orderCount = 0;

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

How it works

You can read about this in the description of the base package cryonighter/formula-doctrine.

Change log

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

Testing

# All tests
./vendor/bin/phpunit

# Only unit
./vendor/bin/phpunit --testsuite Unit

# Only integration
./vendor/bin/phpunit --testsuite Integration

# Specific file
./vendor/bin/phpunit tests/Unit/DependencyInjection/FormulaDoctrineCompilerPassTest.php

# With coating (requires Xdebug or PCOV)
./vendor/bin/phpunit --coverage-text

Contributing

Please see CONTRIBUTING and CODE_OF_CONDUCT for details.

Security

If you discover any security related issues, please email cryonighter@yandex.ru instead of using the issue tracker.

Credits

Andrey Reshetchenko

License

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

Formula Doctrine Bundle Laravel Package