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 { 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', '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', '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', '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 SELECT * when neither is set (returns the FROM-side row).

Reading without .selectJoined

If you skip .selectJoined, the result row stays the FROM-side row type. The joined columns ARE on the row at runtime (postgres returns them flat with alias.col keys) but TypeScript doesn't see them. For ad-hoc reads:

const rows = await ctx.store.query(
  queryFor(database.posts).as('c')
    .innerJoin(database.posts, 'p', eq('p.id', 'c.parentId'))
    .descriptor,
)
// rows[0] is typed Post
// rows[0]['p.title'] exists at runtime but needs an explicit cast

Use .selectJoined({...}) for the typed path; the cast escape-hatch is for one-off reads.

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