Prisma•2w ago

how to prevent duplicate rows in my friends table

i am working on adding friends feature in my application i am using mysql database , with prisma orm i have a friends table and a users table as the name implies the user table stores the users , friends stores friend records the schema of both tables are as follows

model Users {
  id       
  username    
  email       
  uuid             
  status      
  friends     Friends[]
}
model Friends {
  id             
  username       
  friendId       
  email          
  friend_name    
  friend_request_enum
  Users  Users? @relation(fields: [userId], references: [id])
  userId Int?
}

model Users {
  id       
  username    
  email       
  uuid             
  status      
  friends     Friends[]
}
model Friends {
  id             
  username       
  friendId       
  email          
  friend_name    
  friend_request_enum
  Users  Users? @relation(fields: [userId], references: [id])
  userId Int?
}

when a friend request is sent a record is created in the friends table the here is what happens in my database as an example we have two users userA:john , userB:sara , john sends a friend request to sara a row is created as following :

username :john
  friendId:sara's uuid 
  email: john email 
  friend_name:sara  
friend_request:"SENT"

username :john
  friendId:sara's uuid 
  email: john email 
  friend_name:sara  
friend_request:"SENT"

if sara accepts the friend request is set to ACCEPTED when i fetch the user Data i am including the friends relation in the query let's say that i fetch the userData of the userB : sara that accepted the friend request the problem is friends field returned from the query is empty after some digging i discovered that's because the row created was created by the sender userA:john therefore when i query the user Data that accepted the friend request userB : sara i am getting an empty friends field , if i query userA:john i get friend list with one row that shows sara as the friend , i attempted to insert a another row in the friends table where the sender in this case is sara and the reciever is john problem is i am gonna have two rows for each friendship which means more space in my database i wanna have only one row for each friendship is there any way to achieve that + is there anything wrong with having two rows for each friendShip ?

4 Replies

Prisma AI Help•2w ago

You chose to compile with the bot. One moment while I piece together your answer… To address your question about preventing duplicate rows in your friends table and optimizing your friendship feature using Prisma with MySQL, I can provide some suggestions based on the available knowledge sources. 1. Schema Optimization: Instead of having separate rows for each direction of a friendship, you can optimize your schema to represent friendships as undirected connections between users. Here's a suggested schema modification:

model User {
  id         Int          @id @default(autoincrement())
  username   String       @unique
  email      String       @unique
  uuid       String       @unique
  status     String
  friends    Friendship[] @relation("UserFriends")
  friendsOf  Friendship[] @relation("UserFriends")
}

model Friendship {
  id           Int      @id @default(autoincrement())
  user         User     @relation("UserFriends", fields: [userId], references: [id])
  userId       Int
  friend       User     @relation("UserFriends", fields: [friendId], references: [id])
  friendId     Int
  status       String   // e.g., "PENDING", "ACCEPTED", "REJECTED"
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

  @@unique([userId, friendId])
}

model User {
  id         Int          @id @default(autoincrement())
  username   String       @unique
  email      String       @unique
  uuid       String       @unique
  status     String
  friends    Friendship[] @relation("UserFriends")
  friendsOf  Friendship[] @relation("UserFriends")
}

model Friendship {
  id           Int      @id @default(autoincrement())
  user         User     @relation("UserFriends", fields: [userId], references: [id])
  userId       Int
  friend       User     @relation("UserFriends", fields: [friendId], references: [id])
  friendId     Int
  status       String   // e.g., "PENDING", "ACCEPTED", "REJECTED"
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

  @@unique([userId, friendId])
}

This schema uses a many-to-many self-relation for users, which is more suitable for representing friendships. The @@unique constraint on [userId, friendId] ensures that there's only one friendship record per pair of users. Prisma Docs: Many-to-many self relations 2. Preventing Duplicates: The @@unique constraint in the Friendship model prevents duplicate friendships. When you try to create a friendship that already exists, Prisma will throw a unique constraint violation error. 3. Querying Friends: With this schema, you can query a user's friends like this:

const userWithFriends = await prisma.user.findUnique({
  where: { id: userId },
  incl
ude: {
    friends: {
      include: {
        friend: true
      }
    },
    friendsOf: {
      include: {
        user: true
      }
    }
  }
})

const userWithFriends = await prisma.user.findUnique({
  where: { id: userId },
  incl
ude: {
    friends: {
      include: {
        friend: true
      }
    },
    friendsOf: {
      include: {
        user: true
      }
    }
  }
})

This query will return both the friends the user has added and the users who have added this user as a friend. 4. Creating Friendships: When creating a friendship, you only need to create one record:

const friendship = await prisma.friendship.create({
  data: {
    userId: user1Id,
    friendId: user2Id,
    status: 'PENDING'
  }
})

const friendship = await prisma.friendship.create({
  data: {
    userId: user1Id,
    friendId: user2Id,
    status: 'PENDING'
  }
})

5. Accepting Friendships: To accept a friendship, you can update the status:

const updatedFriendship = await prisma.friendship.update({
  where: {
    userId_friendId: {
      userId: user1Id,
      friendId: user2Id
    }
  },
  data: {
    status: 'ACCEPTED'
  }
})

const updatedFriendship = await prisma.friendship.update({
  where: {
    userId_friendId: {
      userId: user1Id,
      friendId: user2Id
    }
  },
  data: {
    status: 'ACCEPTED'
  }
})

This approach ensures that you have only one row per friendship, regardless of who initiated it. It's more efficient in terms of database space and makes querying friendships easier. To answer your final question: While having two rows for each friendship isn't inherently wrong, it can lead to data inconsistencies and makes querying more complex. The approach suggested above is generally considered more efficient and easier to manage. Github Discussion: bidirectional many-to-many relation Remember to adjust your application logic to work with this new schema, particularly when checking if a friendship exists or when displaying friend lists.

johnny the fifthOP•2w ago

@Prisma AI Help friend User @relation("UserFriends", fields: [friendId], references: [id]) i didn't understsand the purpose of this relation ?

Prisma AI Help•2w ago

🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly. In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.

Nurul•7d ago

I would recommend you to try to remove duplication by modelling your schema something like this:

model User {
  id                   Int          @id @default(autoincrement())
  username             String
  email                String
  // Friendships where the user is the requester
  friendshipsInitiated Friendship[] @relation("FriendshipRequester")
  // Friendships where the user is the recipient
  friendshipsReceived  Friendship[] @relation("FriendshipRecipient")
}

model Friendship {
  id          Int          @id @default(autoincrement())
  requesterId Int
  friendId    Int
  status      FriendStatus
  // Define relations with unique names:
  requester   User        @relation("FriendshipRequester", fields: [requesterId], references: [id])
  friend      User        @relation("FriendshipRecipient", fields: [friendId], references: [id])

  @@unique([requesterId, friendId])
}

enum FriendStatus {
  PENDING
  ACCEPTED
  DECLINED
}

model User {
  id                   Int          @id @default(autoincrement())
  username             String
  email                String
  // Friendships where the user is the requester
  friendshipsInitiated Friendship[] @relation("FriendshipRequester")
  // Friendships where the user is the recipient
  friendshipsReceived  Friendship[] @relation("FriendshipRecipient")
}

model Friendship {
  id          Int          @id @default(autoincrement())
  requesterId Int
  friendId    Int
  status      FriendStatus
  // Define relations with unique names:
  requester   User        @relation("FriendshipRequester", fields: [requesterId], references: [id])
  friend      User        @relation("FriendshipRecipient", fields: [friendId], references: [id])

  @@unique([requesterId, friendId])
}

enum FriendStatus {
  PENDING
  ACCEPTED
  DECLINED
}

Instead of creating a record with only one user as owner, you can redesign your friendship table to represent a relationship between two users. This would ensure that queries are normalized, less redundant, and you only store one record per relationship. I also don't necessarily see any issue in duplication if it works for you and data size is not a concern

Gaming

Programming

how to prevent duplicate rows in my friends table

Did you find this page helpful?