> hypequery

Join Relationships

Define reusable join relationships with JoinRelationships and withRelation

Join Relationships

While basic joins work well for simple queries, larger applications benefit from defining reusable relationships. This page shows how to use JoinRelationships to create consistent, maintainable join patterns across your queries.

Overview

Use join relationships to:

  • Define reusable join paths once
  • Maintain consistency across multiple queries
  • Simplify complex multi-table joins
  • Make queries more readable and semantic

Basic Joins vs Relationships

Basic inline joins are perfect for simple cases:

const results = await ctx.db
  .table('orders')
  .innerJoin('users', 'user_id', 'users.id')
  .select(['orders.id', 'users.name', 'orders.amount'])
  .execute();

But when multiple queries use the same join pattern, defining it once as a relationship is cleaner:

import { JoinRelationships } from '@hypequery/clickhouse';

const relationships = new JoinRelationships<Schema>();

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

// Now reuse in any query
const results = await ctx.db
  .table('orders')
  .withRelation('userOrders')
  .select(['orders.id', 'users.name', 'orders.amount'])
  .execute();

Defining Relationships

Single Relationship

Define a relationship with a name, tables, columns, and join type:

const relationships = new JoinRelationships<Schema>();

relationships.define('userOrders', {
  from: 'orders',        // Source table
  to: 'users',          // Target table
  leftColumn: 'user_id', // Column in 'from' table
  rightColumn: 'id',     // Column in 'to' table
  type: 'LEFT',         // Join type: INNER, LEFT, RIGHT, or FULL
});

Multiple Relationships

Define all your relationships at startup:

const relationships = new JoinRelationships<Schema>();

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

relationships.define('userProfile', {
  from: 'profiles',
  to: 'users',
  leftColumn: 'user_id',
  rightColumn: 'id',
  type: 'INNER',
});

// Order relationships
relationships.define('orderItems', {
  from: 'orders',
  to: 'order_items',
  leftColumn: 'id',
  rightColumn: 'order_id',
  type: 'INNER',
});

relationships.define('orderInvoice', {
  from: 'orders',
  to: 'invoices',
  leftColumn: 'id',
  rightColumn: 'order_id',
  type: 'LEFT',
});

Registering Relationships

Before using relationships in queries, register them globally:

import { createQueryBuilder, JoinRelationships } from '@hypequery/clickhouse';

const relationships = new JoinRelationships<Schema>();

// ... define relationships ...

// Register ONCE during app initialization
createQueryBuilder.setJoinRelationships(relationships);

// Now all query builders can use withRelation()
export const db = createQueryBuilder<Schema>({ ... });

Important

Call QueryBuilder.setJoinRelationships() exactly once during startup, before you initialize any query definitions or execute queries.

Using Relationships

Basic Usage

Use withRelation() to apply a predefined relationship:

const results = await ctx.db
  .table('orders')
  .withRelation('userOrders')
  .select(['orders.id', 'users.name', 'orders.total'])
  .execute();

Multiple Relationships

Chain multiple withRelation() calls for complex joins:

const results = await ctx.db
  .table('orders')
  .withRelation('userOrders')      // Join users
  .withRelation('orderItems')      // Join order_items
  .withRelation('orderInvoice')    // Join invoices
  .select([
    'orders.id',
    'users.name',
    'order_items.sku',
    'invoices.invoice_number'
  ])
  .execute();

Filtering with Relationships

Once a relationship is applied, you can filter on any joined table:

const results = await ctx.db
  .table('accounts')
  .withRelation('ownerUser')
  .where('accounts.active', 'eq', 1)
  .where('users.role', 'eq', 'admin')  // Filter on joined table
  .select(['accounts.id', 'accounts.name', 'users.role'])
  .execute();

Aggregations with Relationships

Relationships work seamlessly with grouping and aggregations:

const results = await ctx.db
  .table('orders')
  .withRelation('userOrders')
  .select(['users.user_name'])
  .sum('orders.total', 'total_spent')
  .count('orders.id', 'order_count')
  .avg('orders.total', 'avg_order_value')
  .groupBy(['users.user_name'])
  .orderBy('total_spent', 'DESC')
  .execute();

Overriding Relationship Options

Override relationship options per-query while reusing the base definition:

// Default relationship is LEFT
const results = await ctx.db
  .table('orders')
  .withRelation('userOrders', { type: 'INNER' })  // Override to INNER
  .select(['orders.id', 'users.name'])
  .execute();

// Override the alias
const results = await ctx.db
  .table('orders')
  .withRelation('userOrders', { alias: 'customers' })
  .select(['orders.id', 'customers.name'])
  .execute();

Mixing Inline Joins and Relationships

You can mix both approaches. Use relationships for stable, reused patterns and inline joins for one-off cases:

const results = await ctx.db
  .table('orders')
  .withRelation('userOrders')        // Predefined relationship
  .leftJoin('regions', 'region_id', 'regions.id')  // Inline join for this query only
  .select([
    'orders.id',
    'users.name',
    'regions.region_name'
  ])
  .execute();

Best Practices

1. Use Semantic Names

Name relationships based on their business meaning, not just table names:

// Good - semantic and descriptive
relationships.define('userOrders', { ... });
relationships.define('orderItems', { ... });
relationships.define('productInventory', { ... });

// Avoid - too generic
relationships.define('usersJoin', { ... });
relationships.define('itemsJoin', { ... });

2. Organize by Domain

Group relationships by domain or feature:

// User domain
relationships.define('userProfile', { ... });
relationships.define('userOrders', { ... });
relationships.define('userSubscriptions', { ... });

// Order domain
relationships.define('orderItems', { ... });
relationships.define('orderShipping', { ... });
relationships.define('orderPayments', { ... });

3. Define Relationships Early

Set up relationships during app initialization, not ad-hoc in query files:

// lib/relationships.ts
import { JoinRelationships } from '@hypequery/clickhouse';
import type { Schema } from './schema';

export const relationships = new JoinRelationships<Schema>();

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

  // ... more relationships
}

// app.ts
import { createQueryBuilder } from '@hypequery/clickhouse';
import { relationships, initRelationships } from './lib/relationships';

initRelationships();
createQueryBuilder.setJoinRelationships(relationships);

4. Test Relationships Individually

When debugging complex multi-join queries, test each relationship separately:

// Test each relationship works
const test1 = await db.table('orders')
  .withRelation('userOrders')
  .select(['orders.id', 'users.name'])
  .execute();

const test2 = await db.table('orders')
  .withRelation('orderItems')
  .select(['orders.id', 'order_items.sku'])
  .execute();

// Now combine them
const combined = await db.table('orders')
  .withRelation('userOrders')
  .withRelation('orderItems')
  .select(['orders.id', 'users.name', 'order_items.sku'])
  .execute();

Next Steps

  • Joins - Learn basic join syntax and types
  • Advanced - Explore CTEs and raw SQL
  • Where - Filter query results with conditions

Joins

Combine data from multiple tables

Aggregation

Group data and calculate summaries with aggregations

On this page

Join RelationshipsOverviewBasic Joins vs RelationshipsDefining RelationshipsSingle RelationshipMultiple RelationshipsRegistering RelationshipsUsing RelationshipsBasic UsageMultiple RelationshipsFiltering with RelationshipsAggregations with RelationshipsOverriding Relationship OptionsMixing Inline Joins and RelationshipsBest Practices1. Use Semantic Names2. Organize by Domain3. Define Relationships Early4. Test Relationships Individually