[Database] Improvements to the pagination without shifts (e.g. when records are skipped/revisited in next pages due to updates in previous pages)

[jonasraoni]

Problem

Right now there are methods that provide this feature, such as the forPageAfterId, forPageBeforeId and chunkById, but I had issues with them:

• forPageAfterId/forPageBeforeId: As it requires me to pass the last key, the code becomes a bit verbose.
• chunkById: This method addresses the previous complaint, but the use of a callback makes it not that great to use. I think this should have been implemented as a generator/iterator, then I'd be able to process it on demand instead of moving all the logic inside a callback, which in my specific case isn't feasible (I've left my use case below).
• General: All the methods just support a single column, so if I'd like to have a composed ordering, such as ORDER BY section_id, post_id, I'd have to go with tricks such as CONCAT(section_id, '-', post_id)... But this is limited (I'm unable to to sort one column by ASC and another by DESC) and slower, as the database won't be able to use indexes.

chunkById use case:

I'm using an open-source library that processes tasks in parallel, after feeding an initial set of data to it, I can call a $pool->wait($callback) which internally is a while(!$finished) callback(...) sending updates about the processing... Whenever a task is finished, I want to feed in more data, and it would be great to use the chunkById, but:

• It's not possible to quit listening the wait (I've created an issue in the other repository to allow it), feed more data, then call wait again (which would allow me to use the callback)
• I can't wait the tasks to finish processing (I'll lose parallelism)
• I'm not willing to cache the results of the chunkById.

Solution

• Use a generator on the chunkById (it could be a safe deprecation, such as introducing a new method chunkByIds)
• Improve the chunkById, forPageAfterId and forPageBeforeId by allowing the user to specify more columns + sorting (then forPageAfterId and forPageBeforeId could be even merged into a single method).

[jonasraoni]

If the idea is welcome I can create a PR, I already have a working code that I've written to fix my issues (a kind of chunkById that uses a generator to pass through the pages and supports multiple columns + variable sorting).

[jonasraoni]

In case someone has the same needs as me, I've created an extension package for myself with the features I needed, I hope it will become deprecated later 👀 Package: https://packagist.org/packages/jonasraoni/query-builder-extensions Repository: https://github.com/jonasraoni/query-builder-extensions