Scalar and statistics query builders

Core 3 adds two relational builders for queries that should return selected values instead of hydrated entities:

• ScallarRelationalQueryBuilder for explicit scalar projections;
• StatsQueryBuilder for SQL aggregate and arithmetic projections.

API spelling

The public Core 3 API is currently named createScallarQueryBuilder() and ScallarRelationalQueryBuilder with two l characters. Use that spelling in application code.

Both builders return values through Small\SwooleEntityManager\Database\Common\ResultSet\ScalarResult objects.

Scalar projections

Create a scalar builder directly from a relational manager:

$query = $userManager
    ->createScallarQueryBuilder('user')
    ->select('user.id, user.username as name');

$rows = $query->getResults();

foreach ($rows as $row) {
    $id = $row['user']['id'];
    $name = $row['user']['name'];
}

select() accepts comma-separated fields. A field can be qualified with its query alias and can expose a separate result alias with case-insensitive AS syntax:

$query
    ->select('user.username AS owner')
    ->addSelect('user.createdAt as registeredAt');

For an unqualified field, pass the entity alias separately:

$query->select('username', 'user');

Result aliases must match [A-Za-z_][A-Za-z0-9_]*. The same source field can be selected more than once when each projection has a distinct output alias.

Use clearSelect() to remove all scalar projections.

One row or many rows

getResults() returns a ScalarResult\ResultSetCollection and can contain any number of rows.

getResult() returns exactly one ScalarResult\ResultSet. Build any filters on the query before executing it:

$query = $userManager
    ->createScallarQueryBuilder('user')
    ->select('user.username as name');

$query
    ->where()
    ->firstCondition(
        $query->getFieldForCondition('id', 'user'),
        \Small\SwooleEntityManager\QueryBuilder\RelationalQueryBuilder\Enum\ConditionOperatorType::equal,
        ':id',
    );
$query->setParameter('id', 42);

$result = $query->getResult();
$name = $result['user']['name'];

getResult() throws EmptyResultException for zero rows and MultipleResultException for more than one row.

Statistics builder

StatsQueryBuilder extends the scalar builder. Ordinary selected fields are automatically used as GROUP BY fields and aggregate projections are added beside them.

A global aggregate query has no ordinary selected field:

$result = $userManager
    ->createStatsQueryBuilder('user')
    ->count('*', 'totalUsers')
    ->sum('user.balance', 'totalBalance')
    ->avg('user.balance', 'averageBalance')
    ->getResult();

$totalUsers = $result['user']['totalUsers'];
$totalBalance = $result['user']['totalBalance'];
$averageBalance = $result['user']['averageBalance'];

A grouped query selects the grouping fields normally:

$rows = $orderManager
    ->createStatsQueryBuilder('order')
    ->select('order.status')
    ->count('*', 'orders')
    ->sum('order.total', 'amount')
    ->getResults();

foreach ($rows as $row) {
    $status = $row['order']['status'];
    $orders = $row['order']['orders'];
    $amount = $row['order']['amount'];
}

Every ordinary scalar projection is included in the generated GROUP BY. Repeated projections of the same source field are grouped only once at SQL level.

Aggregate functions

The builder exposes fluent methods for the SQL aggregate operations supported by Core 3:

$query
    ->count('*', 'rows')
    ->countDistinct('user.id', 'uniqueUsers')
    ->sum('invoice.total', 'total')
    ->avg('invoice.total', 'average')
    ->min('invoice.total', 'minimum')
    ->max('invoice.total', 'maximum')
    ->stddev('invoice.total', 'stddev')
    ->stddevPop('invoice.total', 'stddevPopulation')
    ->stddevSamp('invoice.total', 'stddevSample')
    ->varPop('invoice.total', 'variancePopulation')
    ->varSamp('invoice.total', 'varianceSample')
    ->groupConcat('user.username', 'names', ';');

count('*') is the only aggregate that accepts *.

groupConcat() maps to GROUP_CONCAT on MySQL and to STRING_AGG(CAST(... AS text), separator) on PostgreSQL.

The generic aggregate() method accepts StatsOperationType and also remains compatible with GroupByOperationType:

use Small\SwooleEntityManager\QueryBuilder\RelationalQueryBuilder\Enum\StatsOperationType;

$query->aggregate(
    StatsOperationType::sum,
    'invoice.total',
    'grossAmount',
);

Composable aggregate arithmetic

For programmatic arithmetic, build an OperationCollection and add it with operation():

use Small\SwooleEntityManager\QueryBuilder\RelationalQueryBuilder\Collection\OperationCollection;
use Small\SwooleEntityManager\QueryBuilder\RelationalQueryBuilder\Enum\StatsOperationType;

$average = (new OperationCollection())
    ->first(StatsOperationType::sum, 'invoice.total')
    ->div(StatsOperationType::count, '*');

$result = $invoiceManager
    ->createStatsQueryBuilder('invoice')
    ->operation($average, 'averageTotal')
    ->getResult();

OperationCollection supports add(), sub(), mul(), div() and mod() after the first aggregate.

String aggregate expressions

stringOperation() is a compact alternative for arithmetic expressions:

$result = $invoiceManager
    ->createStatsQueryBuilder('invoice')
    ->stringOperation(
        '(sum(invoice.total) - sum(invoice.discount)) / count(*)',
        'averageNet',
    )
    ->getResult();

$averageNet = $result['invoice']['averageNet'];

Supported arithmetic operators are +, -, *, / and %, with parentheses for precedence. Fields must appear inside aggregate functions. Nested aggregate functions are rejected.

Aggregate function names are case-insensitive and include avg, count, countDistinct, groupConcat/stringAgg, max, min, stddev, stddevPop, stddevSamp, sum, varPop and varSamp.

SQL driver support

StatsQueryBuilder is an SQL feature and is supported by the MySQL and PostgreSQL adapters. The Small Swoole DB adapter explicitly rejects statistics queries with QueryBuilderAdapterException instead of returning non-aggregated data.

Scalar projections remain useful with the non-SQL backend because Core normalizes driver result fields into the same scalar result structure.

Next chapter: Update builder