Can you elaborate on the issues with that? I've used that pattern and haven't found any major woes (yet), but also don't usually paginate complicated queries.
The efficient way to handle it is to set a lower limit on the pkid (or other atomically increasing row) of the last record fetched, and fetch in ascending order e.g.
SELECT * from my_table where id > (last_row_id_seen) ORDER BY id asc limit 20;
Then you have an indexed jump to the correct rows to return. It's pretty straightforward to abstract a batched database iterator with this pattern for use application-wide (usually can be done in ~20-50 loc), but no ORM that I'm aware of supports this pattern natively.Do note that, by extension, it's impossible to efficiently batch iterate over tables that don't have a unique, orderable key in postgres and mysql.
https://www.citusdata.com/blog/2016/03/30/five-ways-to-pagin...
Highly recommended read.
Easiest way to prevent this is add enough filtering options that it doesn't inconvenience users to limit depth to page 10 or something