Hey all, I'm wondering if someone can explain self relations by use of a join table. I know has been discussed previously but there's something I'm still missing some important about how it works. The use-case here is having a

User

model where it can have friends who are also users. About 5 months ago I saw a nice explanation by @Ryan where he said we could use the following model:

model User {
  id        String         @id @default(cuid())
  createdAt DateTime       @default(now())
  updatedAt DateTime       @updatedAt
  friends   Relationship[]
}
model Relationship {
  id        String   @id @default(cuid())
  user      User?    @relation(fields: [userId], references: [id])
  userId    String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

I think the thing that would clear this up for me would be to see example prisma create commands that would show how to take 2 users and first create a relation from

user1 -> user2

and then how to do the back relation. I'll start- thanks so much!

const user1 = await prisma.user.create({ data: { id: "1" } })
const user2 = await prisma.user.create({ data: { id: "2" } })
const relationship1To2 = await prisma.relationship.create({ //?? })
const relationship2To1 = await prisma.relationship.create({ //?? })

(My confusion stems from the fact that it seems like if we create a relationship where we set

user1.id

to

userId

, we will establish the relationship between

user1->friendship

but not back to

user2

.)

@Stephen Jensen 👋 If it’s just a simple friends model, I would recommend using the following:

model User {
  id      Int      @id @default(autoincrement())
  name    String
  friends Friend[] @relation("friends")
  fr      Friend[] @relation("fr")
}

model Friend {
  id     Int   @id @default(autoincrement())
  user   User? @relation("friends", fields: [userId], references: [id])
  userId Int?
  fr     User? @relation("fr", fields: [frId], references: [id])
  frId   Int?
}

And then you can set the relationship as follows:

let user1 = await prisma.user.create({ data: { name: 'user 1' } })
  let user2 = await prisma.user.create({ data: { name: 'user 2' } })
  let user3 = await prisma.user.create({ data: { name: 'user 3' } })

  await prisma.user.update({
    where: { id: user1.id },
    data: {
      friends: {
        create: [
          { fr: { connect: { id: user2.id } } },
          { fr: { connect: { id: user3.id } } },
        ],
      },
    },
  })

  await prisma.user.update({
    where: { id: user2.id },
    data: {
      friends: {
        create: [
          { fr: { connect: { id: user1.id } } },
          { fr: { connect: { id: user3.id } } },
        ],
      },
    },
  })

The output will look something like this on querying for user’s friends:

Ooh that makes so much more sense that you create both the

friends

and

fr

and then essentially ignore the

fr

field when you are trying to query for friends. Thanks so much for the awesome reply @Ryan! 🎉

follow up on this. The issue I have with the above example is User 3 is showing they don't have any friends, because it's only joining on 1 field

@Robert Witchell 👋 For friends, you only need to check a single relation. If you are creating a follower/following type relationship, then the followers will be in one relation and the following in the other. In case of friends, you can just use the single relation and not worry about the other as that will just be used to store the many-to-many relation. You can check out an implementation here that has the same 🙂

Thanks Ryan. I wanted to store that data in a separate model, as I wanted to store extra metadata about the relationship, but it became too difficult to handle. Instead, I opted for what you mentioned after figuring out how to make it all work 🙂