Notes about the upcoming back relation support

[ganigeorgiev]

Below are some notes regarding the upcoming back relation filter and sort support (for both single and multiple relation fields).

_via_ syntax

We already have syntax for referencing back relations as part of the expand parameter:

// ex. comments(post)
yourCollection(yourField).*

We could technically continue to use the same syntax for the filter and sort but several users complaint that the parenthesis makes it difficult to serialize the api responses since most of the programming languages doesn't allow ( or ) characters in the variable/property name and it requires manually specifying an alias or brackets access notation (eg. obj["yourCollection(yourField)"].someField.*).

Therefore instead of parenthesis I've decided to change it to use _via_ as a fixed string separator:

// ex. comments_via_post
yourCollection_via_yourRelField.*

(I'm still not 100% sure about _via_ but I decided on it instead of _by_ to avoid the stuttering with common relation fields like "createdBy")

This both satisfy the requirement the key to be valid variable/property name and at least to me reads just as fine as the parenthesis.

The drawback from the fixed string separator is that we'll no longer allow collections and fields with _via_ in their name to avoid ambiguities and collisions (I think this would be rare).

For the expand parameter the old parenthesis syntax will continue to work alongside the new one in order to minimize the breaking changes, but if you use the old syntax there will be a deprecated warning message printed to the stderr.

In general back relations references will be treated like dynamic relation fields:

• as part of the main collection schema (eg. posts_via_createdBy)
• as part of @request.auth.yourCollection_via_yourRelField.* expressions (eg. @request.auth.posts_via_createdBy.*)
• as part of @request.data.yourCollection_via_yourRelField.* expressions (eg. @request.data.posts_via_createdBy.*)
• as part of @collection.baseCollection.yourCollection_via_yourRelField.* expressions (eg. @collection.usersView.users_via_owner.*)

Single vs Multiple relation fields resolution

Similar to how back relation references in expand works, when filtering back relations we can't rely only on the MaxSelect relation field option to determine whether the filter expression will resolve to a single field or multiple because it is possible the main record to have multiple single back relation references.
Therefore a back relation reference is considered "single" only if it has a unique index for the relation field (to avoid ambiguities partial index constraints are ignored).
Or in other words, comments_via_post.* will resolve to a single record constraint if there is a unique index in the comments collection for the post field.

Additionally, as a side effect of the recent changes, the existing limitation for expand not working with multiple back relation fields is lifted and multiple back relation expands will be supported with the next release.

Security and performance

In some rare cases, especially now with the back relations, security researches could argue that there is a risk of information disclosure because someone could try to use the filter to find out indirectly whether a back relation record exists or not.
If we ignore the non-descriptive API rule errors and blank responses, to some extent this is possible, but keep in mind that users still need first to comply with the List/Search API rule of the main collection and even then they will not be able to retrieve the complete relation record if they don't satisfy the specific API rule of the related collection.
If developers strictly don't want to allow filtering relation references they can always enforce manual checks using something like @request.query.filter !~ "someRelField." or via the app hooks (with the refactoring this may be further simplified with individual "Filterable" field option).
Therefore in my opinion such cases are negligible for the sake of better performance and the cleaner developer APIs.

Regarding query performance - I still have to do some manual performance tests on larger datasets before finalizing the implementation, but the expected query performance should be similar to the regular relations.

For the Admin UI, because generating all autocomplete back relation fields and their various nesting options could block the interface, the autocomplete generator was migrated to execute separately in a worker. I've tested the UI with ~30k autocomplete fields (combined with @request.* and @collection.* completions) and locally seems OK (it can be further optimized but the Admin UI is not that critical and it will be refactored anyway).

---

That's it. I'm planning to release PocketBase v0.22.0 sometime later next week or the week following it.
In the meantime feel free to comment if you have something else in mind in regards to the back relations.
If you want to test it, I've pushed the latest changes in the develop branch.

[sergio9929]

Regarding the back relation syntax... what about _from_?

// ex. comments_from_post
yourCollection_from_yourRelField.*

Although I don't know how common it is to use _from_ in the collection name.

[ganigeorgiev]

It doesn't read OK to me and it is longer to type.

Note that the idea of the back-relation is to join the "comments" collection through/by/via the "post" relation field.

[sergio9929]

I know, I have used the old indirect relations. I was reading it as "expand a collection from this field" and it made sense in my head. Maybe I'm translating it wrong and it doesn't make sense in English, nevermind.

[jonshipman]

I think "via" is also lesser used vernacular than "from." Hell, I'd be surprised if there are any Pocketbase databases in the world that have "via" in the collection name.

[octavonu]

Hi @ganigeorgiev ,

what about OData V4 $filter and $expand query parameters? Can you implement something similar for PB?

Thank you

[ganigeorgiev]

@octavonu I'm not familiar with OData but we already have filter and expand query parameters support.

You can find the supported query parameters in the API preview section for the specific action or in the general Web API docs.

[calumk]

What if you named a collection

thing_via_that

and another

thing_via_that2

you would end up with a query

thing_via_that_via_thing_via_that2

I think i would much prefer .via.

[ganigeorgiev]

The drawback from the fixed string separator is that we'll no longer allow collections and fields with _via_ in their name to avoid ambiguities and collisions (I think this would be rare).

[calumk]

The drawback is that the underscore makes this quite unreadable. - consider something like the following :

producs_and_services_via_customers_from_old_database

why is _via_ a key word but _and_ is not

What happens if you add another _word_ in the future?

[calumk]

yourCollection_via_yourRelField.*

what about ~via~

yourCollection~via~yourRelField.*

or ^

yourCollection^yourRelField.*

or would these suffer similar serialize issues?

[ganigeorgiev]

None of ^, ., ~, (, ) are valid variable characters in a lot of programming languages.
The point of using a fixed string separator and not use the old parenthesis syntax was to make it a valid property/variable name so that when used with expand you can type record.expand.yourCollection_via_yourRelField.id instead of record.expand["yourCollection(yourRelField)"].id (the json decoding in various typed languages is also another issue and usually require specifying a special alias for the field).

In any case note that this is already released as part of PocketBase v0.22.0.

[calumk]

Hey @ganigeorgiev - Do you have an expected release number when the old format of this will be dropped?

Will it be V0.23?