查询和查询时的远程分页在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屋！