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

Question

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?

Tasin Ishmam · Accepted Answer · 2021-07-19T19:50:57.510

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.

score 0 · Answer 2 · answered Mar 19 '23 at 15:15

0

it's a bit late but for whoever may get stuck later yo can do something like this

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

this should output something like this

//user data
"_count": {
    "followers": 99,
    "following": 99
 }

you can then add them in your frontend

answered Mar 19 '23 at 15:15

Daniel Qaldes

1

1

Will this count any relation? For example if I have another relation and I don't need to count it, will your solution include this as well? – G.G. Mar 20 '23 at 16:27
1

yes if you only want followers for example you can do something like this _count: { select: { followers: true } } instead of _count: true – Daniel Qaldes Mar 21 '23 at 17:37

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

2 Answers2

Find count using nested read

Find count using separate count queries.

Change schema to use explicit many-to-many notation

Linked