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.89 Other 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)

This article is part of the CakeDC Advent Calendar 2025 (December 2th 2025)

CounterCacheBehavior in CakePHP: what it is, when to use it, and what’s new in CakePHP 5.2

As your application grows, a very common pattern appears: you need to display things like “number of comments”, “number of tasks”, or “number of orders”, and you need to do it fast. Calculating these values with COUNT() queries can work until performance starts to suffer (and complexity increases because of filters, states, or joins). This is exactly where CounterCacheBehavior* becomes useful.

What is CounterCacheBehavior?

CounterCacheBehavior is a CakePHP ORM behavior that keeps a counter field in a “parent” table synchronized based on the records in a related table. Typical example:

• Articles hasMany Comments
• You want to store the number of comments in articles.comment_count

The behavior automatically increments, decrements, or recalculates that value when related records are created, deleted, or modified.

When should you use it?

Common use cases include:

• Listings with counters (e.g. “Posts (123 comments)”).
• Sorting by counters (most commented, most active, etc.).
• Filtering by counters (categories with more than X products).
• Avoiding repeated and expensive COUNT( ) queries.

The idea is simple: accept a small cost on writes in exchange for much faster reads.

Basic configuration

CounterCache is configured in the child table (the one that belongs to the parent). If Comments belongsTo Articles, the behavior lives in CommentsTable. // src/Model/Table/CommentsTable.php namespace App\Model\Table; use Cake\ORM\Table; class CommentsTable extends Table { public function initialize(array $config): void { parent::initialize($config); $this->belongsTo('Articles'); $this->addBehavior('CounterCache', [ 'Articles' => ['comment_count'] ]); } } Doing this, CakePHP will automatically keep articles.comment_count up to date.

CounterCache with conditions (scoped counters)

Often you don’t want to count everything, but only a subset: published comments, active records, non-spam items, etc. $this->addBehavior('CounterCache', [ 'Articles' => [ 'published_comment_count' => [ 'conditions' => ['Comments.is_published' => true] ] ] ]); This pattern is very useful for dashboards such as:

• open issues.
• completed tasks.
• approved records.

CounterCache with callbacks (custom calculations)

In some cases, conditions are not enough and you need more complex logic (joins, dynamic filters, or advanced queries). CounterCacheBehavior allows you to define a callable to calculate the counter value. Important: when using callbacks, bulk updates with updateCounterCache() will not update counters defined with closures. This is an important limitation to keep in mind.

What’s new in CakePHP 5.2: rebuild counters from the console

Before CakePHP 5.2, rebuilding counters often meant writing your own scripts or commands, especially after:

• bulk imports done directly in the database.
• manual data fixes.
• adding a new counter cache in production.
• data becoming out of sync.

New command: bashbin/cake counter_cache CakePHP 5.2 introduced an official command to rebuild counter caches: bin/cake counter_cache --assoc Comments Articles This command recalculates all counters related to Comments in the Articles table. Processing large tables in batches For large datasets, you can rebuild counters in chunks: bin/cake counter_cache --assoc Comments --limit 100 --page 2 Articles When using --limit and --page, records are processed ordered by the table’s primary key. This command is ideal for maintenance tasks and for safely backfilling new counter caches without custom tooling.

What’s new in CakePHP 5.2: bulk updates from the ORM

In addition to the console command, CakePHP 5.2 added a new ORM method: CounterCacheBehavior::updateCounterCache() This allows you to update counters programmatically, in batches: // Update all configured counter caches in batches $this->Comments->updateCounterCache(); // Update only a specific association, 200 records per batch $this->Comments->updateCounterCache('Articles', 200); // Update only the first page $this->Comments->updateCounterCache('Articles', page: 1); This is available since CakePHP 5.2.0.

Complete practical example: Articles and Comments

Assume the following database structure:

• articles: id, title, comment_count (int, default 0), published_comment_count (int, default 0).
• comments: id, article_id, body, is_published.

1) Behavior configuration in CommentsTable:

$this->addBehavior('CounterCache', [ 'Articles' => [ 'comment_count', 'published_comment_count' => [ 'conditions' => ['Comments.is_published' => true] ] ] ]);

2) Populate existing data (production)

After deploying, rebuild counters: bin/cake counter_cache --assoc Comments Articles From that point on, counters will stay synchronized automatically.

Best practices and Common Mistakes

Here you have some best practices and common mistakes:

• Add indexes to foreign keys (comments.article_id) and fields used in conditions (comments.is_published) for large datasets.
• If you perform direct database imports (bypassing the ORM), remember to rebuild counters using bin/cake counter_cache or updateCounterCache().
• Counters defined using closures are not updated by updateCounterCache().
• If a record changes its foreign key (e.g. moving a comment from one article to another), CounterCache handles the increments and decrements safely.

This article is part of the CakeDC Advent Calendar 2025 (December 2th 2025)

The Generational Perception of Work and Productivity in the Remote-Work Era

In the year 2020, everything changed when the world stopped completely during COVID-19. The perception of safety, health, mental health, work, and private life completely turned around and led to a different conception of the world we knew. As the global pandemic thrived, we saw how many jobs could be done from home, because people had to reinvent themselves as we were not able to go to our workplaces. And it settled a statement, changing the perception of work dramatically. Before it, and for older generations, work was associated with physical presence, rigid schedules, and productivity measured by visible hours. But after it, younger generations saw the potential of working from home or being a so-called digital nomad, giving more priority to flexibility, emotional well-being, and measuring efficiency through results. This change reflects a social evolution guided by new technologies, new expectations, and a more connected workforce. Remote work has been key in this transformation. For thousands of professionals, the ability to work from home meant reclaiming personal time, reducing stress, and achieving a healthier work--life balance (for example, by reducing commuting time most people get almost 2 extra hours of personal time). Productivity did not decrease --- in many cases, it actually improved --- because the focus shifted from "time spent" to "goals achieved." This model has also shown that trust and autonomy can lead to more engaged teams. However, despite all of the perks, many companies are apparently eager to return to traditional workplaces. Maybe it is the fear of losing control or a lack of understanding of the new work dynamics, but this tendency threatens to undo meaningful progress for generations that have already experienced the freedom and effectiveness of remote work. Going back to the old-fashioned way of work feels like a step backward. So now, the challenge is to find a middle ground that acknowledges the cultural and technological changes of our time, passing the torch to a new generation of workers. Because productivity is no longer measured by how many people are sitting in a chair, but by the value of the final results. And if we want organizations truly prepared for the future, we must listen to younger generations and build work models that prioritize both results and workers' well-being. In CakeDC we do believe in remote work! Proving through the years that work can be done remotely no matter the timezone or language.