CODEWITHSUNDEEP
prisma
Philippe Sabourin
Philippe Sabourin

Reputation: 8075

What is the best way to model a one-to-many relationship that also has a "selected" object in Prisma?

In our app, a user can be a member of many organizations. While viewing the site, they are generally viewing it through one of those organizations. This is the selected organization.

Is the following model the best way to model that? Is there a way to model it so you don't have to have the selectedBy and selectedById on the Membership and just have the selectedMembershipId on the User with a foreign key to the Membership?

model User {
  id                 Int          @id @default(autoincrement())
  memberships        Membership[] @relation("Memberships")
  selectedMembership Membership?  @relation("Selection")
}
model Membership {
  id           Int          @id @default(autoincrement())
  user         User         @relation("Memberships", fields: [userId], references: [id])
  userId       Int
  selectedBy   User?        @relation("Selection", fields: [selectedById], references: [id])
  selectedById Int?          @unique
  organization Organization @relation(fields: [orgId], references: [id])
  orgId        Int
  role         MemberRole

  @@unique([userId, orgId])
}

Upvotes: 0

Views: 120

Answers (1)

Tasin Ishmam
Tasin Ishmam

Reputation: 7248

It's not possible to do precisely what you're hoping to. The syntax for relations in prisma calls for having a relation field (To the best of my understanding, that's what you would prefer not to have in your schema).

Since it's a one-to-one relation, if you prefer, you could do it the other way around, and have a selectedMembershipId foreign key on the User side and only have the selectedBy relation field on the Membership side like this:

model User {
  id                 Int          @id @default(autoincrement())
  memberships        Membership[] @relation("Memberships")
  selectedMembership Membership?  @relation("Selection", fields: [selectedMembershipId], references: [id])
  selectedMembershipId       Int?         @unique

}

model Membership {
  id         Int   @id @default(autoincrement())
  user       User  @relation("Memberships", fields: [userId], references: [id])
  userId     Int
  selectedBy User? @relation("Selection")
  ...
}

However, this is really a matter of preference regarding which side to keep the foreign key. In my opinion, the way you're handling your schema is the most reasonable way to model the relationship in Prisma.

Upvotes: 1

Related Questions