Routier

Appearance

Joining Collections ​

Pair rows from two collections on a matching key with join and leftJoin.

Quick Navigation ​

A First Join ​

ts
const pairs = await ctx.players
  .join(s => s.playerMatches, p => p._id, m => m.playerId)
  .toArrayAsync();

s is the store the collection belongs to. A store can see its own collections, so a sibling is named once — and because the selector is checked against your store's type, a wrong name is a compile error rather than a query that returns nothing.

Each result is a [player, match] pair — one for every match a player has. For example, given:

ts
players = [
  { _id: "p1", name: "James" },
  { _id: "p2", name: "Lin" },
];

playerMatches = [
  { _id: "m1", playerId: "p1", score: 42 },
  { _id: "m2", playerId: "p1", score: 18 },
];

The inner join above returns:

ts
[
  [
    { _id: "p1", name: "James" },
    { _id: "m1", playerId: "p1", score: 42 },
  ],
  [
    { _id: "p1", name: "James" },
    { _id: "m2", playerId: "p1", score: 18 },
  ],
]

p1 appears twice because it has two matches. p2 is absent because an inner join drops an outer row with no match. With leftJoin, the same data adds one more tuple:

ts
[
  { _id: "p2", name: "Lin" },
  undefined,
]

The tuple is an actual two-element JavaScript array, so destructuring works naturally:

ts
for (const [player, match] of pairs) {
  console.log(player.name, match.score);
}

The two selectors name the key on each side. They must be single property paths, and they must agree on type: joining a string key to a number key does not compile.

Left Joins ​

leftJoin keeps rows from the left side that match nothing, pairing them with undefined:

ts
const pairs = await ctx.players
  .leftJoin(s => s.playerMatches, p => p._id, m => m.playerId)
  .toArrayAsync();

for (const [player, match] of pairs) {
  if (match == null) {
    console.log(`${player.name} has not played yet`);
  }
}

The unmatched half is undefined — never an entity whose properties are all null.

Group Joins ​

groupJoin returns one result per left row instead of one per pair: the row, and an array of every right row that matches it. With the players and matches from A First Join:

ts
[
  [{ _id: "p1", name: "James" }, [{ _id: "m1", score: 42 }, { _id: "m2", score: 18 }]],
  [{ _id: "p2", name: "Lin" }, []],
]

It's the shape a screen usually wants (each player with their matches), without regrouping pairs yourself:

ts
import { DataStore } from "@routier/datastore";
import { MemoryPlugin } from "@routier/memory-plugin";
import { s } from "@routier/core/schema";

const playerSchema = s
    .define("players", {
        _id: s.string().key().identity(),
        name: s.string(),
    })
    .compile();

const playerMatchSchema = s
    .define("playerMatches", {
        _id: s.string().key().identity(),
        playerId: s.string(),
        score: s.number(),
    })
    .compile();

export class LeagueStore extends DataStore {
    players = this.collection(playerSchema).proxy().create();
    playerMatches = this.collection(playerMatchSchema).proxy().create();

    constructor() {
        super(new MemoryPlugin("league"));
    }
}

export const playersWithMatches = (ctx: LeagueStore) =>
    ctx.players
        .groupJoin((s) => s.playerMatches, (p) => p._id, (m) => m.playerId)
        .sort(([player]) => player.name)
        .map(([player, matches]) => ({
            ...player,
            matches,
            best: Math.max(0, ...matches.map((m) => m.score)),
        }))
        .toArrayAsync();
  • Every left row appears exactly once. A row with no matches, including one whose key is null, gets an empty array. A group join never drops a left row, so it behaves like leftJoin.
  • Everything after it works on groups. where, sort, map, skip, take, first and count see [row, matches[]], so count() counts left rows and take(10) returns ten left rows with all of their matches. A condition on the array, such as ([p, matches]) => matches.length > 2, is allowed.
  • A window before it limits the left rows. .sort(...).take(10).groupJoin(...) returns the first ten players, each with all of their matches.
  • Order is undefined without sort, and that includes the order of the matches inside each array. Sort the array in map if its order matters.
  • The key rules, the inner side's scopes and soft delete, cross-store joins, and read-only results are the same as for join.

It runs as a left join on every backend, and Routier groups the pairs before anything chained after it runs. The cost is a left join plus one pass over the pairs.

Naming The Inner Side ​

Two forms, and the difference is only which store the inner collection lives on:

ts
// Same store — a selector over it. The everyday form.
ctx.players.join(s => s.playerMatches, p => p._id, m => m.playerId)

// A different store — the collection itself.
localStore.players.join(remoteStore.playerMatches, p => p._id, m => m.playerId)

The selector runs when the query is built, not when it executes, so it costs nothing and its mistakes surface immediately.

What You Get Back ​

A join returns tuples, and each half is a fully deserialized entity of its own collection: dates are Dates, renamed columns carry their in-memory names, computed properties are present.

Two things follow from a tuple not being a row:

  • Results are read-only projections. They do not attach to the change tracker, exactly like map results. Assigning to player.name on a joined pair changes nothing and saves nothing — read the row through its own collection to modify it.
  • Order is undefined without sort. Backends pair rows in different orders, and that is the only difference between them you can observe. Sort when order matters.

Working With The Pairs ​

Everything you chain after a join operates on the pairs:

ts
const rows = await ctx.players
  .join(s => s.playerMatches, p => p._id, m => m.playerId)
  .where(([p, m]) => p.rank > 10 && m.won === true)
  .sort(([p, m]) => p.rank)
  .map(([p, m]) => ({ name: p.name, matchId: m._id }))
  .toArrayAsync();

A where after the join can compare the two sides to each other, which is the only place such a condition can go:

ts
// A condition spanning both sides of the pair: expressible only after the join.
ctx.players
  .join(s => s.playerMatches, p => p._id, m => m.playerId)
  .where(([p, m]) => m.score > p.averageScore)
  .toArrayAsync();

A where after the join is split where it can be: any && part of it that mentions one side only also narrows that side's read, while the whole condition still decides the pairs. So ([p, m]) => p.region === "east" && m.rank > 10 reads only eastern teams and only members above rank 10, without you having to say so twice. A part naming both sides stays where it is, because it cannot be answered by either read alone.

A where before the join filters the left side, and is pushed to the database the same way any other filter is. Either place is correct; before the join is clearer when the condition is only about the left side:

ts
// Reads only the players in question, then joins
ctx.players.where(p => p.region === "east").join(s => s.playerMatches, p => p._id, m => m.playerId)

count counts pairs, not left-side rows:

ts
// How many player-match pairs exist
await ctx.players.join(s => s.playerMatches, p => p._id, m => m.playerId).countAsync();

The terminal methods available on a join or group join are toArray/toArrayAsync, first/firstAsync, firstOrUndefined/firstOrUndefinedAsync, and count/countAsync. map() changes the result shape but deliberately keeps that same join terminal surface. sum, min, max, distinct, toGroup, remove, and subscribe are not exposed on JoinQueryable.

Key Rules ​

RuleBehaviour
EqualityStrict === on the two key values, compared as entities.
Key typeMust be string or number. A Date, boolean or object key throws when the query is built.
Null keysnull and undefined match nothing. Under leftJoin the row still appears, paired with undefined.
DuplicatesEvery matching pair is returned — two left rows sharing a key each pair with every matching right row.
Empty sideNo pairs from join; every left row with undefined from leftJoin.
ScopesBoth collections are read under their own softDelete and .scope() filters. A soft-deleted row on either side is not in the results.

A key selector has to be a property path — p => p._id, or p => p.team.id. Anything else throws when the query is built rather than when it runs, because a join with an unusable key has no partially-correct behaviour to fall back on. Conditions that are not key equality belong in where after the join.

Joining A View ​

A view is a join side like any collection:

ts
await ctx.products
  .join(store => store.productSummaries, p => p._id, summary => summary.productId)
  .toArrayAsync();

Joining Across Stores ​

Two collections on different plugins — a local cache and a remote store, two databases — join normally:

ts
await localStore.teams
  .join(remoteStore.members, t => t._id, m => m.teamId)
  .toArrayAsync();

Here the inner side is passed directly rather than selected. That is the one case the selector cannot express: s is the store the query started on, and it has no way to reach outside it. Both forms are accepted everywhere, so use whichever fits.

Neither plugin can read the other's rows, so Routier reads both sides and pairs them itself. The results are identical; the cost is two round trips instead of one.

What A Join Costs ​

The pairing itself is a hash join: one pass over each side, not a scan of one side per row of the other. Where it happens depends on the backend, and it never changes the answer:

BackendHow the join runs
SQLite, D1, PostgreSQL, MySQLA real INNER JOIN/LEFT JOIN, done by the engine.
Memory, file-system, browser-storageThe plugin reads both collections and pairs them in memory.
Dexie, PouchDB, MongoDBEach side is read through the plugin's normal query path — indexes and all — and paired in the plugin.
HTTP / replicationTwo ordinary requests, one per collection, paired in the plugin. No server needs to know what a join is.
Two different plugins or storesRoutier reads both sides and pairs them itself.

What a join reads is worth thinking about. A where recorded before the join narrows the left side, and the right side always keeps its collection scopes. Routier also analyzes top-level && conjuncts in a post-join where: a conjunct that names only one side narrows that side's read, while the complete predicate still checks every resulting pair. Cross-side conditions cannot be pushed down and run only over the pairs.

One thing an engine-side join gets for free: a sort, skip or take recorded before the join is applied to the left rows, not to the pairs. .sort(...).take(2).join(...) pairs the first two left rows, on every backend — the same answer the in-memory join gives.

Where Routier can, it also narrows the right-hand read to keys the left side actually has, rather than reading that collection whole. It stops doing so past semiJoinKeyThreshold distinct keys (default 500), where a long key list costs more than the scan it saves:

ts
new MyStore(plugin, { semiJoinKeyThreshold: 2000 })

Purely a cost knob — the pairs are identical either way.

Not Supported Yet ​

  • A join whose right-hand collection has a scope on an unmapped property is refused on SQL backends rather than pushed down: there is no column to compare, so the join would return rows that scope excludes. Nothing silently falls back — a wrong join is worse than a missing one.
  • The SWR plugin (HttpSwrDbPlugin) refuses a join: it merges a local read with a remote one, and the two would disagree about whether a row is an entity or a pair. Use HttpDbPlugin.
  • Subscriptions are not available on a join — the returned query has no subscribe.
  • Three or more collections in one join.

Released under the MIT License.

Copyright © Routier contributors