英文:
mongoDB $sort inconsistent results
问题
我在go语言中有以下查询，运行良好：
query["name"] = bson.M{"$regex": searchStr, "$options": "i"}
query["likes"] = userSession.Id
c.Find(query).Skip(0).Limit(2).Select(bson.M{"name":1, "profile":1, "description":1, "user_id":1, "likes":1}).Sort("-pro", "-check").All(&business)
然后我尝试使用聚合框架编写相同的查询：
query["name"] = bson.M{"$regex": searchStr, "$options": "i"}
query["likes"] = userSession.Id
oe := bson.M{
"$match" :query,
}
oa := bson.M{
"$project": bson.M {"pro": 1, "check": 1, "name":1, "profile":1, "description":1, "user_id":1, "likes":1, "nrLikes": bson.M{ "$size": "$likes" }, "city": 1, "country": 1, "industry": 1},
}
ol := bson.M{
"$limit" :pageSize,
}
os := bson.M{
"$skip" :skips,
}
or := bson.M{
"$sort" : bson.M {"pro": -1, "check": -1},
}
pipe := c.Pipe([]bson.M{oe, oa, or, os, ol })
pipe.All(&business)
第二个查询大部分时间都能正常工作，但有10%的时间会返回不同顺序的结果。
有什么想法吗？
稍后编辑：以下是结果：
[]bson.M{
{
"description": "<p>sasdfdasf</p>",
"profile": []interface {}{
"rKwMmXPWheGczwvGn2TzSRU7jRorhorKwMmXPWheGczwvGn2TzSRU7jRorho=0.jpg",
},
"likes": []interface {}{
"Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
},
"nrLikes": int(1),
"name": "ediloc.com2",
"city": "Calimanesti",
"industry": "Automotive",
"_id": "Yo\xd4f\x1a\xa9Q|w\tG^",
"user_id": "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
"country": "Romania",
},
{
"_id": "Yo\xc7\xd7\x1a\xa9Qy1['\xea",
"user_id": "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
"name": "ediloc.com",
"country": "Romania",
"description": "<p>a</p>",
"profile": []interface {}{
"1ssSySNRZwGJJwqzXghL6qzAVfWZis1ssSySNRZwGJJwqzXghL6qzAVfWZis=1.jpg",
},
"likes": []interface {}{
"Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
},
"nrLikes": int(1),
"city": "Calimanesti",
"industry": "Accounting",
},
}
[]bson.M{
{
"likes": []interface {}{
"Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
},
"_id": "Yo\xd4f\x1a\xa9Q|w\tG^",
"name": "ediloc.com2",
"city": "Calimanesti",
"country": "Romania",
"profile": []interface {}{
"rKwMmXPWheGczwvGn2TzSRU7jRorhorKwMmXPWheGczwvGn2TzSRU7jRorho=0.jpg",
},
"user_id": "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
"industry": "Automotive",
"nrLikes": int(1),
},
{
"_id": "Yo\xc7\xd7\x1a\xa9Qy1['\xea",
"user_id": "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
"industry": "Accounting",
"profile": []interface {}{
"1ssSySNRZwGJJwqzXghL6qzAVfWZis1ssSySNRZwGJJwqzXghL6qzAVfWZis=1.jpg",
},
"likes": []interface {}{
"Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
},
"nrLikes": int(1),
"name": "ediloc.com",
"city": "Calimanesti",
"country": "Romania",
"description": "<p>a</p>",
},
}
[]bson.M{
{
"nrLikes": int(1),
"user_id": "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
"description": "<p>a</p>",
"profile": []interface {}{
"1ssSySNRZwGJJwqzXghL6qzAVfWZis1ssSySNRZwGJJwqzXghL6qzAVfWZis=1.jpg",
},
"country": "Romania",
"industry": "Accounting",
"likes": []interface {}{
"Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
},
"_id": "Yo\xc7\xd7\x1a\xa9Qy1['\xea",
"name": "ediloc.com",
"city": "Calimanesti",
},
{
"name": "ediloc.com2",
"industry": "Automotive",
"description": "<p>sasdfdasf</p>",
"likes": []interface {}{
"Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
},
"user_id": "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
"city": "Calimanesti",
"country": "Romania",
"profile": []interface {}{
"rKwMmXPWheGczwvGn2TzSRU7jRorhorKwMmXPWheGczwvGn2TzSRU7jRorho=0.jpg",
},
"nrLikes": int(1),
"_id": "Yo\xd4f\x1a\xa9Q|w\tG^",
},
}
pro和check字段是int32类型，具有较高pro字段编号的文档应优先于具有较高check字段的文档。
<details>
<summary>英文:</summary>
I have the following query in go lang which works fine:
query["name"] = bson.M{"$regex": searchStr, "$options": "i"}
query["likes"] = userSession.Id
c.Find(query).Skip(0).Limit(2).Select(bson.M{"name":1, "profile":1, "description":1, "user_id":1, "likes":1}).Sort("-pro", "-check").All(&business);
Then I tried to write the same query using the aggregation framework:
query["name"] = bson.M{"$regex": searchStr, "$options": "i"}
query["likes"] = userSession.Id
oe := bson.M{
"$match" :query,
}
oa := bson.M{
"$project": bson.M {"pro": 1, "check": 1, "name":1, "profile":1, "description":1, "user_id":1, "likes":1, "nrLikes": bson.M{ "$size": "$likes" }, "city": 1, "country": 1, "industry": 1},
}
ol := bson.M{
"$limit" :pageSize,
}
os := bson.M{
"$skip" :skips,
}
or := bson.M{
"$sort" : bson.M {"pro": -1, "check": -1},
}
pipe := c.Pipe([]bson.M{oe, oa, or, os, ol })
pipe.All(&business)
The second query works fine 90% of the time, but 10% of the times it returns a different order for results.
Any thoughts?
Later edit: Here are the resuls
[]bson.M{
{
"description": "<p>sasdfdasf</p>",
"profile": []interface {}{
"rKwMmXPWheGczwvGn2TzSRU7jRorhorKwMmXPWheGczwvGn2TzSRU7jRorho=0.jpg",
},
"likes": []interface {}{
"Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
},
"nrLikes": int(1),
"name": "ediloc.com2",
"city": "Calimanesti",
"industry": "Automotive",
"_id": "Yo\xd4f\x1a\xa9Q|w\tG^",
"user_id": "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
"country": "Romania",
},
{
"_id": "Yo\xc7\xd7\x1a\xa9Qy1['\xea",
"user_id": "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
"name": "ediloc.com",
"country": "Romania",
"description": "<p>a</p>",
"profile": []interface {}{
"1ssSySNRZwGJJwqzXghL6qzAVfWZis1ssSySNRZwGJJwqzXghL6qzAVfWZis=1.jpg",
},
"likes": []interface {}{
"Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
},
"nrLikes": int(1),
"city": "Calimanesti",
"industry": "Accounting",
},
}
[]bson.M{
{
"likes": []interface {}{
"Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
},
"_id": "Yo\xd4f\x1a\xa9Q|w\tG^",
"name": "ediloc.com2",
"city": "Calimanesti",
"country": "Romania",
"profile": []interface {}{
"rKwMmXPWheGczwvGn2TzSRU7jRorhorKwMmXPWheGczwvGn2TzSRU7jRorho=0.jpg",
},
"user_id": "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
"industry": "Automotive",,
"nrLikes": int(1),
},
{
"_id": "Yo\xc7\xd7\x1a\xa9Qy1['\xea",
"user_id": "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
"industry": "Accounting",
"profile": []interface {}{
"1ssSySNRZwGJJwqzXghL6qzAVfWZis1ssSySNRZwGJJwqzXghL6qzAVfWZis=1.jpg",
},
"likes": []interface {}{
"Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
},
"nrLikes": int(1),
"name": "ediloc.com",
"city": "Calimanesti",
"country": "Romania",
"description": "<p>a</p>",
},
}
[]bson.M{
{
"nrLikes": int(1),
"user_id": "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
"description": "<p>a</p>",
"profile": []interface {}{
"1ssSySNRZwGJJwqzXghL6qzAVfWZis1ssSySNRZwGJJwqzXghL6qzAVfWZis=1.jpg",
},
"country": "Romania",
"industry": "Accounting",
"likes": []interface {}{
"Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
},
"_id": "Yo\xc7\xd7\x1a\xa9Qy1['\xea",
"name": "ediloc.com",
"city": "Calimanesti",
},
{
"name": "ediloc.com2",
"industry": "Automotive",
"description": "<p>sasdfdasf</p>",
"likes": []interface {}{
"Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
},
"user_id": "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
"city": "Calimanesti",
"country": "Romania",
"profile": []interface {}{
"rKwMmXPWheGczwvGn2TzSRU7jRorhorKwMmXPWheGczwvGn2TzSRU7jRorho=0.jpg",
},
"nrLikes": int(1),
"_id": "Yo\xd4f\x1a\xa9Q|w\tG^",
},
}
Pro and check fields are in32, the documents with higher pro field number should have priority over the documents that have higher check fields.
</details>
# 答案1
**得分**: 2
确保在限制和跳过阶段之前设置排序管道阶段。只有在排序输入上，才能可靠地获得相同的结果。
**编辑**
意识到您正在使用`bson.M {"pro": -1, "check": -1}`来定义排序顺序。在Go中，映射的迭代顺序是未指定的，可能会发生变化。这可能是您得到不一致结果的原因。
尝试将其更改为`bson.D`，以便保持按列排序的顺序。
可以查看查询[Sort](http://bazaar.launchpad.net/+branch/mgo/v2/view/head:/session.go#L2130)方法如何从提供的字符串构建它。
对于您的用例，您可以将`or`变量更改为：
or := bson.M{
"$sort": bson.D{
bson.DocElem{Name: "pro", Value: -1},
bson.DocElem{Name: "check", Value: -1},
},
}
<details>
<summary>英文:</summary>
Make sure you have your sort pipeline stage *before* your limit & skip stages. You can only reliably obtain the same results with limit/skip on sorted input.
**EDIT**
Realised that you are using `bson.M {"pro": -1, "check": -1}` to define your sort order. The iteration order of a map is unspecified in Go and can change. Hence this is probably why you are getting inconsistent results.
Try changing this to a `bson.D` so that the order of columns to sort by is maintained.
It make help to see how the query [Sort](http://bazaar.launchpad.net/+branch/mgo/v2/view/head:/session.go#L2130) method constructs this from the strings you provide.
For your use case, you would change the `or` variable to:
or := bson.M{
"$sort": bson.D{
bson.DocElem{Name: "pro", Value: -1},
bson.DocElem{Name: "check", Value: -1},
},
}
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论