# Bug 3454: Support more than two levels of chaining in related

- Status: open
- Project: Zero
- Creator: @aboodman
- Labels: zero
- Created: 2025-01-21T01:27:08Z
- Modified: 2026-04-05T06:52:28Z
- Reactions: 👍️ ×12 (Aexylus, Exerosis, adamsvystun, bent101, danielcompton, djm, jakobdybdahl, joodaloop, jorroll, mtorcutt, tomyfalgui, typedrat)
- URL: https://bugs.rocicorp.dev/p/zero/issue/3454

## Description

Right now related() is limited to two levels of chaining. We found a place where more than two would be useful.

It could be used as a workaround for not having recursive queries, e.g., in tree-like UIs or permission schemes that use nested teams.

## Comments (4)

### @aboodman — 2025-01-21T01:28:10Z

This seems like it is likely straightforward:

https://github.com/rocicorp/mono/blob/bd05f896f8a03c4a66c6b0d65d6395c60ea0103e/packages/zero-schema/src/table-schema.ts#L89-L92

More than two levels could result in dupes, and I guess `ArrayView` would have to be taught that dupes are OK (and I guess to count them so it knows when to remove?). But otherwise seems fine.

### @arv — 2025-01-21T18:22:05Z

When I implemented this the main problem that I foresaw was getting the types working with more than two levels.

I'm not sure I fully understand why there might be duplicates?

### @aboodman — 2025-01-22T01:58:14Z

Consider the schema that models teams and subteams:

```
user
+id text

# Only one of userID and teamID can be set
team_entry
+parentTeamID text
+userID text null
+teamID text null
```

And the relationships:

```
const userTable = createTableSchema({
  columns: {
    id: 'string',
  },
  primaryKey: ['id'],
}

const teamEntryTable = createTableSchema({
  columns: {
    parentTeamID: 'string',
    // Teams can contain subteams or users
    // Only one of these may be set
    childTeamID: {type: 'string', optional: true},
    userID: {type: 'string', optional: true},
  },
  primaryKey: ['parentTeamID', 'childTeamID', 'userID'],
});

const teamTable = createTableSChema({
  columns: {
    id: 'string',
  },
  relationships: {
    users1: [{
      sourceField: 'id',
      destSchema: teamEntryTable,
      destField: 'parentTeamID',
    }, {
      sourceField: 'userID',
      destSchema: userTable,
      destField: 'id',
    }],
    users2: [{
      sourceField: 'id',
      destSchema: teamEntryTable,
      destField: 'parentTeamID',
    }, {
      sourceField: 'childTeamID',
      destSchema: teamEntryTable,
      destField: 'parentTeamID',
    }, {
      sourceField: 'userID',
      destSchema: userTable,
      destField: 'id',
    }],
  }
```

In the `users1` relationship there cannot be any duplicate users assuming that teamEntry schema is unique on (parentTeamID, userID). but in `users2` relationship we can have duplicates because the same user can show up in multiple subteams.

### @typedrat — 2025-06-12T20:58:36Z

Forgive me if I'm missing something, but why can't this just turn into a `SELECT DISTINCT` when talking to the actual database?