# Help: `many_to_many` (concept)

A many-to-many link is its own entity (a bridge / junction). Roll a metric across the bridge with `RELATED(... GROUP BY ...)`, or enrich the per-parent edge array with `EXTEND`.

## Roll a metric across the bridge

```featureql
-- bring the linked dimension value onto each bridge row (bridge grain)
BRIDGE_ACCT_BALANCE := BRIDGE[owned_account_id].RELATED(DIM_ACCOUNTS[balance]),
BRIDGE_WEIGHTED     := BRIDGE_ACCT_BALANCE * BRIDGE[ownership],

-- aggregate the bridge rows up to the parent entity grain
ALLOCATED_BALANCE := CUSTOMER_ID.RELATED(
    SUM(BRIDGE_WEIGHTED) GROUP BY BRIDGE[owner_customer_id]
),
```

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

## Enrich a per-parent edge array

When the parent already holds an array of link IDs, `EXTEND` joins each edge to its junction row, then `TRANSFORM` aggregates.

```featureql
-- join each enrollment ID to its junction row, then average grades
ENRICHED := EXTEND(
    ZIP(STUDENT_OBT[enrollment_ids] AS enrollment_id)
    WITH ENROLL_DATA[grade] AS grade, ENROLL_DATA[course_id] AS course_id
    VIA enrollment_id BIND TO ENROLLMENT_ID
),
GPA := ENRICHED.TRANSFORM(RETURN AVG(grade)).UNWRAP_ONE(),
```

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

Inside `EXTEND`, declare a FK to a *third* entity as plain `BIGINT` (e.g. `course_id BIGINT`, not `BIGINT#COURSES`); look that dimension up in a later step.

# Help: `many_to_many` (snippet)

## `help_many_to_many_bridge`

```slt
# tags: many_to_many, bridge_table, weighted_allocation, related_aggregation
# schema: BIGINT#CUSTOMERS|DOUBLE
query CUSTOMER_ID:I,ALLOCATED_BALANCE:D
DEFINE
    customers := ENTITY(),
    accounts := ENTITY(),
    ownerships := ENTITY(),
    customer_id := INPUT(BIGINT#customers),
    account_id := INPUT(BIGINT#accounts),
    ownership_id := INPUT(BIGINT#ownerships),
    dim_accounts := INLINE_COLUMNS(
        account_id BIGINT#accounts BIND TO account_id,
        balance DOUBLE
        FROM CSV(
            account_id,balance
            500,1000e0
            501,200e0
            502,3000e0
        )
    ),
    bridge := INLINE_COLUMNS(
        ownership_id BIGINT#ownerships BIND TO ownership_id,
        owner_customer_id BIGINT#customers,
        owned_account_id BIGINT#accounts,
        ownership DOUBLE
        FROM CSV(
            ownership_id,owner_customer_id,owned_account_id,ownership
            1,10,500,0.5
            2,11,500,0.5
            3,10,501,1.0
            4,10,502,0.25
            5,12,502,0.75
        )
    ),
    -- bring the account balance onto each bridge row (OWNERSHIPS grain)
    bridge_acct_balance := bridge[owned_account_id].RELATED(
        dim_accounts[balance]
    ),
    bridge_weighted := bridge_acct_balance * bridge[ownership],
    -- roll the weighted contributions up to the customer grain
    allocated_balance := customer_id.RELATED(
        SUM(bridge_weighted) GROUP BY (bridge[owner_customer_id])
    )
RETURN
    customer_id,
    allocated_balance
FOR
    customer_id := BIND_VALUES(ARRAY(10, 11, 12)),
    NESTED ownership_id := BIND_VALUES(ARRAY(1, 2, 3, 4, 5))
ORDER BY customer_id
;
----
10	1450
11	500
12	2250
```

## `help_many_to_many_gpa`

```slt
# comment: EXTEND ON path hits OuterReferenceColumn; DF 50.3 physical plan rejects it.
# tags: many_to_many, junction_table, enrollment_pattern, extend
# schema: BIGINT#STUDENTS|DOUBLE
query STUDENT_ID:I,GPA:D rowsort
DEFINE
    students := ENTITY(),
    enrollments := ENTITY(),
    student_id := INPUT(BIGINT#students),
    enrollment_id := INPUT(BIGINT#enrollments),
    student_obt := EXTERNAL_COLUMNS(
        student_id BIGINT#students BIND TO student_id,
        enrollment_ids ARRAY(BIGINT#enrollments)
        FROM SQL(
            SELECT student_id, ARRAY_AGG(enrollment_id) AS enrollment_ids
            FROM (VALUES (1, 10), (1, 11), (2, 12)) AS t(student_id, enrollment_id)
            GROUP BY (student_id)
        )
    ),
    enroll_data := INLINE_COLUMNS(
        enrollment_id BIGINT#enrollments BIND TO enrollment_id,
        course_id BIGINT,
        grade DOUBLE
        FROM CSV(
            enrollment_id,course_id,grade
            10,100,90
            11,101,80
            12,100,70
        )
    ),
    enriched := EXTEND(
        ZIP(student_obt[enrollment_ids] AS enrollment_id)
        WITH enroll_data[grade] AS grade,
        enroll_data[course_id] AS course_id
        VIA enrollment_id BIND TO enrollment_id
    ),
    gpa := enriched.TRANSFORM(RETURN AVG(grade)).UNWRAP_ONE()
RETURN
    student_id,
    gpa
FOR
    student_id := BIND_VALUES(ARRAY(1, 2)),
    NESTED enrollment_id := BIND_VALUES(ARRAY(10, 11, 12))
;
----
1	85
2	70
```

**Related tags**: [`related`](/llms/related.md) [`related_aggregation`](/llms/related_aggregation.md) [`dual_fk`](/llms/dual_fk.md) [`extend`](/llms/extend.md) [`array_agg`](/llms/array_merge.md)