https://www.prisma.io/ logo
Join Slack
Powered by
I'm trying to model a referral table for my Postgr...
# orm-help
s
I'm trying to model a referral table for my Postgres database. Not sure if the db schema is correct, but I have a table with a 
referId
, `userId`: 1 to 1, and `referredUserId`: 1 to many. model Referral { id Int @id @default(autoincrement()) createdAt DateTime @default(now()) updatedAt DateTime @default(now()) referId String // Auto Generate Random String userId Int @unique user User @relation(fields: [userId], references: [id]) referredUsersId Int[] referredUsers User[] @relation(fields: [referredUsersId], references: [id]) } I'm not sure exactly how to reference these in the User model. I tried Referral Referral? UsersReferred Referral[] But I get an error
Error validating model "User": Ambiguous relation detected
What's the correct way to model a referral table and how can I do it in prisma?
s
This looks like a many to many, as 
UsersReferred
is an array and 
referredUsers
is an array. Is that intentional?
Also, because the same model is relating to another model twice, you have to specifically name the relations
I would think the model would look something like this:
Copy code
model User {
  id            String     @id
  Referral      Referral?  @relation("UserReferral")
  UsersReferred Referral[] @relation("UsersReferred")
}

model Referral {
  id              Int      @id @default(autoincrement())
  createdAt       DateTime @default(now())
  updatedAt       DateTime @default(now())
  referId         String
  userId          String   @unique
  user            User     @relation(fields: [userId], references: [id], name: "UserReferral")
  referredUsersId String
  referredUsers   User     @relation(fields: [referredUsersId], references: [id], name: "UsersReferred")
}
s
@Sabin Adams Thanks! I'm now getting this error: Error parsing attribute "@relation": The type of the field 
userId
in the model 
Referral
is not matching the type of the referenced field 
id
in model 
User
.
so userId to User should be a one to one, while referredUsersId to User should be many to one... I think Meaning in Referral, there will only be one 
userId
, but many 
referredUsersId
. Makes sense?
Or another way to do it, is to create a new 
Referral
table for every time a user makes a referral, making both 
userId
and 
referredUsersId
a one to one
What do u suggest the best way to do that is?
s
I think that error is because userId on the referral model should be a String because that's what you defined in the User model
s
@Sabin Adams How would I go about it using the method of creating a new Referral table every time someone makes a referral?
Does this make sense: 
model User {
Referral        Referral?         @relation("UserReferral")
UsersReferred   Referral?         @relation("UsersReferred")
}
model Referral {
id        Int      @id @default(autoincrement())
createdAt DateTime @default(now())
updatedAt DateTime @default(now())
userId Int  @unique
user   User @relation(fields: [userId], references: [id], name: "UserReferral")
referredUserId Int  @unique
referredUser   User @relation(fields: [referredUserId], references: [id], name: "UsersReferred")
}
s
That kinda makes sense to me. In that case there's two one to one relations. One user can have one referral and be referred by one other user. Would you not want UsersReferred to be a many?
s
Which way do you think is better? @Sabin Adams
s
Personally I think in that case: 
Referral
should be a one-to-one because one user can have one referral from another user 
UsersReferred
should be a one-to-many because one user can have sent many referrals to other users. So this same model would still apply:
Copy code
model User {
  id            String     @id
  Referral      Referral?  @relation("UserReferral")
  UsersReferred Referral[] @relation("UsersReferred")
}

model Referral {
  id              Int      @id @default(autoincrement())
  createdAt       DateTime @default(now())
  updatedAt       DateTime @default(now())
  referId         String
  userId          String   @unique
  user            User     @relation(fields: [userId], references: [id], name: "UserReferral")
  referredUsersId String
  referredUsers   User     @relation(fields: [referredUsersId], references: [id], name: "UsersReferred")
}
It does depend on what your application needs to do though, so make sure you pick what works best for your needs.
s
Got it. Thanks So muchn @Sabin Adams
s
No problem!
Open in Slack
PreviousNext
Powered by