# Help: `related_on` (concept)

`RELATED(<aggregate> ON <predicate>)` produces one value per outer entity when a key-only `VIA` relationship cannot express the match. The predicate must connect the outer query grain to one nested inner grain, and the expression before `ON` must aggregate possible matches.

## Classify a value with a range join

```featureql
-- ON is the complete match; MAX_BY collapses possible inner matches to one label
PRICE_BAND := RELATED(
    MAX_BY(BAND_DATA[name], BAND_DATA[price_max])
    ON BAND_DATA[price_min] <= PRODUCT_DATA[price]
       AND PRODUCT_DATA[price] < BAND_DATA[price_max]
),
```

Runnable snippet: [`help_related_on_range`](/llms/help_related_on_range.md)

## Select the latest value as of a date

```featureql
-- Match every earlier event, then choose the event with the greatest timestamp
LAST_STATUS := RELATED(
    MAX_BY(EVENT_DATA[status], EVENT_DATA[event_date])
    ON EVENT_DATA[event_date] <= ACCOUNT_DATA[as_of_date]
),
```

Runnable snippet: [`help_related_on_as_of`](/llms/help_related_on_as_of.md)

## Keep matching and filtering separate

Equality, range, and inequality conditions that relate the two grains belong in `ON`; do not combine this form with `VIA` or `BIND TO`. Use `FILTER (WHERE ...)` for additional inner-row filtering after the match — it is AND-ed with `ON`, not replaced by it.

```featureql
-- ON matches product to order; FILTER keeps open orders only
OPEN_AMOUNT := COALESCE(
    RELATED(
        SUM(ORDER_DATA[amount]) FILTER (WHERE IS_OPEN)
        ON ORDER_DATA[product_id] = PRODUCT_ID
    ),
    0
),
```

Runnable snippet: [`help_related_on_filter`](/llms/help_related_on_filter.md)

## Dedup an identity self-join

`<` stays refused on identity keys. Order two keys of one family with `IS_KEY_BEFORE(left, right)` inside `ON`. Axis keys (`DATE#`, `TIMESTAMP#`, geography) already accept `<`.

## Broadcast a global aggregate

A child-grain `AVG` with no join key still needs `RELATED ON` so it can sit next to an outer grain. `ON` must name both grains; `IS NOT NULL` on each key broadcasts the same scalar to every outer row — see [`global_related`](/llms/global_related.md).

## Daily snapshot over a calendar is not RELATED ON

`ON` is exactly two grains (outer entity + nested events). A `SEQUENCE` of dates is a third grain — do not `AND event_date <= DAY` into `ON`. Bind the dates with `FOR CROSS` against the **fact** `INPUT` and `SUM(...) GROUP BY (keys, date)` — [`date_spine`](/llms/date_spine.md).

## Two facts that share dimensions are not RELATED ON

Spend and orders meet on campaign and month, not a row key. Binding those dimensions and `FILTER`ing each fact is [`dimensional_spine`](/llms/dimensional_spine.md). `RELATED ON` campaign **and** month raises `UE/STATIC-ANALYSIS-RELATED-ON-GRAIN-COUNT`. Two children of one parent: [`independent_facts`](/llms/independent_facts.md).

# Help: `related_on` (snippet)

## `help_related_on_range`

```slt
# tags: related_on, non_equi_join, range_join
# warning: SELECTION-ARBITRARY MAX_BY selects inside a tied ordering class and UNNAMED_FEATURE_P6GP1Q is not determined by UNNAMED_FEATURE_2M46Z5. Add a stable tie-break key.
# schema: BIGINT#PRODUCTS|VARCHAR
query PRODUCT_ID:I,PRICE_BAND:V
DEFINE
    products := ENTITY(),
    product_id := INPUT(BIGINT#products),
    product_data := INLINE_COLUMNS(
        product_id BIGINT#products BIND TO product_id,
        price DECIMAL(10,2)
        FROM CSV(
            product_id,price
            1,52.20
            2,302.00
            3,1200.05
        )
    ),
    bands := ENTITY(),
    band_id := INPUT(BIGINT#bands),
    band_data := INLINE_COLUMNS(
        band_id BIGINT#bands BIND TO band_id,
        name VARCHAR,
        price_min DECIMAL(10,2),
        price_max DECIMAL(10,2)
        FROM CSV(
            band_id,name,price_min,price_max
            100,low,0.,100.
            101,mid,100.,1000.
            102,high,1000.,100000.
        )
    )
RETURN
    product_id,
    price_band := RELATED(
        MAX_BY(band_data[name], band_data[price_max]) ON band_data[price_min]
        <= product_data[price]
        AND product_data[price] < band_data[price_max]
    )
FOR
    product_id := BIND_VALUES(ARRAY(1, 2, 3)),
    NESTED band_id := BIND_VALUES(ARRAY(100, 101, 102))
ORDER BY product_id
;
----
1	low
2	mid
3	high
```

## `help_related_on_as_of`

```slt
# tags: related_on, non_equi_join, as_of_join
# warning: SELECTION-ARBITRARY MAX_BY selects inside a tied ordering class and UNNAMED_FEATURE_23D23W is not determined by UNNAMED_FEATURE_2RRUMD. Add a stable tie-break key.
# schema: BIGINT#ACCOUNTS|VARCHAR
query ACCOUNT_ID:I,LAST_STATUS:V
DEFINE
    accounts := ENTITY(),
    account_id := INPUT(BIGINT#accounts),
    account_data := INLINE_COLUMNS(
        account_id BIGINT#accounts BIND TO account_id,
        as_of_date DATE
        FROM CSV(
            account_id,as_of_date
            1,2024-01-10
            2,2024-01-20
        )
    ),
    events := ENTITY(),
    event_id := INPUT(BIGINT#events),
    event_data := INLINE_COLUMNS(
        event_id BIGINT#events BIND TO event_id,
        event_date DATE,
        status VARCHAR
        FROM CSV(
            event_id,event_date,status
            1,2024-01-01,new
            2,2024-01-05,active
            3,2024-01-15,paused
        )
    )
RETURN
    account_id,
    last_status := RELATED(
        MAX_BY(event_data[status], event_data[event_date]) ON event_data[event_date]
        <= account_data[as_of_date]
    )
FOR
    account_id := BIND_VALUES(ARRAY(1, 2)),
    NESTED event_id := BIND_VALUES(ARRAY(1, 2, 3))
ORDER BY account_id
;
----
1	active
2	paused
```

## `help_related_on_filter`

```slt
# tags: related_on, related_filter
# schema: BIGINT#PRODUCTS|BIGINT
query PRODUCT_ID:I,OPEN_AMOUNT:I
DEFINE
    products := ENTITY(),
    product_id := INPUT(BIGINT#products),
    orders := ENTITY(),
    order_id := INPUT(BIGINT#orders),
    order_data := INLINE_COLUMNS(
        order_id BIGINT#orders BIND TO order_id,
        product_id BIGINT#products,
        amount BIGINT,
        status VARCHAR
        FROM CSV(
            order_id,product_id,amount,status
            10,1,10,open
            11,1,100,closed
            20,2,20,open
            21,2,5,open
        )
    ),
    is_open := order_data[status] = 'open'
RETURN
    product_id,
    open_amount := COALESCE(
        RELATED(
            SUM(order_data[amount]) FILTER (WHERE is_open) ON order_data[product_id]
            = product_id
        ),
        0
    )
FOR
    product_id := BIND_VALUES(ARRAY(1, 2)),
    NESTED order_id := BIND_VALUES(ARRAY(10, 11, 20, 21))
ORDER BY product_id
;
----
1	10
2	25
```

## `related_on_range_default_grain`

```slt
# comment: Range join at the query PK; the ON predicate is the join (no equi key).
# tags: related, related_on, non_equi_join, range_join
# warning: SELECTION-ARBITRARY MAX_BY selects inside a tied ordering class and UNNAMED_FEATURE_ZZ1W77 is not determined by UNNAMED_FEATURE_2U1H5Q. Add a stable tie-break key.
# schema: BIGINT#PRODUCTS|DECIMAL(19,6)|VARCHAR
query PRODUCT_ID:I,PRODUCT_VAT:D,PRODUCT_RANGE:V
DEFINE
    products := ENTITY(),
    product_id := INPUT(BIGINT#products),
    product_data := INLINE_COLUMNS(
        product_id BIGINT#products BIND TO product_id,
        price DECIMAL
        FROM CSV(
            product_id,price
            1,52.20
            2,302.00
            3,1200.05
        )
    ),
    product_vat := product_data[price]
    - (product_data[price] / 1.20)::DECIMAL(10,2),
    ranges := ENTITY(),
    range_id := INPUT(BIGINT#ranges),
    range_data := INLINE_COLUMNS(
        range_id BIGINT#ranges BIND TO range_id,
        name VARCHAR,
        price_min DECIMAL,
        price_max DECIMAL
        FROM CSV(
            range_id,name,price_min,price_max
            100,low,0.,100.
            101,mid,100.,1000.
            102,high,1000.,100000.
        )
    )
RETURN
    product_id,
    product_vat,
    product_range := RELATED(
        MAX_BY(range_data[name], range_data[price_max]) ON range_data[price_min]
        <= product_data[price]
        AND product_data[price] < range_data[price_max]
    )
FOR
    product_id := BIND_VALUES(ARRAY(1, 2, 3)),
    NESTED range_id := BIND_VALUES(ARRAY(100, 101, 102))
ORDER BY product_id
;
----
1	8.70	low
2	50.33	mid
3	200.01	high
```

## `related_on_range_with_equi_component`

```slt
# comment: Equi + non-equi predicates coexist in one ON (the equi key is not a VIA).
# tags: related, related_on, non_equi_join, range_join
# warning: SELECTION-ARBITRARY MAX_BY selects inside a tied ordering class and UNNAMED_FEATURE_ZZ1W77 is not determined by UNNAMED_FEATURE_2U1H5Q. Add a stable tie-break key.
# schema: BIGINT#PRODUCTS|VARCHAR
query PRODUCT_ID:I,PRODUCT_RANGE:V
DEFINE
    products := ENTITY(),
    product_id := INPUT(BIGINT#products),
    product_data := INLINE_COLUMNS(
        product_id BIGINT#products BIND TO product_id,
        category VARCHAR,
        price DECIMAL
        FROM CSV(
            product_id,category,price
            1,food,52.20
            2,electronics,302.00
            3,electronics,1200.05
        )
    ),
    ranges := ENTITY(),
    range_id := INPUT(BIGINT#ranges),
    range_data := INLINE_COLUMNS(
        range_id BIGINT#ranges BIND TO range_id,
        name VARCHAR,
        category VARCHAR,
        price_min DECIMAL,
        price_max DECIMAL
        FROM CSV(
            range_id,name,category,price_min,price_max
            100,food_low,food,0.,100.
            101,food_mid,food,100.,1000.
            102,food_high,food,1000.,100000.
            110,elec_low,electronics,0.,100.
            111,elec_mid,electronics,100.,1000.
            112,elec_high,electronics,1000.,100000.
        )
    )
RETURN
    product_id,
    product_range := RELATED(
        MAX_BY(range_data[name], range_data[price_max]) ON range_data[category]
        = product_data[category]
        AND range_data[price_min] <= product_data[price]
        AND product_data[price] < range_data[price_max]
    )
FOR
    product_id := BIND_VALUES(ARRAY(1, 2, 3)),
    NESTED range_id := BIND_VALUES(ARRAY(100, 101, 102, 110, 111, 112))
ORDER BY product_id
;
----
1	food_low
2	elec_mid
3	elec_high
```

**Related tags**: [`related_patterns`](/llms/related_aggregation.md) [`extend_on`](/llms/extend_on.md) [`array_merge_on`](/llms/array_merge.md) [`related_vs_extend`](/llms/related_vs_extend.md) [`where_vs_filter`](/llms/where_vs_filter.md) [`global_related`](/llms/global_related.md) [`date_spine`](/llms/date_spine.md) [`dimensional_spine`](/llms/dimensional_spine.md)