Pagination when querying and sorting by dynamic, unique fields in mongodb

Question

Spaced pagination is cut and dry when you paginate pages into separate unique fields, but how does it work, if at all, in situations with untrue fields, perhaps several of them at a time?

TL DR: Is pagination and sorting of an “advanced search” query using range-based parsing appropriate or possible? This means querying and sorting by user-selected, possibly non-unique fields.

For example, I’ll say that I wanted to share the search for words in words in a text game. Let's say each doc has score and word , and I would like to allow users to filter and sort in these fields. No field is unique. Assume the sorted index in the corresponding fields.

Starting is simple, say, the user wants to see all the words with a score of 10:

 // page 1 db.words.find({score: 10}).limit(pp) // page 2, all words with the score, ranged on a unique _id, easy enough! db.words.find({score: 10, _id: {$gt: last_id}}).limit(pp) 

But what if the user wanted to get all the words with a score of less than 10?

 // page 1 db.words.find({score: {$lt: 10}}).limit(pp) // page 2, getting ugly... db.words.find({ // OR because we need everything lt the last score, but also docs with // the *same* score as the last score we haven't seen yet $or: [ {score: last_score, _id: {$gt: last_id}}, {score: {$lt: last_score} ] }).limit(pp) 

Now, what if a user needs words with a score of less than 10 and a literal value greater than "FOO"? The query grows rapidly in complexity, and this applies only to one variant of the search form with default sorting.

 // page 1 db.words.find({score: {$lt: 10}, word: {$gt: "FOO"}}).limit(pp) // page 2, officially ugly. db.words.find({ $or: [ // triple OR because now we need docs that have the *same* score but a // higher word OR those have the *same* word but a lower score, plus // the rest {score: last_score, word: {$gt: last_word}, _id: {$gt: last_id}}, {word: last_word, score: {$lt: last_score}, _id: {$gt: last_id}}, {score: {$lt: last_score}, word: {$gt: last_word}} ] }).limit(pp) 

I assume that creating a query builder for this type of template would be feasible, but it seems terribly dirty and error prone. I tend to fall backward to skip pagination with a limited result size, but I would like to use ranked pagination if possible. Am I completely wrong in my thoughts on how this should work? Is there a better way?

Edit: for recording ...

Without viable alternatives, so far I’m actually just using pagination based on skips with a limited set of results, while preserving the missed controls. For my purposes, this is actually enough, since there is no real need to search, and then break it down into thousands.

Answer 1

You can get a pagination by sorting by a unique field and storing the value of this field for the last result. For instance:

 // first page var page = db.words.find({ score:{$lt:10}, word:{$gt:"FOO"} }).sort({"_id":1}).limit(pp); // Get the _id from the last result var page_results = page.toArray(); var last_id = page_results[page_results.length-1]._id; // Use last_id to get your next page var next_page = db.words.find({ score:{$lt:10}, word:{$gt:"FOO"}, _id:{$gt:last_id} }).sort({"_id":1}).limit(pp);