查询时的范围分页 &对mongodb中的动态、非唯一字段进行排序 [英] Ranged pagination when querying & sorting on dynamic, non-unique fields in mongodb

问题描述

当您基于单个唯一字段进行分页时，远程分页会被剪切和干燥，但是在具有非唯一字段(可能一次有几个)的情况下，如果有的话，它是如何工作的?

Ranged pagination is cut and dry when you're paginating based on single unique fields, but how does it work, if at all, in situations with non-unique fields, perhaps several of them at a time?

TL;DR:使用基于范围的分页对高级搜索"类型的查询进行分页和排序是否合理或可能?这意味着对用户选择的可能非唯一字段进行查询和排序.

例如，假设我想对文字游戏中播放的文字文档的搜索进行分页.假设每个文档都有一个 score 和一个 word，我想让用户在这些字段上过滤和排序.这两个字段都不是唯一的.假设有问题的字段的排序索引.

For example say I wanted to paginate a search for played word docs in a word game. Let's say each doc has a score and a word and I'd like to let users filter and sort on those fields. Neither field is unique. Assume a sorted index on the fields in question.

从简单开始，假设用户想要查看所有得分为 10 的单词:

Starting simple, say the user wants to see all 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)

但是如果用户想要获取所有分数低于 10 的单词怎么办?

But what if the user wanted to get all words with a score 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)

现在如果用户想要分数小于 10 且字母值大于FOO"的单词怎么办?查询的复杂性迅速增加，这仅适用于具有默认排序的搜索表单的一个变体.

Now what if the user wanted words with a score less than 10, and an alphabetic value greater than "FOO"? The query quickly escalates in complexity, and this is for just one variation of the search form with the default sort.

// 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 suppose writing a query builder for this sort of pattern would be doable, but it seems terribly messy and error prone. I'm leaning toward falling back to skip pagination with a capped result size, but I'd like to use ranged pagination if possible. Am I completely wrong in my thinking of how this would have to work? Is there a better way?

到目前为止没有可行的替代方案，我实际上只是使用基于跳过的分页和有限的结果集，保持跳过可管理.就我而言，这实际上已经足够了，因为没有真正需要搜索然后分页到数千个.

With no viable alternatives thus far I'm actually just using skip based pagination with a limited result set, keeping the skip manageable. For my purposes this is actually sufficient, as there's no real need to search then paginate into the thousands.

推荐答案

您可以通过对唯一字段进行排序并为最后结果保存该字段的值来获得范围分页.例如:

You can get ranged pagination by sorting on a unique field and saving the value of that field for the last result. For example:

// 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);

这篇关于查询时的范围分页 &对mongodb中的动态、非唯一字段进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！