I am working on a project where I have an entity called Author that can have multiple Books associated with it. My goal is to retrieve all Authors who have more than 5 books in my database. The application is built using Prisma in conjunction with Next.js 14, and there is also pagination logic implemented within the API.
To find the count of books for each Author in Prisma, I am utilizing the following code snippet:
const books = await prisma.author.findMany({
take: limit,
skip: (page - 1) * limit,
select {
id: true,
name: true,
books: {
select: {
id: true,
bookName: true,
publishedDate: true,
},
},
_count: {
select: { books: true }
}
}
});
However, I have encountered an issue where Prisma does not allow me to reference the count value in order to include a condition in my query to only fetch authors with more than 5 books.
I came across a solution on stack overflow suggesting the use of JavaScript's filter method on the result set to filter out the desired data. However, due to pagination being in place, this approach would lead to data loss as I am working with a fixed number of rows already retrieved.
In an attempt to access the count value, I tried setting an alias for _count, but unfortunately, this did not yield the desired outcome either.
Another approach I experimented with was using groupBy, but this method does not allow me to select additional fields with custom objects like books when specifying fields within the by() method.