Skip to content
Source
Sign inSign up
TanStack/db

Forward relation filters to loadSubset as an optional relations hint #2124

Send to my agent
OpenKyleAMathews(GitHub profile, opens in a new tab) opened this issue 1 comment

Problem

#1909 lets an ordered, limited query with a filter on a direct LEFT-joined source load root rows a page at a time. The adapter still receives only the root's own predicate. When few root rows pass the relation filter, the query pages through much of the source. For example, if 1 in 100 rows matches, a window of 20 rows reads about 2,000 root rows.

Proposal

This is step 1 of #1898, which #1909 did not implement. Add an optional hint to LoadSubsetOptions:

relations?: Array<{
  quantifier: 'some' | 'none'
  collectionId: string
  on: { parent: Array<string>; child: Array<string> }
  where?: BasicExpression<boolean>
}>
  • every(p) is none(not p).
  • An adapter can apply the hint and return only root rows that pass. It can also ignore the hint and return a superset. It must never drop a root row that passes.
  • Local evaluation stays authoritative. Correctness does not depend on the adapter.
  • getLoadSubsetDemandKey must include relations, so that a narrowed load never covers the same load without the hint.

Examples of backends that can apply the hint:

  • SQL: EXISTS (...) and NOT EXISTS (...).
  • PostgREST: select=*,children!inner(...) for some, and a filtered embed with children=is.null for none.

Evidence

The prototype in #1898 (fake on-demand backends, 40 root rows, some, take 5):

TodayHint appliedHint ignored
1 request, 40 rows2 requests, 6 rows6 requests, 13 rows

Open questions

  • Does the hint also apply to unlimited queries?
  • How do relation predicates under or or not behave, and joins through subqueries that join again?
  • Which adapters implement the hint first?

This is new public API. It needs a decision on its shape before implementation.

This issue was written by Isaac.

  1. We've been running this design in production as a patch since September, rebuilt on 0.15.2's joined-filter paging. It uses the same shape as this issue:

    type LoadSubsetRelation = {
      quantifier: 'some' | 'none'
      collectionId: string
      on: { parent: Array<string>; child: Array<string> }
      where?: BasicExpression<boolean>
    }

    It's forwarded on every loadSubset of the window's subscription, including boundary-tie loads, and it's part of getLoadSubsetDemandKey. It's derived only when the window already pages as a joined filter, and only when every cross-alias conjunct maps to a relation; otherwise no hint is sent. Conjuncts map like this:

    • an existence check on the join key → some/none;
    • a conjunct over the joined row that fails on a missing row → some(where);
    • one that passes on a missing row → none(not where), but only when the join is on the joined collection's key.

    Our InstantDB adapter pushes it as a link path. Measured on a page of 5 out of 40 rows: 40 rows load without the hint, and 6 with it applied. Tests cover the hint being applied and ignored, to-one and existence checks, a filter-subquery join (#2242), and demand-key separation. Happy to open a PR if this matches what you had in mind.

Sign in to join this conversation.