Compute a field

doctrine expert

Computing and Sorting by a Derived Field in API Platform with Doctrine This recipe explains how to dynamically calculate a field for an API Platform/Doctrine entity by modifying the SQL query (via stateOptions/handleLinks), mapping the computed value to the entity object (via processor/process), and optionally enabling sorting on it using a custom filter configured via parameters.

// src/App/Filter.php
namespace App\Filter;
use ApiPlatform\Doctrine\Orm\Filter\FilterInterface;
use ApiPlatform\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use ApiPlatform\Metadata\JsonSchemaFilterInterface;
use ApiPlatform\Metadata\Operation;
use ApiPlatform\Metadata\Parameter;
use ApiPlatform\State\ParameterNotFound;
use Doctrine\ORM\QueryBuilder;

Custom API Platform filter to allow sorting by the computed ’totalQuantity’ field. Works with the alias generated by Cart::handleLinks.

class SortComputedFieldFilter implements FilterInterface, JsonSchemaFilterInterface
{

Applies the sorting logic to the Doctrine QueryBuilder. Called by API Platform when the associated query parameter (‘sort[totalQuantity]’) is present. Adds an ORDER BY clause to the query.

    public function apply(QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, ?Operation $operation = null, array $context = []): void
    {
        if ($context['parameter']->getValue() instanceof ParameterNotFound) {
            return;
        }

Extract the desired sort direction (‘asc’ or ‘desc’) from the parameter’s value. IMPORTANT: ’totalQuantity’ here MUST match the alias defined in Cart::handleLinks.

        $queryBuilder->addOrderBy('totalQuantity', $context['parameter']->getValue()['totalQuantity'] ?? 'ASC');
    }
    /**
     * @return array<string, mixed>
     */

Defines the OpenAPI/Swagger schema for this filter parameter. Tells API Platform documentation generators that ‘sort[totalQuantity]’ expects ‘asc’ or ‘desc’. This also add constraint violations to the parameter that will reject any wrong values.

    public function getSchema(Parameter $parameter): array
    {
        return ['type' => 'string', 'enum' => ['asc', 'desc']];
    }
    public function getDescription(string $resourceClass): array
    {
        return [];
    }
}

// src/App/Entity.php
namespace App\Entity;
use ApiPlatform\Doctrine\Orm\State\Options;
use ApiPlatform\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use ApiPlatform\Metadata\GetCollection;
use ApiPlatform\Metadata\NotExposed;
use ApiPlatform\Metadata\Operation;
use ApiPlatform\Metadata\QueryParameter;
use App\Filter\SortComputedFieldFilter;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
use Doctrine\ORM\QueryBuilder;
#[ORM\Entity]

Defines the GetCollection operation for Cart, including computed ’totalQuantity’. Recipe involves:

handleLinks (modify query)
process (map result)
parameters (filters)

#[GetCollection(
    normalizationContext: ['hydra_prefix' => false],
    paginationItemsPerPage: 3,
    paginationPartial: false,

stateOptions: Uses handleLinks to modify the query before fetching.

    stateOptions: new Options(handleLinks: [self::class, 'handleLinks']),

processor: Uses process to map the result after fetching, before serialization.

    processor: [self::class, 'process'],
    write: true,

parameters: Defines query parameters.

    parameters: [

Define the sorting parameter for ’totalQuantity'.

        'sort[:property]' => new QueryParameter(

Link this parameter definition to our custom filter.

            filter: new SortComputedFieldFilter(),

Specify which properties this filter instance should handle.

            properties: ['totalQuantity'],
            property: 'totalQuantity'
        ),
    ]
)]
class Cart
{

Handles links/joins and modifications to the QueryBuilder before data is fetched (via stateOptions). Adds SQL logic (JOIN, SELECT aggregate, GROUP BY) to calculate ’totalQuantity’ at the database level. The alias ’totalQuantity’ created here is crucial for the filter and processor.

    public static function handleLinks(QueryBuilder $queryBuilder, array $uriVariables, QueryNameGeneratorInterface $queryNameGenerator, array $context): void
    {

Get the alias for the root entity (Cart), usually ‘o’.

        $rootAlias = $queryBuilder->getRootAliases()[0] ?? 'o';

Generate a unique alias for the joined ‘items’ relation to avoid conflicts.

        $itemsAlias = $queryNameGenerator->generateParameterName('items');
        $queryBuilder->leftJoin(\sprintf('%s.items', $rootAlias), $itemsAlias)
            ->addSelect(\sprintf('COALESCE(SUM(%s.quantity), 0) AS totalQuantity', $itemsAlias))
            ->addGroupBy(\sprintf('%s.id', $rootAlias));
    }

Processor function called after fetching data, before serialization. Maps the raw ’totalQuantity’ from Doctrine result onto the Cart entity’s property. Handles Doctrine’s array result structure: [0 => Entity, ‘alias’ => computedValue]. Reshapes data back into an array of Cart objects.

    public static function process(mixed $data, Operation $operation, array $uriVariables = [], array $context = [])
    {

Iterate through the raw results. $value will be like [0 => Cart Object, ’totalQuantity’ => 15]

        foreach ($data as &$value) {

Get the Cart entity object.

            $cart = $value[0];

Get the computed totalQuantity value using the alias defined in handleLinks. Use null coalescing operator for safety.

            $cart->totalQuantity = $value['totalQuantity'] ?? 0;

Replace the raw array structure with just the processed Cart object.

            $value = $cart;
        }

Return the collection of Cart objects with the totalQuantity property populated.

        return $data;
    }

Public property to hold the computed total quantity. Not mapped by Doctrine (@ORM\Column) but populated by the ‘process’ method. API Platform will serialize this property.

    public ?int $totalQuantity;
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private ?int $id = null;
    /**
     * @var Collection<int, CartProduct> the items in this cart
     */
    #[ORM\OneToMany(targetEntity: CartProduct::class, mappedBy: 'cart', cascade: ['persist', 'remove'], orphanRemoval: true)]
    private Collection $items;
    public function __construct()
    {
        $this->items = new ArrayCollection();
    }
    public function getId(): ?int
    {
        return $this->id;
    }
    /**
     * @return Collection<int, CartProduct>
     */
    public function getItems(): Collection
    {
        return $this->items;
    }
    public function addItem(CartProduct $item): self
    {
        if (!$this->items->contains($item)) {
            $this->items[] = $item;
            $item->setCart($this);
        }
        return $this;
    }
    public function removeItem(CartProduct $item): self
    {
        if ($this->items->removeElement($item)) {

set the owning side to null (unless already changed)

            if ($item->getCart() === $this) {
                $item->setCart(null);
            }
        }
        return $this;
    }
}
#[NotExposed()]
#[ORM\Entity]
class CartProduct
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private ?int $id = null;
    #[ORM\ManyToOne(targetEntity: Cart::class, inversedBy: 'items')]
    #[ORM\JoinColumn(nullable: false)]
    private ?Cart $cart = null;
    #[ORM\Column(type: 'integer')]
    private int $quantity = 1;
    public function getId(): ?int
    {
        return $this->id;
    }
    public function getCart(): ?Cart
    {
        return $this->cart;
    }
    public function setCart(?Cart $cart): self
    {
        $this->cart = $cart;
        return $this;
    }
    public function getQuantity(): int
    {
        return $this->quantity;
    }
    public function setQuantity(int $quantity): self
    {
        $this->quantity = $quantity;
        return $this;
    }
}

// src/App/Playground.php
namespace App\Playground;
use Symfony\Component\HttpFoundation\Request;
function request(): Request
{
    return Request::create('/carts?sort[totalQuantity]=asc', 'GET');
}

// src/DoctrineMigrations.php
namespace DoctrineMigrations;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
final class Migration extends AbstractMigration
{
    public function up(Schema $schema): void
    {
        $this->addSql('CREATE TABLE cart (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL)');
        $this->addSql('CREATE TABLE cart_product (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, quantity INTEGER NOT NULL, cart_id INTEGER NOT NULL, CONSTRAINT FK_6DDC373A1AD5CDBF FOREIGN KEY (cart_id) REFERENCES cart (id) NOT DEFERRABLE INITIALLY IMMEDIATE)');
        $this->addSql('CREATE INDEX IDX_6DDC373A1AD5CDBF ON cart_product (cart_id)');
    }
}

// src/App/Tests.php
namespace App\Tests;
use ApiPlatform\Playground\Test\TestGuideTrait;
use ApiPlatform\Symfony\Bundle\Test\ApiTestCase;
final class ComputedFieldTest extends ApiTestCase
{
    use TestGuideTrait;
    public function testCanSortByComputedField(): void
    {
        $ascReq = static::createClient()->request('GET', '/carts?sort[totalQuantity]=asc');
        $this->assertResponseIsSuccessful();
        $asc = $ascReq->toArray();
        $this->assertGreaterThan(
            $asc['member'][0]['totalQuantity'],
            $asc['member'][1]['totalQuantity']
        );
    }
}

// src/App/Fixtures.php
namespace App\Fixtures;
use App\Entity\Cart;
use App\Entity\CartProduct;
use Doctrine\Bundle\FixturesBundle\Fixture;
use Doctrine\Persistence\ObjectManager;
use function Zenstruck\Foundry\anonymous;
use function Zenstruck\Foundry\repository;
final class CartFixtures extends Fixture
{
    public function load(ObjectManager $manager): void
    {
        $cartFactory = anonymous(Cart::class);
        if (repository(Cart::class)->count()) {
            return;
        }
        $cartFactory->many(10)->create(fn ($i) => [
            'items' => $this->createCartProducts($i),
        ]);
    }
    /**
     * @return array<CartProduct>
     */
    private function createCartProducts($i): array
    {
        $cartProducts = [];
        for ($j = 1; $j <= 10; ++$j) {
            $cartProduct = new CartProduct();
            $cartProduct->setQuantity((int) abs($j / $i) + 1);
            $cartProducts[] = $cartProduct;
        }
        return $cartProducts;
    }
}

You can also help us improve this guide.