Prisma•4mo ago

self-relation

Greetings! Could someone help me? I haven't been able to understand this. I'm checking the followers and following of a user and everything appears correct in my db, but then when I check it the result is reversed. Any idea why? That 1 should appear in following and not in followers.

22 Replies

edarcodeOP•4mo ago

edarcodeOP•4mo ago

Nurul•4mo ago

Do you mean the value in followingId and followerId should be replaced? Can you please elaborate?

edarcodeOP•4mo ago

Currently there are 2 users, the idea is that user A follows user B To represent this follow-up, there must be a table containing AId and BId, I named it Follow

edarcodeOP•4mo ago

As you can see, A or followerId already follows B or followingId So when I check A and his followers it should return a 0, and his following should return a 1, but it comes out the other way around. And I'm very frustrated because I've already checked every possible error in detail and I don't see anything wrong T.T @Nurul (Prisma)

edarcodeOP•4mo ago

final result is inverted

edarcodeOP•4mo ago

I have deleted package.lock, updated dependencies, asked many colleagues, etc. and I have not been able to find the error, I have checked my schema a thousand times, I am dead, I have asked chatGPT. I have read the doc I really don't want to leave Prisma, I like it a lot. And I don't see any alternatives either. So I don't know what to do.

Nurul•4mo ago

Can you share your schema.prisma file? Also, how are you inserting the record in Follow table?

edarcodeOP•4mo ago

yes 1s

model User {
  id        String   @id @default(uuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  followers Follow[] @relation("followers")
  following Follow[] @relation("following")
}

model Follow {
  followerId  String
  followingId String
  createdAt   DateTime @default(now())

  follower  User @relation("followers", fields: [followerId], references: [id])
  following User @relation("following", fields: [followingId], references: [id])

  @@id([followerId, followingId])
}

model User {
  id        String   @id @default(uuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  followers Follow[] @relation("followers")
  following Follow[] @relation("following")
}

model Follow {
  followerId  String
  followingId String
  createdAt   DateTime @default(now())

  follower  User @relation("followers", fields: [followerId], references: [id])
  following User @relation("following", fields: [followingId], references: [id])

  @@id([followerId, followingId])
}

edarcodeOP•4mo ago

@Nurul (Prisma)

Nurul•4mo ago

Let me create a minimal working example with your schema and share it with you

edarcodeOP•4mo ago

I saw you writing bro xD @Nurul (Prisma)

Nurul•4mo ago

You should get the followers and following counts separately like this. I used this schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins", "nativeDistinct", "fullTextSearch", "omitApi"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        String   @id @default(uuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  name      String

  followedBy Follow[] @relation("followedBy")
  following  Follow[] @relation("following")
}

model Follow {
  createdAt DateTime @default(now())

  followedBy   User   @relation("followedBy", fields: [followedById], references: [id])
  followedById String
  following    User   @relation("following", fields: [followingId], references: [id])
  followingId  String

  @@id([followingId, followedById])
}

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins", "nativeDistinct", "fullTextSearch", "omitApi"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        String   @id @default(uuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  name      String

  followedBy Follow[] @relation("followedBy")
  following  Follow[] @relation("following")
}

model Follow {
  createdAt DateTime @default(now())

  followedBy   User   @relation("followedBy", fields: [followedById], references: [id])
  followedById String
  following    User   @relation("following", fields: [followingId], references: [id])
  followingId  String

  @@id([followingId, followedById])
}

And this is my script.ts file:

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient({
  //log: ["query"],
});

async function main() {
  await prisma.follow.deleteMany();
  await prisma.user.deleteMany();

  //Create two users
  await prisma.user.createMany({
    data: [
      {
        name: "Alice",
        id: "1",
      },
      {
        name: "Bob",
        id: "2",
      },
    ],
  });

  // Alice follows Bob
  await prisma.follow.create({
    data: {
      followedById: "1",
      followingId: "2",
    },
  });

  // Count how many users is Alice following
  const AliceFollowingUsersCount = await prisma.follow.count({
    where: {
      followedById: "1",
    },
  });

  // Expected output: 1
  console.log("Number of users Alice Follows", AliceFollowingUsersCount);

  // Count how many users are following Alice
  const usersFollowingAliceCount = await prisma.follow.count({
    where: {
      followingId: "1",
    },
  });

  // Expected output: 0
  console.log(
    "Number of users who are following Alice",
    usersFollowingAliceCount
  );

  const user = await prisma.user.findUnique({
    where: {
      id: "1",
    },
  });

  console.log(user);
}

main()
  .catch((e) => {
    throw e;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient({
  //log: ["query"],
});

async function main() {
  await prisma.follow.deleteMany();
  await prisma.user.deleteMany();

  //Create two users
  await prisma.user.createMany({
    data: [
      {
        name: "Alice",
        id: "1",
      },
      {
        name: "Bob",
        id: "2",
      },
    ],
  });

  // Alice follows Bob
  await prisma.follow.create({
    data: {
      followedById: "1",
      followingId: "2",
    },
  });

  // Count how many users is Alice following
  const AliceFollowingUsersCount = await prisma.follow.count({
    where: {
      followedById: "1",
    },
  });

  // Expected output: 1
  console.log("Number of users Alice Follows", AliceFollowingUsersCount);

  // Count how many users are following Alice
  const usersFollowingAliceCount = await prisma.follow.count({
    where: {
      followingId: "1",
    },
  });

  // Expected output: 0
  console.log(
    "Number of users who are following Alice",
    usersFollowingAliceCount
  );

  const user = await prisma.user.findUnique({
    where: {
      id: "1",
    },
  });

  console.log(user);
}

main()
  .catch((e) => {
    throw e;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Nurul•4mo ago

Now here is the correct output that you would expect

edarcodeOP•4mo ago

but _count works correctly for you? My idea is to consult the user directly and from there select some data from their relationships, so as not to have to make 2 queries.

Nurul•4mo ago

The reason your query shows inverted numbers is because this

_count :{
  select: { followers: true, following: true}
}

_count :{
  select: { followers: true, following: true}
}

would not return the number of followers and number of following as you are expecting. It shows all the records in the Follows table that has followerId: 99f977... and all followingId that has value 99f977... which is not what you want.

edarcodeOP•4mo ago

mmm ok ok

Nurul•4mo ago

As you can see in the screenshot, you had 1 record with followeId: 99f and 0 records with followingId: 99f which is what was returned.

Gaming

Programming

self-relation