VoltroDocumentation

Self-joins + aliased projections

Join a table to itself or to a CTE. selectJoined({...}) for aliased projections with full TypeScript inference.

When you need to reference the same table twice in one query — parent/child trees, before/after comparisons, follower/followee graphs — use .as(alias) + .innerJoin(table, alias, on).

For non-self joins (a table joined to a DIFFERENT table) the preferred pattern is eager-loading via relations. This page covers the cases where eager-load doesn't fit: self-joins, joining against a CTE, or joining against a dynamically- named table.

Quick start — self-join

import { columnRef, eq, queryFor } from '@voltro/database'

// Posts and their parent posts
const tree = await ctx.store.query(
  queryFor(database.posts).as('children')
    .innerJoin(database.posts, 'parents', eq('parents.id', columnRef('children.parentId')))
    .selectJoined({
      childId:     'children.id',
      childTitle:  'children.title',
      parentTitle: 'parents.title',
    })
    .descriptor,
)
// rows: Array<{ childId: string; childTitle: string; parentTitle: string }>

Compiles to:

SELECT
  "children"."id"    AS "childId",
  "children"."title" AS "childTitle",
  "parents"."title"  AS "parentTitle"
FROM "posts" AS "children"
INNER JOIN "posts" AS "parents" ON "parents"."id" = "children"."parentId"

.as(alias) — aliasing the FROM table

.as('children') emits FROM "posts" AS "children". Required when combining with .innerJoin(...) so the JOIN's ON condition can disambiguate columns from both sides.

.innerJoin() vs .leftJoin()

Same shape, different semantics:

  • .innerJoin(target, alias, on) — drops rows from the FROM side that have no JOIN-side match.
  • .leftJoin(target, alias, on) — keeps rows from the FROM side even when no JOIN-side match exists. The aliased row's columns will be null for those rows.

Both have two overloads:

Pass a Table descriptor → full type inference

queryFor(database.posts).as('children')
  .innerJoin(database.posts, 'parents', eq('parents.id', columnRef('children.parentId')))
  .selectJoined({
    parentTitle: 'parents.title',          // ← type inferred as string
  })
// rows[0].parentTitle is typed `string`, not `unknown`

The framework tracks the joined Table's row type in a hidden Joins type parameter. .selectJoined({...}) reads it to resolve each 'alias.column' source to the joined column's actual type.

Pass a string name → opaque type fallback

queryFor(database.posts).as('children')
  .innerJoin('some_cte', 'cte', eq('cte.userId', columnRef('children.userId')))
  .selectJoined({
    fromCte: 'cte.value',                  // ← type stays `unknown`
  })

Use the string form for CTE references (the framework doesn't have the CTE's row type at compile time) or when joining against a name that's only known at runtime.

.selectJoined({...}) — picking columns

The chain method has TWO mutually-exclusive projection forms:

  • .select('col1', 'col2') — for FROM-side rows only. Result row type narrows to Pick<RowOf, 'col1' | 'col2'>.
  • .selectJoined({outKey: 'alias.col', ...}) — for queries with joins. Each spec entry maps a '<alias>.<column>' source to an output key on the result row.

Mixing isn't supported; pick one. The framework defaults to the FROM-side columns when neither is set.

Column references and result shape

Use columnRef('alias.column') for the right-hand column of eq, neq, gt, gte, lt or lte. A plain string remains a literal value, even when it contains a dot. References are serializable data; no SQL expression is interpolated from the column name.

A comparison involving NULL is unknown and does not match, including when wrapped in not(...). Use isNull when NULL itself is the intended match.

Without .selectJoined, the result contains only the FROM-side columns. Use .selectJoined({ parentTitle: 'parents.title' }) to request joined values; joined fields are not added as hidden alias.column keys.

The memory store executes inner/left joins, self-joins and aliased projections, including transaction-local writes. Reactive reads track both joined tables; a change to either side recomputes the result. Use a unique id in a reactive joined projection so row patches can identify each result row.

Cross-dialect

Standard SQL — INNER JOIN ... AS ... ON ... is supported on every dialect we ship with identical syntax.

When NOT to use this

  • Joining different tables related via FK — use eager-loading with relations + .with({}). More ergonomic, gives you the nested-object result shape, and reactively subscribes to the joined tables.
  • Walking trees more than 1 level deep — use a recursive CTE. Self-join only covers parent + immediate child.

See also