One to many relationships with Self on Prisma

Question

I'm trying to implement a tinder-like simple app with Prisma where a user can swipe left and right to like or dislike another user, and after that, I was to be able to fetch the matches and the candidates. The matches are all users that also like me, while the candidates are all users aside from myself, my matches, the users I dislike and the uses that dislike me.

I had it working creating a User model, a Likes model and a Dislikes model

model User {
  id            String     @id
  username      String     @default(value: "")
  age           Int        @default(value: 0)
  bio           String     @default(value: "") @db.VarChar(1000)
}

model Likes {
  id          Int    @id @default(autoincrement())
  userId      String
  likedUserId String
}

model Dislikes {
  id             Int    @id @default(autoincrement())
  userId         String
  dislikedUserId String
}

and creating a couple of fairly complex SQL statements running them with queryRaw

It does work but I feel I'm not using Prisma right, I'm just translating SQL to Prisma. So I decided to change my model as the following

model User {
  id            String     @id
  username      String     @default(value: "")
  age           Int        @default(value: 0)
  bio           String     @default(value: "") @db.VarChar(1000)
  liked         User[]     @relation("LikedUserToUser")
  disliked      User[]     @relation("DislikedUserToUser")
}

to which my IDE prompted me to add the opposite relation or running prisma format After doing that I ended up with something like this

model User {
  id           String     @id
  username     String     @default(value: "")
  age          Int        @default(value: 0)
  bio          String     @default(value: "") @db.VarChar(1000)
  liked        User[]     @relation("LikedUserToUser")
  disliked     User[]     @relation("DislikedUserToUser")
  userToLike    User?      @relation("LikedUserToUser", fields: [userId], references: [id])
  userToDislike User?      @relation("DislikedUserToUser", fields: [userId], references: [id])
  userId       String?
}

Now, it seems to be working decently, I can 'like' a user with

const user = await prisma.user.update({
    where: { id: '1' },
    data: {
      liked: {
        connect: { id: '2' },
      },
    },
  })

and when I query for user '1' including likes I get

{
  id: '1',
  username: 'Cheryl',
  age: 36,
  bio: 'ornare, libero at auctor ullamcorper',
  userId: null,
  liked: [
    {
      id: '2',
      username: 'Daphne',
      age: 57,
      bio: 'at sem molestie sodales',
      userId: '1'
    }
  ]
}

My problem is that I don't know how to run the more complex queries, described above, the matches and the candidates. Any idea?

Ryan · Answer 1 · 2021-09-01T06:50:37.670

0

Your schema should be like this:

model User {
  id             String  @id
  username       String  @default(value: "")
  age            Int     @default(value: 0)
  bio            String  @default(value: "")
  liked          User[]  @relation("likedUsers")
  disliked       User[]  @relation("dislikedUsers")
  likedUser      User?   @relation("likedUsers", fields: [likedUserId], references: [id])
  likedUserId    String?
  dislikedUser   User?   @relation("dislikedUsers", fields: [dislikedUserId], references: [id])
  dislikedUserId String? @map("userId")
}

Then you can run queries like this (user 1 liked user 2):

const user = await prisma.user.update({
    where: { id: '1' },
    data: {
      liked: {
        connect: { id: '2' },
      },
    },
})

Fetching the users that user 1 liked is also easy using nested reads:

const user = await prisma.user.findUnique({
    where: { id: '1' },
    include: { liked: true }
})

edited Sep 01 '21 at 06:50

answered Aug 28 '21 at 13:07

Ryan

5,229
1
20
31

That's exactly what I tried to do but I got `Error validating field 'liked' in model 'User': The relation field 'liked' on Model 'User' is missing an opposite relation field on the model 'User'. Either run 'prisma format' or add it manually.` The queries exactly like mine and work perfectly – Riccardo Aug 28 '21 at 13:36
This works fine. The name in `@relation` should be the same and then you won't get this error. – Ryan Aug 29 '21 at 14:04
Doing that the error disappears, but the behaviour is strange: If I run the query above for user1 to like use2, user 2 is added to user 1 likes, but also user 1 is added to user 2 dislikes! – Riccardo Aug 31 '21 at 14:32
In that case, you would need to add a separate relation for both likes and dislikes. Editing the schema in my answer. The query will remain the same. – Ryan Sep 01 '21 at 06:48

One to many relationships with Self on Prisma

1 Answers1

Linked