count self relation on Prisma error: table name specified more than once

I am trying to count a self relation (followers) in Prisma2 (using PostgreSQL)

Model:

model User {
  id        String  @id @default(cuid())
  following User[]  @relation(name: "UserFollows")
  followers User[]  @relation(name: "UserFollows")
}

Query:

const user = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    _count: {
      select: { followers: true, following: true },
    },
  },
});

(using previewFeatures = ["selectRelationCount"]) and getting the following error:

Invalid prisma.user.findUnique() invocation:

Error occurred during query execution: ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("42712"), message: "table name "User" specified more than once", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("parse_relation.c"), line: Some(423), routine: Some("checkNameSpaceConflicts") }) }) })

Does anybody have any idea of what I am doing wrong?

Solution

This is a known issue with self-relations and we hope to fix it soon. If you want to track this bug, follow this github issue. Please feel free to add a comment to explain your use case/problem over there.

In the meantime, here are some workarounds that you can use:

Find count using nested read

You can use a nested read to return all the records in followers and following and find the length of those arrays to get the count. This seems like the most straightforward way, so long as you're okay with fetching all the followers/following records.

 const user = await prisma.user.findUnique({
        where: {
            id: userId,
        },
        include: {
            followers: true,
            following: true,
        },
    });
    let followerCount = user.followers.length; 
    let followingCount = user.following.length;

Find count using separate count queries.

Alternatively, you can use the count API to find followers and following counts for a certain user.

// number of followers for some user "x" = number of times x.id appaers in "following" relation of other users.
    const followerCount = await prisma.user.count({
        where: {
            following: {
                some: {
                    id: userId,
                },
            },
        },
    });

// number of users that user "x" is following = number of times x.id appaers in "followers" relation of other users.
    const followingCount = await prisma.user.count({
        where: {
            followers: {
                some: {
                    id: userId,
                },
            },
        },
    });

Change schema to use explicit many-to-many notation

If you're okay with slightly tweaking your schema, you can explicitly define the many-to-many relation table.


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

  @@id([followerId, followingId])
}

model User {
  id        String  @id @default(cuid())
  followers Follows[] @relation("follower")
  following Follows[] @relation("following")
}

You should be able to run the count query without issues in this way.