Native subtree (descendant) matching for hierarchical taxonomy where filters

[MA2153]

Type: Feature (additive, backwards-compatible)

Summary

Add a subtree operator to collection where taxonomy filters so selecting a parent term matches that term and all of its descendants, resolved in SQL:

await getEmDashCollection("listings", {
  where: { region: { subtree: "europe" } }, // europe + every descendant region
  status: "published",
  limit: 24,
});

Today the taxonomy where filter matches by exact slug only. There is no way to express "this term or anything filed under it" — the natural meaning of selecting a parent category in a faceted browse UI.

The problem

The only workaround for "term-or-descendants" is to enumerate the whole subtree on the client and pass every descendant slug. The loader expands that to one bound parameter per slug:

... AND t.slug IN (?, ?, ?, …)   -- one bound param per descendant

That collides with a hard platform limit:

• D1 caps bound parameters at 100 per query. A deep hierarchical taxonomy subtree easily exceeds that — selecting a top-level term can mean hundreds of descendant slugs → D1_ERROR: too many SQL variables.
• It reproduces locally too: workerd's emulated D1 enforces the same cap, so it fails in plain pnpm dev, not only in production.

This can't be fixed cleanly in the consumer:

• The where API exposes only exact-slug matching, so "descendant of X" is inexpressible without enumerating the subtree — which is exactly what overflows.
• Chunking the slug list into ≤100 batches breaks keyset pagination (limit/cursor can't span IN-chunked queries and still return one correctly-ordered page).
• The hierarchy already lives in the DB (taxonomies.parent_id); recomputing the subtree in the consumer and shipping hundreds of slugs across the call boundary is the wrong layer.

Proposed solution

Resolve the subtree server-side from a single root slug, so the bound-parameter count is independent of subtree size:

• A new operator object on the existing WhereValue shape — { subtree: slug | slug[] } — alongside today's exact ["slug"] form. Purely additive; existing filters are unchanged.
• The loader matches content_taxonomies.taxonomy_id against the set of translation-groups in the subtree, produced by a recursive walk of taxonomies.parent_id. After fix: store translation_group in taxonomy parent_id to stop cross-locale parent leak #1646 both columns live in translation_group space, so the walk is locale-correct and matching needs no extra join.
• Naturally portable: the recursive CTE is standard SQL on both supported dialects (SQLite/D1 and Postgres).

Secondary (opt-in): a rollup option on getTaxonomyTerms (and the admin terms endpoint) that returns distinct-entry subtree counts, so a facet badge equals what selecting that facet actually returns. Default counts are unchanged.

Why it's low-risk

• Backwards-compatible / additive. New optional operator and option; default code paths behave exactly as before. No migration of its own (it builds on fix: store translation_group in taxonomy parent_id to stop cross-locale parent leak #1646's parent_id semantics).
• Bounded blast radius. The subtree path is inert unless subtree: is present in a where clause.
• SQL-injection-safe (parameterized values, sql.ref() identifiers), and counts stay honest via COUNT(DISTINCT entry_id).

Status

A complete, reviewed implementation is ready to open as a PR once this is approved: the subtree operator, the distinct-count rollup, dialect-parity tests (including a >999-descendant overflow regression guard and a distinct-count honesty test), and a changeset.

Questions for maintainers

1. Does this align with the project's direction for the where API?
2. Any preference on the operator surface — { subtree: … } vs. { descendantsOf: … } — before the PR?

Happy to adjust the API shape to match your conventions.

[MA2153]

PR is up: #1648