Join Relationships

Inline joins are often enough for one-off queries. When the same join path appears across multiple queries, JoinRelationships gives you a reusable, named relationship that you can apply with withRelation().

There are two withRelation() modes:

• pass a relationship name from JoinRelationships for reusable runtime lookup
• pass a JoinPath object (or chain) directly when you want compile-time table/alias widening

When to use this

Use join relationships when you want to:

• define common join paths once
• keep multi-query join logic consistent
• reuse multi-step joins without rewriting them
• override join type per query while keeping a shared base definition

Define relationships

Create a JoinRelationships registry during app startup:

import { createQueryBuilder, JoinRelationships } from '@hypequery/clickhouse';
import type { Schema } from './generated-schema';

const relationships = new JoinRelationships<Schema>();

relationships.define('orderCustomer', {
  from: 'orders',
  to: 'users',
  leftColumn: 'user_id',
  rightColumn: 'id',
  type: 'LEFT',
});

createQueryBuilder.setJoinRelationships(relationships);

export const db = createQueryBuilder<Schema>({
  host: process.env.CLICKHOUSE_HOST!,
});

Each relationship includes:

• from: source table
• to: joined table
• leftColumn: column on the source side
• rightColumn: column on the joined table
• type: optional default join type
• alias: optional default alias

Use a relationship in a query

Apply the relationship with withRelation():

const rows = await db
  .table('orders')
  .withRelation('orderCustomer')
  .select([
    'orders.id',
    'orders.total',
    'users.name',
    'users.email',
  ])
  .execute();

This is equivalent to writing the join inline, but the join path now lives in one reusable place.

Define relationship chains

Use defineChain() when a relationship should apply multiple joins together:

const relationships = new JoinRelationships<Schema>();

relationships.defineChain('orderCustomerRegion', [
  {
    from: 'orders',
    to: 'users',
    leftColumn: 'user_id',
    rightColumn: 'id',
    type: 'INNER',
  },
  {
    from: 'users',
    to: 'regions',
    leftColumn: 'region_id',
    rightColumn: 'id',
    type: 'LEFT',
  },
]);

Then apply the whole chain in one call:

const rows = await db
  .table('orders')
  .withRelation('orderCustomerRegion')
  .select([
    'orders.id',
    'users.name',
    'regions.region_name',
  ])
  .execute();

Override join options per query

You can override the join type without redefining the relationship:

const rows = await db
  .table('orders')
  .withRelation('orderCustomer', { type: 'INNER' })
  .select([
    'orders.id',
    'users.name',
  ])
  .execute();

This is useful when the shared relationship is usually LEFT, but one query needs stricter matching.

alias can be defined on the relationship itself for SQL generation.

• For string-based registry lookups such as withRelation('orderCustomer'), TypeScript cannot inspect the stored relationship shape, so alias/table widening is runtime-only.
• For direct JoinPath usage, withRelation() does widen the builder type for joined tables and aliases.
• Inline joins are still a good choice for one-off joins, but direct JoinPath usage is fully typed when you want reusable path objects with compile-time widening.

Alias override is only supported for single-join relationships. For defineChain() relationships, each step should define its own alias explicitly if needed.

Typed direct-path usage

If you want withRelation() to widen the builder type for joined-table or aliased column selection, pass a JoinPath directly instead of looking it up by string name:

import type { JoinPath } from '@hypequery/clickhouse';

const orderCustomerPath = {
  from: 'orders',
  to: 'users',
  leftColumn: 'user_id',
  rightColumn: 'id',
  alias: 'customer',
} as const satisfies JoinPath<Schema>;

const rows = await db
  .table('orders')
  .withRelation(orderCustomerPath)
  .select([
    'orders.id',
    'customer.name',
  ])
  .execute();

String-based registry lookups remain convenient for reuse, but their widening is runtime-only because the relationship name is not available to TypeScript.

Initialization requirements

Call createQueryBuilder.setJoinRelationships(...) before using withRelation().

If relationships are not registered, hypequery throws:

Join relationships have not been initialized. Call QueryBuilder.setJoinRelationships first.

If a relationship name does not exist, hypequery throws:

Join relationship 'orderCustomer' not found

Best practices

• Register relationships once during app startup, not inside query functions.
• Use semantic names such as orderCustomer or orderCustomerRegion, not generic names like join1.
• Prefer inline joins for one-off queries and relationships for stable, reused join paths.
• Keep chains small and intentional so query behavior stays readable.

See Also

• Joins
• Subqueries & CTEs
• Query Builder API