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?
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:
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;
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,
},
},
},
});
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.