Many-to-many Self Relation Prisma - One Field

Question

I'm trying to create a friendship mechanic for my app using Prisma among other tools. In the docs it shows the following example for how to create a many-to-many self relation:

model User {
  id         Int       @id @default(autoincrement())
  name       String?
  followedBy Follows[] @relation("following")
  following  Follows[] @relation("follower")
}

model Follows {
  follower    User @relation("follower", fields: [followerId], references: [id])
  followerId  Int
  following   User @relation("following", fields: [followingId], references: [id])
  followingId Int

  @@id([followerId, followingId])
}

I have implemented this and it works, however the issue is that for friendships, there is no 'following' and 'followedBy', you're just friends. At the moment, when I query, I have to query both fields in order to find all of a user's friends. Is there any way to define this type of relationship with only one field? Whereby we just have a single list of friends on a user?

Bluenose

It's typically nice to have both sides of the relation so that you can understand who requested the friendship vs. who accepted it. You could have a single friends User[] field, but you would lose any meta-information about the friendship. – Hakim

@AustinCrim, I would disagree. It might be nice to have a many-to-many self-relation solely for storing metadata on friend requests. However, OP would still need an additional Friends User[] many-to-many self-relation to know who actually is friends, which would, again, come with an additional FriendsThrowaway User[] field. I also don't think it's common to use a many-to-many self-relation field for more than one purpose, which is why it's unfortunate that Prisma requires two fields. Seems cleaner to do it the old way by creating a "Friends" table composed of two composite keys. – Connieconniption

Answer 1

I agree that it would be nice if Prisma could more natively support this sort of self-relation where the relationship is expected to be symmetric (e.g. userA is friends with userB if and only if userB is friends with userA).

However, as far as I can tell Prisma insists on having two "sides" of the relationship. (If someone knows better, I would love to hear it!) So what follows is the approach I am taking, which avoids having to query both relations to find a user's full set of friends.

Concept

1. We'll use one "side" of the relation to contain the complete set of friends. The other "side" exists solely to meet Prisma's requirement, and we'll never query it directly.

2. When adding or removing a friend relationship, we'll make two prisma calls, one to update each object.

Code

Schema file:

model User {
  id         Int       @id @default(autoincrement())
  name       String?
  friends    User[]    @relation("UserFriends")

  // This second "side" of the UserFriends relation exists solely 
  // to satisfy prisma's requirements; we won't access it directly.
  symmetricFriends  User[] @relation("UserFriends")
}

Methods to add and remove friendships (there's plenty of redundant code in here that could be abstracted out, but I think it's clearer to read this way):

const addFriendship = async (userIdA: string, userIdB: string) => {
  await prisma.user.update({
    where: {id: userIdA},
    data: {friends: {connect: [{id: userIdB}]}},
  });
  await prisma.user.update({
    where: {id: userIdB},
    data: {friends: {connect: [{id: userIdA}]}},
  });
};

const removeFriendship = async (userIdA: string, userIdB: string) => {
  await prisma.user.update({
    where: {id: userIdA},
    data: {friends: {disconnect: [{id: userIdB}]}},
  });
  await prisma.user.update({
    where: {id: userIdB},
    data: {friends: {disconnect: [{id: userIdA}]}},
  });
}

With this approach, one can load a user and get all their friends in the expected manner, e.g.

const getUserWithFriends = async (userId) => 
  await prisma.user.find({
    where: {id: userId},
    include: {friends: true},
  });

Pani

Cool idea! I ended up just constantly calling both fields, which is a nuisance, but it does work. When I inevitabley overhaul my system, I'll give this approach a shot! – Bluenose

@brahn, I like the approach insomuch makes getUserWithFriends query clean. However, afaict, it doesn't handle friend request relations and would require a separate table w/ composite key, to handle. The separate table can also include add'l metadata such as relations state (request, accepted), type of friend (family, close friends, casual friend, etc.), but at cost of a more complex query. My SQL chops are not great, and I'm a Prisma noob, so any add'l insights are appreciated (right now I'm thinking to go w/a separate table as @ cush suggested. Thanks! – Bixler

@RolandAyala Re: "doesn't handle friend request relations" -- I think I may have gotten lucky here in that my particular query pattens (specific findManys) are well-supported, but you're definitely right that others (especially create and update) are not. And more generally I expect your identified tradeoffs are correct. – Pani