Aggregate Functions

Aggregate functions compute a single value across a set of rows and are used in the retrieve clause.

explanation

count() and countu()

Returns the number of matching rows. countu() counts only distinct values:

// Total orders and distinct products ordered per user
$results = $entityManager->executeQuery("
    range of u is App\\Entity\\UserEntity
    range of o is App\\Entity\\OrderEntity via u.orders
    retrieve (u.name, totalOrders=count(o.id), distinctProducts=countu(o.productId))
");

sum() and sumu()

Returns the total of all non-null numeric values. sumu() sums only distinct values:

// Total revenue per user
$results = $entityManager->executeQuery("
    range of u is App\\Entity\\UserEntity
    range of o is App\\Entity\\OrderEntity via u.orders
    retrieve (u.name, sum(o.total))
");

avg() and avgu()

Returns the arithmetic mean of all non-null numeric values. avgu() averages only distinct values:

// Average order value per user
$results = $entityManager->executeQuery("
    range of u is App\\Entity\\UserEntity
    range of o is App\\Entity\\OrderEntity via u.orders
    retrieve (u.name, avg(o.total))
");

min() and max()

Returns the smallest and largest non-null value respectively. Both work on numeric, date, and string fields using their natural ordering:

// Price range per category
$results = $entityManager->executeQuery("
    range of c is App\\Entity\\CategoryEntity
    range of p is App\\Entity\\ProductEntity via c.products
    retrieve (c.name, min(p.price), max(p.price))
");

any()

Returns 1 if at least one matching row exists, 0 otherwise. Unlike count(), it stops scanning as soon as the first match is found, making it more efficient for existence checks:

// Per user: whether they have at least one completed order
$results = $entityManager->executeQuery("
    range of u is App\\Entity\\UserEntity
    range of o is App\\Entity\\OrderEntity via u.orders
    retrieve (u.name, hasCompletedOrder=any(o.id where o.status = 'completed'))
");

Inline WHERE Filter

Every aggregate accepts an optional where condition inside its parentheses, which scopes what the aggregate counts or sums without affecting which rows the outer query returns:

// Per user: total orders, completed orders, and revenue from completed orders only
$results = $entityManager->executeQuery("
    range of u is App\\Entity\\UserEntity
    range of o is App\\Entity\\OrderEntity via u.orders
    retrieve (
        u.name,
        count(o.id),
        count(o.id where o.status = 'completed'),
        sum(o.total where o.status = 'completed')
    )
");