Relationships

Relationships model how one dataset links to another. To-one relationships (belongsTo, hasOne) are queryable: dataset and metric queries can select, filter, and order by fields on the related dataset one hop deep, and hypequery executes the join for you. To-many relationships (hasMany) are metadata only.

Defining relationships

Declare relationships in the dataset config. The target is a lazy reference (() => Dataset) so datasets can reference each other without import-order problems. from is the join column on this dataset's table; to is the join column on the target's table.

import { dataset, dimension, measure, belongsTo, hasMany } from '@hypequery/datasets';

const Customers = dataset('customers', {
  source: 'customers',
  dimensions: {
    id: dimension.string(),
    country: dimension.string(),
    tier: dimension.string(),
  },
  measures: { customerCount: measure.countDistinct('id') },
});

const LineItems = dataset('line_items', {
  source: 'line_items',
  dimensions: {
    id: dimension.string(),
    sku: dimension.string(),
  },
});

const Orders = dataset('orders', {
  source: 'orders',
  dimensions: {
    id: dimension.string(),
    status: dimension.string(),
    amount: dimension.number(),
  },
  measures: {
    revenue: measure.sum('amount'),
  },
  relationships: {
    customer: belongsTo(() => Customers, { from: 'customer_id', to: 'id' }),
    items: hasMany(() => LineItems, { from: 'id', to: 'order_id' }),
  },
});

The three helpers describe where the foreign key lives:

• belongsTo — many-to-one; the FK is on this table (orders.customer_id → customers.id).
• hasOne — one-to-one; the FK is on the target table.
• hasMany — one-to-many; the FK is on the target table. Metadata only — see below.

Querying related fields

Reference to-one related dimensions as <relationship>.<dimension> anywhere a dimension name is accepted: dimensions, filters, and orderBy, in both dataset and metric queries.

const result = await analytics.execute(Orders, {
  dimensions: ['customer.country'],
  measures: ['revenue'],
  filters: [{ field: 'customer.tier', operator: 'eq', value: 'enterprise' }],
  orderBy: [{ field: 'customer.country', direction: 'asc' }],
});

// Rows are typed: result.data[0]['customer.country'] is string | undefined

Result rows key joined columns by their qualified name ('customer.country'), and the row types include them, so projections stay fully typed end to end — including through Serve endpoints and the React hooks.

Querying related measures

Dataset queries can select one-hop target base measures as <relationship>.<measure> through createDatasetClient({ queryBuilder: db }):

const customersByStatus = await analytics.execute(Orders, {
  dimensions: ['status'],
  measures: ['revenue', 'customer.customerCount'],
  orderBy: [{ field: 'customer.customerCount', direction: 'desc' }],
});
// result.data[0]['customer.customerCount'] is string | null | undefined

The target population consists of rows reached by the selected base rows. Tenant scoping, base filters, and segments determine which orders participate; the target measure's fixed filters apply to customer columns. Customers without matching orders never contribute. Unmatched orders retain their base measures but contribute no target aggregate input.

A belongsTo join repeats each customer for every matching order, so only duplicate-insensitive aggregations are selectable: countDistinct, approxCountDistinct, min, max, argMax, and argMin. A declared hasOne permits all base aggregations, including sum, count, and avg. The declaration is trusted; use checkRelationships to validate target key uniqueness.

Derived, window, and shift measures on the target, hasMany, multi-hop paths, and SQL-backed target measures or inputs are rejected. Local derived and time-based measures can be selected alongside related base measures. The advanced backend execution path does not support relationship measures.

Catalog relationship entries expose safe qualified measures in measures, preserving labels and approximate markers. Generated dataset schemas, Serve inputs, and React hooks include the same names.

Join semantics

Relationship traversal executes as a ClickHouse LEFT ANY JOIN (first match), aliased by the relationship name:

• Base rows always survive. An order with no matching customer keeps its measures; its joined columns are NULL.
• At most one target row matches per base row, so duplicate join keys on the target can never fan out and inflate aggregates. NULL keys never match.
• Filtering on a joined column excludes base rows without a match (the standard SQL behavior: NULL fails the comparison).

Queries that reference no relationship fields generate exactly the same SQL as before relationships existed — there is no cost until you traverse.

A custom query builder must implement leftAnyJoin to traverse relationships. Without it, a query that references a relationship field fails with an error naming the relationship, rather than falling back to a plain LEFT JOIN that would fan out duplicate keys. Queries without relationship fields are unaffected.

Checking that to-one keys are unique

belongsTo and hasOne are declarations. A single-match join keeps a duplicate target key from inflating totals, but it silently picks one of the matching rows, so grouped values can still be wrong. checkRelationships counts rows and distinct keys on each to-one target so you can catch a mis-declared relationship in CI or before deploying:

import { checkRelationships } from '@hypequery/datasets';

const result = await checkRelationships(Orders, { queryBuilder: db });
if (!result.ok) {
  throw new Error(result.issues.map((issue) => issue.message).join('\n'));
}

hasMany relationships are skipped. A tenant-scoped target is checked within the runtime tenant (pass context: { runtime: { tenant } }), the same scope its join uses. Pass relationships: ['customer'] to check a subset. The in-memory backend enforces the rule directly: a duplicate to-one key makes the query fail.

Issue counts are numbers when safely representable in JavaScript. Larger counts are decimal strings so the reported values remain exact.

Rules and limits

Validation rejects, with a specific error message:

• More than one hop. customer.region.name is not supported; only <relationship>.<dimension> or <relationship>.<measure>.
• hasMany traversal. Joining a to-many relationship would fan out rows and corrupt aggregates, so hasMany stays metadata only.
• SQL-backed target dimensions. Dimensions defined with a raw sql expression on the target are not yet queryable through a relationship.
• Unsafe related aggregations. Duplicate-sensitive aggregates through belongsTo, and target derived/time measures, are rejected.
• Ordering by an unselected joined field. As with local dimensions, a qualified orderBy field must also be selected as a dimension or measure.

At definition time, dataset() rejects relationship names that collide with the dataset's own source table (the join alias would shadow the base table) or contain a dot.

Multi-tenancy

When runtime tenant enforcement is active and the target dataset declares a tenantKey, the tenant predicate is applied to the joined table inside the join condition. Rows from other tenants are never joined — they surface as NULLs rather than leaking values — and base-table scoping continues to apply as usual. Explicitly filtering on the target's tenant column is rejected while enforcement is active, same as on the base dataset.

Metadata

The catalog and the versioned semantic contract expose relationship metadata, so tools and agents can discover what is traversable:

{
  "relationships": {
    "customer": {
      "kind": "belongsTo",
      "target": "customers",
      "from": "customer_id",
      "to": "id",
      "queryable": true,
      "fields": ["customer.id", "customer.country", "customer.tier"]
    },
    "items": {
      "kind": "hasMany",
      "target": "line_items",
      "from": "id",
      "to": "order_id",
      "queryable": false,
      "fields": []
    }
  }
}

fields lists the qualified names a query may reference. The same list flows into generated tools (enum schemas for agents), Serve's OpenAPI input schemas, and MCP's get_dataset_schema, so every surface advertises exactly what the validators accept.