Prisma get count based of single field-CodePudding

I have a comment table which have many interactions.

model comment {
    id          Int      @id @default(autoincrement())
    content     String
    commenterId String

    commenter            User                  @relation("commented", fields: [commenterId], references: [id])
    comment_interactions comment_interaction[]
}

comment_interaction table looks like this

model comment_interaction {
    userId    String
    commentId Int
    state     CommentInteraction
    user      User               @relation(fields: [userId], references: [id])
    comment   comment            @relation(fields: [commentId], references: [id])

    @@unique([userId, commentId])
}

Here state is a CommentInteraction type enum.

enum CommentInteraction {
    LIKED
    DISLIKED
}

Now what is want is to query comment table and get count of both LIKED interaction and DISLIKED interactions. I can get count of either one only like so:

const comments = await prisma.comment.findUnique({
    where: { id },
    select: {
        id: true,
        content: true,
        _count: {
            select: {
                comment_interactions: { where: { state: "LIKED" } },
            },
        },
    },
})

But how do I get count of both.

CodePudding user response：

It's not possible for now with prisma

To count your comment_interactions you need to run a second query like

const count = await prisma.comment_interaction.groupBy({
  by: ["state"],
  where: {
    commentId: id
  },
  _count: {
    state: true,
  },
});

and then aggregate your query with content and this one

Else you can do a raw query but it's less pretty