Prisma : how can I add a column on a query return?

Question

Let's say I have a table User with a name and an Id and a table Post with a name and a content. The two tables are linked through a many to many relation ( one post can have many users / authors and each user can have many posts )

Example :

model User {
  id            Int      @id @default(autoincrement())
  name          String
  posts         users_to_posts[]
}

model Post {
  id            Int      @id @default(autoincrement())
  name          String
  users         users_to_posts[]
}

model user_to_post {
  user          user? @relation(fields: [user_id], references: [id])
  user_id       Int
  post          signe? @relation(fields: [post_id], references: [id])
  post_id       Int
  @@id([user_id, post_id])
}

What I would like to do is query the user table and have the top 10 users who have written the most posts.

However, I do not wish do just return a combination of user + post count but have a full return of the user, his Id and the amount of posts he have written as a separate key in the returned JSON

Example of what I am attempting to get ( using nextJS ) :

import { PrismaClient, Prisma } from '@prisma/client'

const prisma = new PrismaClient()


export default async function handler(req, res) {
    const ret = await prisma.user.findMany({
        include: {
            posts: {
                select: {
                    post: true
                }
            }
        }
        // include post count
        // order by post count
        // limit 10
    });
    res.status(200).json(ret)
}

As you can see I do not have the 'count' column in my table and it has to be inserted during the query

My best alternative right now is to parse the obtained json ( the ret variable ) and do everything through typescript but this is far from ideal

user_8855844 · Answer

You can use orderBy to sort user records by the count of posts they have. Furthermore, you can use the take operator to limit the number of records (works similar to the LIMIT command in SQL). Finally, to include the count of posts, you can add that inside include.

Here is the query you're looking for

await prisma.user.findMany({
    orderBy: {
        posts: {
            _count: "desc",
        },
    },
    take: 10,
    include: {
        _count: {
            select: {
                posts: true,
            },
        },
    },
});

From what I understand from your question, you only want to return the count of posts, not the post records themselves. However, if you also want to return the post records, you can also add that to the include statement, like you are currently doing.

Further Reading from the Docs for the operators used:

Prisma : how can I add a column on a query return?

1 Answers