This article is part of the CakeDC Advent Calendar 2025 (December 15th 2025)
Did you ever wanted to provide a partial result as part of an existing report?
Window functions were added in CakePHP 4.1 and provide a way to pull a rolling result expressed naturally using the ORM. We'll use CakePHP 5 code in this article.
Apart from the examples described in the book https://book.cakephp.org/5/en/orm/query-builder.html#window-functions
One common scenario where window functions are very useful are rolling results.
Imagine we have a transactions table, where account transactions are stored including a dollar amount of the transaction.
The following migration would describe an example transactions table
class CreateTransactions extends \Migrations\BaseMigration
{
public function change(): void
{
$table = $this->table('transactions');
$table
->addColumn('occurred_on', 'date', [
'null' => false,
])
->addColumn('debit_account', 'string', [
'limit' => 255,
'null' => false,
])
->addColumn('credit_account', 'string', [
'limit' => 255,
'null' => false,
])
->addColumn('amount_cents', 'biginteger', [
'null' => false,
'signed' => false,
])
->addColumn('currency', 'string', [
'limit' => 3,
'null' => false,
'default' => 'USD',
])
->addColumn('reference', 'string', [
'limit' => 255,
'null' => true,
])
->addColumn('description', 'string', [
'limit' => 255,
'null' => true,
])
->addTimestamps('created', 'modified')
->addIndex(['occurred_on'], ['name' => 'idx_transactions_occurred_on'])
->addIndex(['debit_account'], ['name' => 'idx_transactions_debit_account'])
->addIndex(['credit_account'], ['name' => 'idx_transactions_credit_account'])
->addIndex(['reference'], ['name' => 'idx_transactions_reference'])
->create();
}
}
Now, let's imagine we want to build a report to render the transaction amounts, but we also want a rolling total.
Using a window function, we could define a custom finder like this one:
public function findWindowReport(
SelectQuery $query,
?string $account,
?Date $from,
?Date $to
): SelectQuery
{
$q = $query
->select([
'id',
'occurred_on',
'debit_account',
'credit_account',
'amount_cents',
'currency',
'reference',
'description',
]);
// Optional filters
if ($account) {
$q->where(['debit_account' => $account]);
}
if ($from) {
$q->where(['occurred_on >=' => $from]);
}
if ($to) {
$q->where(['occurred_on <=' => $to]);
}
$runningWin = (new WindowExpression())
->partition('debit_account')
->orderBy([
'occurred_on' => 'ASC',
'id' => 'ASC'
]);
$q->window('running_win', $runningWin);
$q->select([
'running_total_cents' => $q
->func()->sum('amount_cents')
->over('running_win'),
]);
return $q->orderBy([
'debit_account' => 'ASC',
'occurred_on' => 'ASC',
'id' => 'ASC'
]);
}
Note the WindowExpression defined will sum the amount for each debit_account to produce the running_total_cents.
The result of the report, after formatting will look like this
Occurred On Debit Account Credit Account Amount (USD) Running Total (USD)
1/3/25 assets:bank:checking income:services $2,095.75 $2,095.75
1/3/25 assets:bank:checking income:sales $2,241.42 $4,337.17
1/7/25 assets:bank:checking income:services $467.53 $4,804.70
1/10/25 assets:bank:checking income:subscriptions $2,973.41 $7,778.11
1/12/25 assets:bank:checking income:sales $2,747.07 $10,525.18
1/17/25 assets:bank:checking income:subscriptions $2,790.36 $13,315.54
1/21/25 assets:bank:checking income:subscriptions $1,891.35 $15,206.89
1/28/25 assets:bank:checking equity:owner $353.00 $15,559.89Other typical applications of window functions are leaderboards (building paginated rankins with scores, sales, activities), analytics for cumulative metrics (like inventory evolution) and comparison between rows (to compute deltas) and de-duplication (to pick the most recent record for example).
This is a very useful tool to provide a solution for these cases, fully integrated into the CakePHP ORM.
This article is part of the CakeDC Advent Calendar 2025 (December 15th 2025)
