Join collections to limit access to entries

[rspy]

Hi there

First of all thank you for your awesome product, I really enjoyed exploring it so far :-)

In my app, there are users and groups (many to many). I want to limit access to users to those, who you are already in a group together with the logged in user. I would have imagined something like this for a list/search rule:
@collection.groups.memberIds ~ [@request.auth.id, id]

What I'm trying to say is give me all users there the memberIds-Array of a group contains the id of the user and the id of the logged-in user. Sidenote: You can still add a "new" user if you type the exact email adress, which I do using a request param, so there will be a "|| email = @request.query.email" in the filter, but that is working fine.

I could of course add a collection reference to groups on the user (this would be my solution for now), but then I need to keep that in sync. Unless pocketbase provides me with an inverse relation?

Furthermore, I want to limit the response to only users that are currently not in the group yet, so I'm doing
filter="id != [id1] && id != [id2] && ...".

Is there a more elegant way for this, for example:
filter="[id1, id2, id3, ...] !~ id"

[ganigeorgiev]

The example is too abstract and I'm not sure that I understand the described data structure and expected workflow.

There are several ways you can handle relations in PocketBase - direct single or multi-relation field(s), junction collection(s) or combination of both.

Could you provide more information on your collections and relation fields structure (you can export your collections configuration as json from the "Admin UI > Settings > Export collections")?

With v0.11+ there is now support for multi-match/match-all conditions that are the defaults when dealing with multi-value fields. If memberIds in the above is a relation field (it may be better to name it just members), then you can do something like @collection.groups.memberIds.id ?= id && @collection.groups.memberIds.id ?= @request.auth.id (this translates to "at least one of the member ids is equal to the id and at least one of the member ids is equal to the current authenticated user").

Furthermore, I want to limit the response to only users that are currently not in the group yet, so I'm doing filter="id != [id1] && id != [id2] && ...".

No, there is no other way at the moment and if you want to combine multiple conditions you'll have to use || or && operators.

[rspy]

OK, I'll try to give an example.

We have the built-in users collection and a collection groups, which has a to-many-relation to users (field-name members).

users:
{ id: 'user1' },
{ id: 'user2' },
{ id: 'user3' },
{ id: 'secret', email: 'secret@email' }

groups:
{ id: 'group1', members: ['user1', 'user2'] },
{ id: 'group2', members: ['user1', 'user3'] }

If user1 queries users, it should return user2 and user3 since they are in a group together, but not user secret since they do not share a group.
If user2 queries users, it should return only user1
If user3 queries users, it should return only user1

If user1 queries users and provides the email address 'secret@email', it should also return user secret. We did this using a request parameter (filter: "|| email = @request.query.email = email"), that is working fine.

My naive way of how I would expect it to work is to add a custom rule on the users collection (list/search action) which looks like "@collection.groups.members ~ [id, @request.auth.id]" (or members.id, not sure when I need the .id). I can't do "@collection.groups.members ~ id && @collection.groups.members ~ @request.auth.id", since that removes the constraint that the users should be in one common group.

Hope that clarifies what we are trying to achieve?

[rspy]

[ { "id": "_pb_users_auth_", "name": "users", "type": "auth", "system": false, "schema": [ { "id": "users_name", "name": "name", "type": "text", "system": false, "required": false, "unique": false, "options": { "min": null, "max": null, "pattern": "" } }, { "id": "users_avatar", "name": "avatar", "type": "file", "system": false, "required": false, "unique": false, "options": { "maxSelect": 1, "maxSize": 5242880, "mimeTypes": [ "image/jpg", "image/jpeg", "image/png", "image/svg+xml", "image/gif", "image/webp" ], "thumbs": null } }, { "id": "ze3uxeji", "name": "groupIds", "type": "relation", "system": false, "required": false, "unique": false, "options": { "maxSelect": null, "collectionId": "jqkikr68rvbjyrb", "cascadeDelete": false } } ], "listRule": "(@collection.groups.memberIds.id ?= id && @collection.groups.memberIds.id ?= @request.auth.id) || @request.query.email = email", "viewRule": "", "createRule": "", "updateRule": "id = @request.auth.id", "deleteRule": "id = @request.auth.id", "options": { "allowEmailAuth": true, "allowOAuth2Auth": true, "allowUsernameAuth": true, "exceptEmailDomains": null, "manageRule": null, "minPasswordLength": 8, "onlyEmailDomains": null, "requireEmail": false } }, { "id": "jqkikr68rvbjyrb", "name": "groups", "type": "base", "system": false, "schema": [ { "id": "b7u4m5a4", "name": "name", "type": "text", "system": false, "required": true, "unique": false, "options": { "min": null, "max": null, "pattern": "" } }, { "id": "znmeqsjq", "name": "location", "type": "text", "system": false, "required": false, "unique": false, "options": { "min": null, "max": null, "pattern": "" } }, { "id": "1xkysiao", "name": "memberIds", "type": "relation", "system": false, "required": false, "unique": false, "options": { "maxSelect": null, "collectionId": "_pb_users_auth_", "cascadeDelete": false } }, { "id": "9qztmq3j", "name": "description", "type": "text", "system": false, "required": false, "unique": false, "options": { "min": null, "max": null, "pattern": "" } }, { "id": "ya0riqrj", "name": "weekdays", "type": "json", "system": false, "required": false, "unique": false, "options": {} }, { "id": "9ztfv7kt", "name": "eventIds", "type": "relation", "system": false, "required": false, "unique": false, "options": { "maxSelect": null, "collectionId": "sj146y9ah72e3tx", "cascadeDelete": false } } ], "listRule": "@request.auth.id != \"\" && memberIds ~ @request.auth.id", "viewRule": "@request.auth.id != \"\" && memberIds ~ @request.auth.id", "createRule": "@request.auth.id != \"\" && memberIds ~ @request.auth.id", "updateRule": "@request.auth.id != \"\" && memberIds ~ @request.auth.id", "deleteRule": "@request.auth.id != \"\" && memberIds ~ @request.auth.id", "options": {} }, { "id": "sj146y9ah72e3tx", "name": "events", "type": "base", "system": false, "schema": [ { "id": "mprnjejh", "name": "startDate", "type": "date", "system": false, "required": true, "unique": false, "options": { "min": "", "max": "" } }, { "id": "txpa4unf", "name": "endDate", "type": "date", "system": false, "required": false, "unique": false, "options": { "min": "", "max": "" } }, { "id": "fdzied6o", "name": "location", "type": "text", "system": false, "required": false, "unique": false, "options": { "min": null, "max": null, "pattern": "" } }, { "id": "jyqgoipb", "name": "participantIds", "type": "relation", "system": false, "required": false, "unique": false, "options": { "maxSelect": null, "collectionId": "_pb_users_auth_", "cascadeDelete": false } }, { "id": "odtkinx3", "name": "groupId", "type": "relation", "system": false, "required": true, "unique": false, "options": { "maxSelect": 1, "collectionId": "jqkikr68rvbjyrb", "cascadeDelete": false } } ], "listRule": "@request.auth.id != \"\" && groupId.memberIds ~ @request.auth.id", "viewRule": "@request.auth.id != \"\" && groupId.memberIds ~ @request.auth.id", "createRule": "@request.auth.id != \"\" && groupId.memberIds ~ @request.auth.id", "updateRule": "@request.auth.id != \"\" && groupId.memberIds ~ @request.auth.id", "deleteRule": "@request.auth.id != \"\" && groupId.memberIds ~ @request.auth.id", "options": {} } ]

[rspy]

BTW: the "groupIds"-relation on users was just for testing purposes. If you add the group in this list, I can do "groupIds.memberIds ~ @request.auth.id" as a rule and it works as expected, but I need to keep group.memberId and user.groupIds in sync.

[ganigeorgiev]

@rspy Thank you for the example. I think I understand what do you want but unfortunately this at the moment is not possible with the milti-relation field and the @collection filter.
You can:

• inverse the relation, eg. define groupIds/groups in the users collection (depending on your use case you may not need the "duplicated" members relation groups field)
• OR as a more traditional SQL approach, use a junction collection userGroup that could have 2 relation fields pointing to the users and groups collection (this will require when creating the user to send a separate request for creating the group relation). There is one small caveat with this and if you want to apply a unique constraint across multiple fields you'll have to use a migration or a hook as shown in How to Unique combined two fields #1693 (comment).

Support for filtering indirect relations is in the roadmap and I'll consider updating its priority since this was requested several times already and eventually could simplify scenarios like the above.

[rspy]

@ganigeorgiev Thank you very much for your fast answer! I'll go with the first suggestion and look forward when the filtering of indirect relations will be implemented. :-)