Is it possible to use paginator with where clause ? (order)

[kamilcglr]

Hello, I am using the .paginate() function and I noticed that if I add a where clause I have duplicates in my results.

[RomainLanz]

Hey @kamilcglr! 👋🏻

It would be easier to help you with some code examples and results.

[kamilcglr]

Hello @RomainLanz, I agree that my question lacks precision
Context: I have a model "MyModel" that allows to retrieve IP from a Postgres table. This table contains several thousands of rows (pk is "ip") and I need to create a file with the results. To do this I use the paginator in a for loop by adding the results in a csv file as I go along.

Here is my code:

for (let page = 1; page <= lastPage; page++) {
    const results = await MyModel.query()
        .select('ip')
        .where('type', MyCondition)
        .paginate(page, 10000)

  const ips = results.toJSON().data
}

By doing this I thought I would get different results for each query but I found that at times I had results that I already got.
I thought it might be because of the "Where" condition but even without it's the same thing.

I finally solved my problem by adding an orderBy('ip').

[RomainLanz]

Yes, you should always sort the data when you want to paginate them to avoid a different order every time you query the data. 👍🏻

[kamilcglr]

It seems obvious now but as I was following the doc and it's not mentioned I didn't think of it right away. Thank you for your answer

[RomainLanz]

We could improve the documentation by adding a small note on that. Happy to accept a PR if you want to do it. 🤗