Scoped Many-to-many Relation

[wadetandy]

Problem

Given a many-to-many relationship, I would like to be able to define a relationship that scopes to a subset of records based on a value in the join table.

Say I have models User and Group. I also have a join table GroupMembership:

model GroupMembership {
  userId String
  user    User @relation(fields: [userId], references: [id])
  groupId String
  group   Group @relation(fields: [groupId], references: [id])
  role String
}

The join table also has a role attribute. For our purposes say it can have a value of admin or member. I want to create two different relations on the Group model which take these into account:

model Group {
  # other stuff
  admins User[] # restrict this to group memberships with role == admin
  members User[] # restrict to group memberships with role == member
}

Suggested solution

I would like a way to specify a relation that is limited to a subset of the joined items. For anyone who has used Ruby-on-Rails/ActiveRecord, this is the equivalent of passing "scope" argument to a has_many through: association. Something like this might work for the example above:

model Group {
  admins Users[] @relation("GroupMemberships", references: [id], fields: [userId], where: { role: 'admin' })
}

## Alternatives
Currently I can do this manually in my code by creating a model for the join table and directly filtering on those records based on what I need, then jumping to the relation I actually care about.

## Additional context
n/a

[pantharshit00]

I would consider this to be related to #678 as I would usually use a sql view to achieve this. Embedding a query in the schema is not ideal in my opinion.

[cannikin]

But then you have business logic living in two places: your code and the database. I'd prefer that the database just be a dumb store of data and let all the logic for that defines how my application works to be in the application code itself.

[codeninja]

Active Record provides a solid implementation of this.

has_many :orders, -> { where processed: true }
has_one :document, as: :logo,  -> { where tag: "logo" } 

AR specifies the business logic as a constraint on the association. When the association is queried, the conditional is appended. When the association is saved, the condition can be validated to ensure the Logo has a Tag == 'logo' and would throw an error.

This would be invaluable.

[codeninja]

But then you have business logic living in two places

I view this less as business logic and more as data integrity. Currently, if I define the concept of a logo as a Document having a tag='logo' there's no way to enforce via the schema that the logo must have a tag=logo to be considered a Logo type.

It would be possible to save a text file in place of the logo using Prisma without guarding logic. If this were defined on the schema directly your schema becomes the source of truth for the data integrity.

IMO the domain of concerns dictates that the database governs which data should pass into it's storage. The code should trust that the data being fed to it is structurally sound without needing to have external knowledge of how the data was previously processed.

IE: The database should be tight and restrictive as possible to enforce data integrity. This should happens on the Prisma schema layer rather than the execution layer.

Thus I believe this feature has merrit.

[said-alex]

It's a shame we don't have updates about this feature, it would be extremely useful!