Indirect expand

[ganigeorgiev]

This was discussed in #289 (comment).

We could add support for a new query parameter ?indirectExpand that will allow eager loading non-directly related collection records within a single client request.

It should work similarly to the existing ?expand parameter but with additional 2 extra prefixed keys that points to the related collection and field (an additional finalizer prop key may also be needed in case of duplicated indirect expand fields from different collections).

The format in the related discussion is not final and may change after we start working on the issue.

[ganigeorgiev]

A POC was already implemented while working on #376 so here are some details of the current implementation:

• Make use of the existing ?expand query parameter instead of introducing a new indirectExpand

• indirect relations will be resolved using the format reference_collection(rel_field)[.*].
  For example, if we have the following collection schemas:

  
  (the tasks collection has a single relation type field to the projects collection)

  We want to expand all tasks related to a project while querying the projects collection:

  GET /api/collections/projects/records?expand=tasks(project)
  
  Output:
  [
    {
      "id": "project1",
      "created": "2022-09-11 00:00:00.123",
      "updated": "2022-09-11 00:00:00.123",
      "title": "Example project"
      "@expand": {
        "tasks(project)": [
          {
            "id": "task1",
            "created": "2022-09-11 00:00:00.123",
            "updated": "2022-09-11 00:00:00.123",
            "project": "project1",
            "status": "pending"
          },
          {
            "id": "task2",
            "created": "2022-09-11 00:00:00.123",
            "updated": "2022-09-11 00:00:00.123",
            "project": "project1",
            "status": "completed"
          }
        ]
      }
    }
    ...
  ]
  

Nested indirect expansions will be supported - both for regular relations and sub indirect relations. In the above example, if tasks had another relation field, we will be able to do ?expand=tasks(project).anotherRel or even sub indirect expansion like ?expand=tasks(project).anotherRel.otherIndirectRel(field).

There are some caveats:

• As a side effect of the nested indirect expansion support, referencing the root relation will be also allowed, eg. ?expand=tasks(project).project (nested relations are supported up to 5 levels deep).

• Since there could be some performance issues when expanding multiple nested indirect expands, initially we will support only indirect expansion on a single relation field, aka. in the above example, the indirect expand would not work if the project field was a multi-relation field.

• The "Unique" indirect relation field option is used to determine whether an array or a single object should be expanded.

Nothing is set in stone yet, so suggestions and feedback on the format are welcomed.

[ollema]

This looks great, really like the syntax.

---

I don't have a lot of feedback or concerns, I have one questions though.

In one of my pocketbase apps, as I have already shared in #289, I want to query something along the lines of:

select the latest events and all responses (along with the user who responded) for each selected event

My collections are setup like this:

+----------------------+
| Events               |
+-------------+--------+
| id          | string |
| name        | string |
+-------------+--------+

+-----------------------------+
| Responses                   |
+----------+------------------+
| id       | string           |
| response | multiple choice  |
| event    | relation(Events) |
| user     | relation(Users)  |
+----------+------------------+

+-----------------+
| Users           |
+--------+--------+
| id     | string |
| name   | string |
+--------+--------+

To

select the latest events and all responses (along with the user who responded) for each selected event

I think I would do something like this:

GET /api/collections/events/records?expand=responses(event)

And then I (think) I would get something like this:

Output:
[
  {
    "id": "event1",
    "created": "2022-09-11 00:00:00.123",
    "updated": "2022-09-11 00:00:00.123",
    "title": "Example event"
    "@expand": {
      "responses(event)": [
        {
          "id": "response1",
          "created": "2022-09-11 00:00:00.123",
          "updated": "2022-09-11 00:00:00.123",
          "event": "event1",
          "user": "user123"
        },
        {
          "id": "response2",
          "created": "2022-09-11 00:00:00.123",
          "updated": "2022-09-11 00:00:00.123",
          "event": "event1",
          "user": "user456"
        }
      ]
    }
  }
  ...
]

Nearly there, but I am still missing the expanded user relation.

Am I interpreting the syntax correctly if that would be resolved with:

GET /api/collections/events/records?expand=responses(event).user

Output:
[
  {
    "id": "event1",
    "created": "2022-09-11 00:00:00.123",
    "updated": "2022-09-11 00:00:00.123",
    "title": "Example event"
    "@expand": {
      "responses(event)": [
        {
          "id": "response1",
          "created": "2022-09-11 00:00:00.123",
          "updated": "2022-09-11 00:00:00.123",
          "event": "event1",
          "user": "user123",
          "@expand": {
            "id": "user123",
            "username": "User 123"
            ...
          } 
        },
        {
          "id": "response2",
          "created": "2022-09-11 00:00:00.123",
          "updated": "2022-09-11 00:00:00.123",
          "event": "event1",
          "user": "user456",
          "@expand": {
            "id": "user456",
            "username": "User 456"
            ...
          } 
        }
      ]
    }
  }
  ...
]

If so, that would be really cool 😎

[ganigeorgiev]

@ollema: Am I interpreting the syntax correctly if that would be resolved with:
GET /api/collections/events/records?expand=responses(event).user

Yes, that's correct. The only difference from your sample output is that you'll have an additional user key in the nested @expand, eg.:

[
  {
    "id": "event1",
    "created": "2022-09-11 00:00:00.123",
    "updated": "2022-09-11 00:00:00.123",
    "title": "Example event"
    "@expand": {
      "responses(event)": [
        {
          "id": "response1",
          "created": "2022-09-11 00:00:00.123",
          "updated": "2022-09-11 00:00:00.123",
          "event": "event1",
          "user": "user123"
          "@expand": {
            "user": {
              "id":      "user123",
              "created": "2022-09-11 00:00:00.123",
              "updated": "2022-09-11 00:00:00.123",
              "name":    "...",
            }
          }
        },
        {
          "id": "response2",
          "created": "2022-09-11 00:00:00.123",
          "updated": "2022-09-11 00:00:00.123",
          "event": "event1",
          "user": "user456"
          "@expand": {
            "user": {
              "id":      "user456",
              "created": "2022-09-11 00:00:00.123",
              "updated": "2022-09-11 00:00:00.123",
              "name":    "...",
            }
          }
        }
      ]
    }
  }
  ...
]

[ganigeorgiev]

This is now part of the v0.8.0-rc1 pre-release.

Please note that the documentation is currently missing for this feature since I'm still not sure how to document it clearly, but it will it be added before the final v0.8.0 release.

[ollema]

I have to say that indirect expand together with the generic methods make the code very clean.

I now do something along the lines of:

const queryParams: Record<string, string | null> = {
  sort: sort,
  filter: filters.join(' && '),
  expand: 'venue.city, artists, responses(event).user'
};

const events = await locals.pb.collection('events').getList<Event>(page, perPage, queryParams);

return {
  events: structuredClone(events),
  sort: sort
};

where I have Event as:

export interface Event extends BaseRecord {
  name: string;
  venue: RecordIdString;
  artists: RecordIdString[];
  type: 'concert' | 'music-festival' | 'movie';
  cancelled: boolean;
  starts: string;
  ends: string;
  url?: string;

  expand: {
    venue: Venue;
    artists: Artist[];
    'responses(event)': Response[];
  };
}

which allows me to do this in my Svelte component:

...
<tbody>
  {#each events.items as event}
    <tr>
      <td><a href="/events/{event.id}">{event.name}</a></td>
      <td>{formatDate(event.starts)}</td>
      <td>{event.expand.venue.name}</td>
      <td>
        {#if event.expand['responses(event)'] !== undefined}
          {#each event.expand['responses(event)'] as response}
            <img
              use:tooltip={{ content: response.expand.user.name }}
              src={response.expand.user.avatar}
              alt="avatar for {response.expand.user.name}"
            />
          {/each}
        {/if}
      </td>
      <td />
    </tr>
  {/each}
</tbody>
...

which I think looks very clean!

thanks a lot for implementing this!

[ganigeorgiev]

Just for info if someone stumble on this issue - this is documented in the new docs with v0.8.0 in https://pocketbase.io/docs/expanding-relations/

[IzMichael]

Reply: #312 (comment)

initially we will support only indirect expansion on a single relation field

Are there plans to change this in the future, as the "initially" implied?