Paginate eager loaded relation

Hi,

I want to know if it’s possible to do a pagination when using eager loading to retrieve a relation ?

I tried this without success :

const post = Post
    .query()
    .with('comments', (builder) => {
        builder.whereNull('parent_id')
        builder.with('user.profile.avatar')
        builder.withCount('childs')

        builder.with('childs', (builder) => {
          builder.with('user.profile.avatar')
          builder.withCount('childs')
        })

        builder.paginate(commentPage, 10)
    })
    .where({ id: params.id })
    .firstOrFail()

I know that I can achieve the result by doing it like this :

const post = await Post
    .query()
    .where({ id: params.id })
    .firstOrFail()

post.comments = await post.comments()
    .whereNull('parent_id')
    .with('user.profile.avatar')
    .with('childs', (builder) => {
        builder.with('user.profile.avatar')
        builder.withCount('childs')
    })
    .withCount('childs')
    .orderBy('created_at', 'asc')
    .paginate(commentPage, 10)

But this way, as stated in the docs, if I want to do this on multiple elements (like multiple posts in this example), I’ll have to iterate over all of them and I’ll have the n+1 problem.

Is there any solution ?

1 Like

Hello @Majesty!

As far as I know paginating like this won’t work fundamentally since you are not paginating your query, you are paginating subquery.

For example

const post = await Post
  .query()
  .with('comments', (builder) => {
    builder.paginate(commentPage, 10)
  })
  .with('users', (builder) => {
    builder.paginate(userPage, 10)
  })
  .with('location', (builder) => {
    builder.paginate(locationPage, 10)
  })
  .fetch()

Would be really suboptimal since it has to do all the joins and fetch all the data again with each resource pagination.

Workaround for that is to fetch data at first

const post = await Post
  .query()
  .with('comments', (builder) => {
    builder.limit(10)
  })
.fetch()

And then do pagination requests to
GET /post/ID/comments?page=2 or something similar
It would remove the need to fetch posts and all related resources with each pagination and will only get you the comments

1 Like

Thank you for your answer ! I tried your solution with the limit function (and the “forPage” too), but when I use it on a list of posts (without using the where clause), the limit seems to apply for all the posts and not for each post.

Here is an example of what I have if I don’t set any limit on the query :

[
  {
    id: 1,
    comments: [ 1,  5,  6,  7, 20, 21, 22, 23, 24, 25 ],
    totalComments: 10
  },
  { id: 2, comments: [ 2 ], totalComments: 1 },
  { id: 3, comments: [], totalComments: 0 },
  { id: 4, comments: [ 3 ], totalComments: 1 }
]

As you can see, the first post have 10 comments and the other 2 have 1 comment each.

Here is the result if I set limit to 5 :

[
  { id: 1, comments: [ 1, 5, 6 ], totalComments: 3 },
  { id: 2, comments: [ 2 ], totalComments: 1 },
  { id: 3, comments: [], totalComments: 0 },
  { id: 4, comments: [ 3 ], totalComments: 1 }
]

Now the first post has only 3 comment and not 5 because the limit apply across all the posts.

Aha, it’s most likely because I was too lazy writing out this limiting part :expressionless:, edited it to be correct

const post = await Post
  .query()
  .with('comments', (builder) => {
    builder.limit(10)
  })
.fetch()

Should work like this

Virk gives 2 good examples in here: What is better for performance?