Prisma 1 Forum

Adding pagination to lists on objects

What is the best practice for adding pagination to lists on properties of objects in a schema?
As an example we may have the following schema:

type User {
  posts: [Post!]!
}

type Post {
  id: ID! @unique
  title: String!
}

If we have a users query, how would we limit the number of posts extracted by Prisma? if for example one user has 100 posts associated with it how would we turn the posts property into a paginated field, including an aggregate count? So the query you would make to the app schema might be:

{
  user {
    posts(first: 3) {
      aggregate {
        count // Should return 100
      }
      edges {
        node {
          title
        }
      }
    }
  }
}

One way is of course to fetch all the posts and then filter on the server level, but we don’t want to do that as it would be bad for performance, and a weakness for attackers.

Another way is to split the query so that only the ids of the posts are returned by the initial Prisma query that would hit the posts property and then adding a resolver to the posts property that transforms the list of ids into a pagination object. Then one could make a new query for all the data requested on the posts. However, this would cause multiple DB hits every time that resolver is run, which is also unfortunate.

Are we missing something?

Hi there !

I think you could do the following:

Schema:

type User {
  posts: PaginatedPosts
}

type Post {
  id: ID! @unique
  title: String!
}

type PaginatedPosts {
  nodes: [Post!]!
  meta: PaginationMeta!
}

type PaginationMeta {
  nodeCount: Int!
  pageCount: Int!
  pageCurrent: Int!
  nodesPerPage: Int!
}

type Query {
  user(id: ID!): User!
}

Resolvers:

{
  Query: {
    users: async (_, args, ctx, info) => ctx.prisma.query.user({where: {id: $id}},info)
  },
  PaginatedPosts: {
    posts: (parent, args, ctx, info) => ctx.prisma.query.posts(args, info),
    meta: async (parent, {first, skip}, ctx, info) => {
      const connection = await ctx.prisma.query.postsConnection(args, '{ aggregate { count } }')
      const count = connection.aggregate.count

      return {
        nodeCount: count,
        pageCount: Math.ceil(count/first),
        pageCurrent: skip/first + 1,
        nodesPerPage: first
      }
    }
  }
}

I did something like this (but not exactly) in one of my project.

Let me know if this works !

Hey @thibaud.courtoison , thanks for chiming in! However, we’re looking for a way to reduce the number of database hits a request takes to resolve, and no offence intended, the proposed solution potentially results in 3 db hits.

To clarify, we aren’t just looking for a way to make the specific query resolve, but also looking for the best practice solution, with an eye towards performance.

Maybe I might not understand everything correctly, but why not doing it in reverse, like fetching posts by user instead of doing user by posts?

posts(where: {creatorId: user_id}) {}

@catalinmiron - One could do that for specific queries, but I’m curious as to the best practice API design that allows for nested lists without making it an obvious performance problem or exploit for attackers.

Let’s say the maximum allowed pagination size is 100, and the schema has Users, Posts and Comments. If we allow the user to make a query such as

{
  users { // Will retrieve 100 users
    id
    posts { // Will retrieve up to 100 posts pr user
      id
      comments { // Will retrieve up to 100 comments pr post
        id
        text
      }
    }
  }
}

That query will then, as far as I understand, hit the database for 100 * 100 * 100 = 1000000 (1 million) rows over 1 + 100 + 100 * 100 = 10101 database queries, which would be abysmal for performance. Sure, one could reduce the max page size to something less, i.e. 50, but our allowed depth for queries would still allow for 4 nested lists, which would still be 50*50*50*50 = 6250000 rows. Or am I wrong in my calculations?

Some feedback from the Prisma team on best practice for this scaling problem would be great!

@nilan , could you or a teammate perhaps help out with some clarification or some reference material? Nested lists is a staple of GQL API’s (or any API for that matter), so there must be some reference implementation/best practice to do this without hitting the database thousands of times. Or maybe I’ve misunderstood how it works, and am overcomplicating things?

I mean, the number of rows is one thing, but hitting the database with so many queries would suck.

IMHO you’re overcomplicating things :slight_smile: You can create particular resolvers like

getUsers() // only users
getUserPosts() // only posts by user
getUserComments() // only comments by user
getUserPostsAndComments() // only posts and comments by user
getPosts() // all posts 
getPostComments() // all comments by post 
....

Are you trying to query the full database with just a single roundtrip? If this is the case, you can’t :slight_smile:

PS: I don’t know what best practice would you like to get when you’re trying to do such a deep and heavy query. You should always optimize for a single query or create multiple resolvers, each resolver to get a single/particular thing.

Creating specific resolvers for specific client queries defeats the purpose of GraphQL over REST. The beauty of GQL is having a single endpoint expose the domain as a queryable schema, where the backend resolvers rarely have to change because of new client requirements.

Furthermore, I know it can be done. Graphcool does it in their generated APIs, so the Prisma team must know of a way :slight_smile:

You’re wrong. Check this tweet: https://twitter.com/leeb/status/1004655619431731200

Interesting, nice reference.

Maybe you’re right in your suggested approach, then, of creating multiple resolvers and having the client perform more queries instead. In the example above, the client would then

  1. Retrieve users, where a user object could perhaps include 10 of their most recent posts. Show a button to view “all posts” which would then query a paginated resolver for posts by a user ID
  2. Under the “all posts” lists, each post would again perhaps return the most recent 10 comments, and then show a button to see “all comments for post”.
  3. Show a list of all comments through a paginated commentsForPost(postId:"") resolver

Perhaps nested pagination does not make sense. I’ll talk it over with the team :slight_smile:

@catalinmiron - Just thought of an issue with this.

When using Prisma Bindings, resolvers usually returns some DB query, i.e.

resolve: async (parent, { first, after }, ctx, info) => {
    return ctx.db.query.usersConnection(
        first,
        after,
      },
      info
    );
  },

How, then, would one dynamically limit the number of posts and comments that can be retrieved by the client calling that mutation?

This topic was automatically closed 45 days after the last reply. New replies are no longer allowed.