Join on single key with RELATED()

In SQL, you write JOIN and GROUP BY to combine data across tables. In FeatureQL, features are organized around entities, so the equivalent operation is RELATED(): it lets you reach across entity boundaries using key relationships.

RELATED() handles three patterns:

• Lookup (foreign key → primary key): get a value from another entity, like looking up a store's city from an order's store ID.
• Aggregation (primary key → foreign keys): aggregate values from related entities, like counting how many orders belong to a customer.
• Lookup + aggregation (foreign key → foreign keys): combine both, like computing the total price of items for a customer's last order.

Syntax

RELATED(<expression> VIA <base_expression>)

-- Chained syntax
<primary_key>.RELATED(<expression> GROUP BY <foreign_key>)

RELATED(<expression> VIA <base_expression>)

-- Chained syntax
<primary_key>.RELATED(<expression> GROUP BY <foreign_key>)

The VIA clause tells FeatureQL which key to follow. For lookups, it points to the foreign key field. For aggregations, it specifies the grouping key and the target entity's primary key.

CUSTOMERS ──< ORDERS ──< ITEMS
  (100s)      (200s)     (300s)

Primary key ↔ primary key

When features share the same input (e.g., both depend on CUSTOMER_ID), they align automatically. No RELATED() needed — just reference them together in the same query. Here, CUSTOMER_NAME and CUSTOMER_ORDERS both come from tables keyed by CUSTOMER_ID, so they line up row-by-row.

SELECT
    CUSTOMER_ID,
    CUSTOMER_NAME := TABLES.DIM_CUSTOMERS[name],
    CUSTOMER_ORDERS := TABLES.AGG_CUSTOMERS_OBT[orders],
FROM FM.ECOMM
FOR
    CUSTOMER_ID := BIND_VALUES(SEQUENCE(100,102))
;

Foreign key → primary key (lookup)

To get a value from another entity, use RELATED(expression VIA foreign_key_field). The foreign key field tells FeatureQL which entity to look up. Here we look up the city of a customer's last order by following the last_order_id foreign key from the customer's aggregated table to the orders table.

WITH
    CUSTOMER_CITY_NAME := RELATED(TABLES.FCT_ORDERS[order_city_name] VIA TABLES.AGG_CUSTOMERS_OBT[last_order_id])
SELECT
    CUSTOMER_ID,
    CUSTOMER_CITY_NAME
FROM FM.ECOMM
FOR
    CUSTOMER_ID := BIND_VALUES(SEQUENCE(100,102))
;

Primary key → foreign keys (aggregation)

To aggregate across related entities, use RELATED(AGG(...) GROUP BY foreign_key_field VIA primary_key). This groups the foreign entity's rows by the key that points back to the current entity, then applies the aggregation. Here we count how many orders each customer has.

SELECT
    CUSTOMER_ID,
    NUM_ORDERS := RELATED(SUM(1) GROUP BY TABLES.FCT_ORDERS[order_customer_id] VIA CUSTOMER_ID),
FROM FM.ECOMM
FOR CROSS
    CUSTOMER_ID := BIND_VALUES(SEQUENCE(100,102)),
    ORDER_ID := BIND_VALUES(SEQUENCE(200,203)),
;

Notice that the FOR clause binds both CUSTOMER_ID and ORDER_ID — FeatureQL needs the order data in scope to perform the aggregation.

Foreign key → foreign keys (lookup + aggregation)

You can combine both patterns: first aggregate at one level, then look up the result via a foreign key. Here we compute the total item price per order (aggregation on item_order_id), then look it up for each customer's last_order_id (lookup).

WITH
    LAST_ORDER_ID := TABLES.AGG_CUSTOMERS_OBT[last_order_id],
    LAST_ORDER_PRICE := RELATED(
        SUM(TABLES.FCT_ITEMS[price] * TABLES.FCT_ITEMS[quantity]::DECIMAL) GROUP BY TABLES.FCT_ITEMS[item_order_id]
        VIA LAST_ORDER_ID
    )
SELECT
    CUSTOMER_ID,
    LAST_ORDER_ID,
    LAST_ORDER_PRICE
FROM FM.ECOMM
FOR CROSS
    ITEM_ID := BIND_VALUES(SEQUENCE(300,306)),
    CUSTOMER_ID := BIND_VALUES(SEQUENCE(100,102)),
;

When to use EXTEND() instead

RELATED() works with a single key relationship and returns a scalar value. When you need to join on multiple keys, add several fields at once, or work with arrays of rows, use the more general EXTEND() operation. See RELATED() vs EXTEND() for a side-by-side comparison.