Lazy Aggregates

Stored aggregate columns are normally eager: every insert, update, delete, and move recomputes the affected ancestor chain in the same transaction as the mutation. That keeps reads cheap (the column is already on the row) but pays the maintenance cost on every write.

A lazy aggregate flips that trade. Writes only invalidate the cached value (one fast UPDATE … SET col = NULL, col_computed_at = NULL over the ancestor chain); the first read past the invalidation recomputes the value with the same correlated SQL as withFreshAggregates(), writes it back, and stamps <column>_computed_at. Subsequent reads return the stored value with no extra work.

Use lazy aggregates when writes are bursty and reads on the affected subtree are sparse — typical in batch import paths, scheduled rebuilds, and admin tools.

1. Declaring a lazy column

use Vusys\NestedSet\Attributes\NestedSetAggregate;

#[NestedSetAggregate(column: 'articles_total', sum: 'articles', lazy: true)]
#[NestedSetAggregate(column: 'articles_count', count: true, lazy: true)]
class Category extends Model implements HasNestedSet
{
    use NodeTrait;
}

lazy: true works on the SQL kinds whose value can be recomputed by a single correlated subquery:

sum, count, min, max
distinctCount, stringAgg, jsonAgg, jsonObjectAgg, topK
bitOr, bitAnd, bitXor
Listener aggregates declared via #[NestedSetAggregateListener]

lazy: true is not supported on the companion-derived display kinds (avg, variance, stddev, weightedAvg, boolOr, boolAnd, geometricMean, harmonicMean) or fresh-only kinds (median, percentile). For an avg, declare sum and count companions lazy instead and compute the average at read time, or accept the eager avg column.

2. Migration shape

The nestedSetAggregate() Blueprint macro takes a lazy: flag that:

Makes the value column nullable (no default 0) — NULL is the signal for "needs recompute".
Adds a <column>_computed_at timestamp companion that tracks when the value was last refreshed.

Schema::create('categories', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->unsignedInteger('articles')->default(0);

    $table->nestedSet(cover: ['articles']);

    $table->nestedSetAggregate('articles_total', lazy: true);
    $table->nestedSetAggregate('articles_count', lazy: true);
});

This emits articles_total, articles_total_computed_at, articles_count, and articles_count_computed_at.

To declare the columns manually (e.g. with a different stamp name or non-default storage type), follow the same convention: the value column nullable, the stamp column nullable timestamp named <column>_computed_at.

3. TTL

By default a lazy column stays fresh until the next mutation invalidates it. Pass ttl: <seconds> to make the read accessor treat any stamped value older than that age as stale and recompute on access:

#[NestedSetAggregate(column: 'view_count', sum: 'views', lazy: true, ttl: 60)]

The TTL is a read-time policy, not a storage shape — the migration is identical to any other lazy column, and the value lives on the model attribute. TTL is useful when the source column changes through paths that don't go through the trait (raw UPDATE statements, queued workers, external pipelines) and you want a maximum staleness bound.

ttl requires lazy: true. ttl <= 0 throws AggregateConfigurationException at registry-build time.

4. Reading lazy values

Read the attribute as normal — the accessor handles the recompute transparently:

$root->articles_total;        // recompute + stamp on first read
$root->articles_total;        // stored value, no extra work
$child->save();               // invalidates ancestors
$root->articles_total;        // recompute + stamp again

4.1 The lifecycle visualised

A small tree, eager-equivalent SUM. Leaves carry articles=N. Ancestor rows carry display chips showing what's actually stored in the articles_total column right now, plus the stamp (articles_total_computed_at) — read these against the widget's Σ articles rollup, which always shows the correct rolled-up value.

Just after a fixAggregates() or recent read — every ancestor is fresh, stored matches the rollup, stamp is set:

Electronics {stored=fresh, stamp=12:00:00}
  Computers {stored=fresh, stamp=12:00:00}
    Laptops {articles=8}
    Desktops {articles=3}
  Phones {stored=fresh, stamp=12:00:00}
    iPhone {articles=12}
    Android {articles=7}

After a write hits Laptops — the package issues one UPDATE … SET articles_total = NULL, articles_total_computed_at = NULL WHERE lft <= Laptops.lft AND rgt >= Laptops.rgt over the ancestor chain. The rollup the widget shows is what the column will become on next read — the actual stored value right now is NULL:

Electronics {stored=NULL, stamp=NULL}
  Computers {stored=NULL, stamp=NULL}
    Laptops {articles=10}
    Desktops {articles=3}
  Phones {stored=fresh, stamp=12:00:00}
    iPhone {articles=12}
    Android {articles=7}

Note Phones is untouched — its subtree didn't include the write, so its cache is still valid. Lazy invalidation walks the ancestor chain only.

After $electronics->articles_total is read — the accessor sees NULL, runs the same correlated SQL as withFreshAggregates(), writes the result back, and stamps the companion. Subsequent reads return the stored value with no extra work:

Electronics {stored=fresh, stamp=12:05:00}
  Computers {stored=NULL, stamp=NULL}
    Laptops {articles=10}
    Desktops {articles=3}
  Phones {stored=fresh, stamp=12:00:00}
    iPhone {articles=12}
    Android {articles=7}

Reading $electronics only refreshed Electronics. Computers stays stale until its column is actually read — that's the trade-off: lazy maintenance defers work to the read site, so columns that nobody reads never pay the recompute cost.

The recompute uses the same correlated SQL as withFreshAggregates() and respects scope columns, soft-delete filters, and the aggregate's declared filter clause. It runs inside the read query's connection, not a new transaction.

To bypass the cache for one read (without invalidating the stored value), use withFreshAggregates():

Category::query()->withFreshAggregates()->find($id);

To force a refresh on the next read, manually null the stamp column:

DB::table('categories')->where('id', $id)
    ->update(['articles_total_computed_at' => null]);

5. Concurrency

Two concurrent readers landing on a stale row will each see a NULL value and run the recompute. The result is the same value written twice — no drift, no consistency issue, just a little wasted work. If your access pattern includes thundering-herd recomputes, route reads through a cache.

The invalidation UPDATE is one statement per inclusivity slice (inclusive + exclusive lazy columns batch separately). Soft-deleted ancestors are skipped — the same rule as eager DeltaMaintenance.

6. When not to use it

Eager columns are still the right default for read-heavy hierarchies — every read is a single column lookup, no SQL recompute. Reach for lazy when:

A single write triggers maintenance over a deep ancestor chain you rarely read.
You're importing thousands of nodes in a tight loop and read-time recompute is cheaper than eager-per-row.
You're rebuilding a subtree under withDeferredAggregateMaintenance() and want subtree reads outside the deferred block to remain fast without forcing a full repair.

If reads are frequent and writes are sparse, the lazy invalidation cost (still one UPDATE per write) doesn't earn its keep — stay eager.

#Lazy Aggregates

#1. Declaring a lazy column

#2. Migration shape

#3. TTL

#4. Reading lazy values

#4.1 The lifecycle visualised

#5. Concurrency

#6. When not to use it