How to select data from an entity using Prisma where the count of its children is greater than a specific value?

Question

I have an entity called Author that can have many Books under it. I want to select all Authors associated with more than 5 books from my database. I am using Prisma in my Next 14 application and I also have some pagination logic inside my API call.

I can find the count of books under the Author inside Prisma by using the following 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 noticed that Prisma won't let me refer to count so that I can insert the condition in my query such that only authors with more than 5 books will be retrieved.

I have found a solution on stack overflow that suggests that I should use the JS filter method on the returned result to filter out the data I want, but because I am using pagination, I will lose data that way from a fixed count of rows that I have already retrieved.

I have tried to set an alias to _count, in case that would potentially let me access the value, but that doesn't work either.

I have tried to use groupBy instead but there I can't select additional fields with custom objects inside, such as books (when I place my fields inside the by() method).

Answer 1

Take a look at cursor-based pagination (documentation). You can combine filtering in code, as suggested, with cursors to enable pagination.

In short, you would do your Prisma query and filter the results in JS. Once you have the desired number of results, you would return the result set. When the client requests more results, they would provide the ID of the last result as cursor value. You can then use this to do the consecutive query:

// Client provides the ID
const lastResultId = req.params.lastResultId;

const books = await prisma.author.findMany({
  // As suggested by documentation, skip the actual
  // record with cursor value
  skip: 1,
  cursor: {
    id: lastResultId
  },
  select {
    // ...
    },
    _count: {
      select: { books: true }
    }
  }
});

Then filter this dataset and return page 2, and client would have the cursor value for requesting page 3.