Relationships

Data is rarely stored in a single table. You often need to fetch Orders with their User, or Products with their Category.

The TableCraft Engine makes joining tables simple and type-safe.

1

Basic Join

To join another table, use the .join() method.

// src/config/orders.ts
import { defineTable } from '@tablecraft/engine';
import { orders, users } from '../db/schema';

export const orderConfig = defineTable(orders)
  .join(users, {
    on: 'orders.user_id = users.id', // SQL-like condition
    type: 'left',                    // 'left' | 'inner' | 'right' | 'full'
    columns: ['email', 'name'],      // Columns to select from Users
  })
  .toConfig();

When you query orders, the response will include users.email and users.name.

2

Filtering by Related Data

Once joined, you can filter the main table based on the related table's columns.

// Allow filtering orders by the user's email
export const orderConfig = defineTable(orders)
  .join(users, {
    on: 'orders.user_id = users.id',
    columns: ['email'],
  })
  .filter('users.email') // Enable filtering on this joined column
  .toConfig();

API Request: GET /orders?filter[users.email]=[e-mail]

This will return only the orders belonging to Jane.

3

Sorting by Related Data

You can also sort the main list based on a column in the joined table.

export const orderConfig = defineTable(orders)
  .join(users, {
    on: 'orders.user_id = users.id',
    columns: ['name'], // We need the name to sort by it
  })
  .sortable('users.name') // Allow sorting by user name
  .toConfig();

API Request:

• GET /orders?sort=users.name (A-Z by Customer Name)

• GET /orders?sort=-users.name (Z-A)

Advanced Join Options

Custom Alias

If you join the same table twice (e.g., creator and assignee are both Users), use an alias.

.join(users, {
  alias: 'creator',
  on: 'tasks.creator_id = creator.id',
  columns: ['name'],
})
.join(users, {
  alias: 'assignee',
  on: 'tasks.assignee_id = assignee.id',
  columns: ['name'],
})

Complex Conditions

Next Steps

Learn how to use Advanced Configuration like computed columns and custom transforms.