如何在Mongo的一个管道中加入和排序几个集合 [英] How to join and sort few collections in one pipeline in Mongo
问题描述
我想列出一个用户列表,这些列表按其背后的关系进行排序.
I want to make a list of users which sorting by relations behind them.
为此,我需要比较3个集合的ID进行排序-
For do this, I need to compare IDs of 3 collections to sort in order -
第一:收到的请求来自",
first: 'received requests from',
second:已发送请求",
second: 'sent requests to',
第三名:朋友",
第四名:其他"
更新: 输入
可识别用户背后关系的收藏集
db.friends.find({userId: currentUser});
// {"_id" : "qwC7LrtaZtQsShtzT", "userId" : "rbrwhh2AQY8mzmzd3", "friendId" : "4y7pG7p2gcGgm5ayj",}
db.requests.find({userId: currentUser});
// {"_id" : "PgC7LrtaZtQsShtzT", "userId" : "tHuxnWxFLHvcpRgHb", "requesterId" : "jZagPF7bd4aW8agXb",}
所有用户及其信息的主要收藏夹
db.users.find({userId: currentUser});
// {"_id" : "4y7pG7p2gcGgm5ayj", profile: {name: 'Andrey', second: 'Andrey'}} -> Same person(id) like in 'friends' collection.
// {"_id": "tHuxnWxFLHvcpRgHb", profile: {name: 'Erick', second: 'Erick'} -> same person who have receive request.
现在我需要汇总用户"集合,定义与前两个集合(朋友,请求)相匹配的分数.
And now I need to aggregate 'users' collection, define the score which matches with previous two collections (friends, requests).
我试图这样做
db.users.aggregate([
// here i tried to compare _id and requesterId to fetch all who received request from me:
{$lookup: {from: 'requests', localField: "_id", foreignField: "requesterId", as: "sentRequest"}},
{$unwind: '$sentRequest'},
{$project: {_id: '$sentRequest.userId', profile: 1, count: {$add: [2]}}},
// And here I tried to get all friends
{$lookup: {from: 'requests', localField: "_id", foreignField: "friendId", as: "friends"}},
{$unwind: '$friends'},
{$project: {_id: '$friends.userId', profile: '$profile', count: {$add: [3]}}}
]);
但是结果是我只有最后一部分(朋友),并且遇到了另一个问题
[ { _id: 'rbrwhh2AQY8mzmzd3', //this field is responds to person which I looking for
profile: //But profile data is given from .users and its not respont to person with given id
{ firstName: 'Andrey',
lastName: 'Andrey',
userImg: '/img/user.jpg',
userDescription: null },
count: 3 } ]
更新
预期结果
//Given: 3 collections
db.users -> provide all additional info about all users
db.requests -> groups IDs of two users 'requester' and 'receiver'
db.friends -> groups IDs of two users 'friend' and 'user', document has pair where value are swapped (but it's not important in this task);
//Need to combine all of this three collections in one queue, to sort users by their relationships between them, in kind of this list:
// 1 - Requests; 2 - Friends; 3 - Other users
// Expected result:
[
// I've got request From:
{ "_id": "tHuxnWxFLHvcpRgHb", // _id must be equal to user who sent request and _id from db.users
"profile": // this value used from db.users
{
"firstName": "Ana",
"lastName": "de Armas",
"userImg": "/img/user.jpg",
"userDescription": null
},
"weight": 4 // given value to make sorting then
},
// I sent request To:
{ "_id": "4y7pG7p2gcGgm5ayj",
"profile":
{
"firstName": "John",
"lastName": "Bonjovi",
"userImg": "/img/user.jpg",
"userDescription": null
},
"weight": 3
},
// My friend:
{ "_id": "jZagPF7bd4aW8agXb",
"profile":
{
"firstName": "Jessica",
"lastName": "Alba",
"userImg": "/img/user.jpg",
"userDescription": null
},
"weight": 2
},
// Unknown user:
{ "_id": "DdX8sPuAoZqKpa6nH",
"profile":
{
"firstName": "Sheldon",
"lastName": "Cooper",
"userImg": "/img/user.jpg",
"userDescription": null
},
"weight": 1
}
]
我尝试将聚合与$ lookup结合使用-但不适用于3个集合.
I tried to use aggregation with $lookup - but its not work with 3 collections.
结果我需要返回队列,在这里可以在新加入的集合中设置新字段-权重.有可能按此字段排序.
In result i need to return queue, where can set new field in new joined collection - weight. To have possibility to sort by this field.
推荐答案
Latest Mongo 3.4 version, you will make use $facet to process the same data across multiple aggregation pipeline and joins the result from all aggregration to single output.
db.users.aggregate([
{ "$match": { "_id":1} },
{
"$facet": {
"Received": [
{$lookup: {from: 'requests', localField: "_id", foreignField: "userId", as: "receivedRequest"}},
{$unwind: '$receivedRequest'},
{$lookup: {from: 'users', localField: "receivedRequest.requesterId", foreignField: "_id", as: "receivedUser"}},
{$project: {_id: '$receivedRequest.requesterId', profile: '$receivedUser.profile', weight: {$add: [4]}}}
],
"Sent": [
{$lookup: {from: 'requests', localField: "_id", foreignField: "requesterId", as: "sentRequest"}},
{$unwind: '$sentRequest'},
{$lookup: {from: 'users', localField: "sentRequest.userId", foreignField: "_id", as: "sendUser"}},
{$project: {_id: '$sentRequest.userId', profile: '$sendUser.profile', weight: {$add: [3]}}}
],
"Friends": [
{$lookup: {from: 'friends', localField: "_id", foreignField: "userId", as: "friends"}},
{$unwind: '$friends'},
{$lookup: {from: 'users', localField: "friends.friendId", foreignField: "_id", as: "friendUser"}},
{$project: {_id: '$friends.friendId', profile: '$friendUser.profile', weight: {$add: [2]}}}
],
"Others": [
{$lookup: {from: 'friends', localField: "_id", foreignField: "friendId", as: "others"}},
{$unwind: '$others'},
{$lookup: {from: 'users', localField: "others.userId", foreignField: "_id", as: "other"}},
{$project: {_id: '$others.userId', profile: '$other.profile', weight: {$add: [1]}}}
]
}
}
]).pretty()
样本输出:
{
"Received" : [
{
"_id" : 3,
"profile" : [
{
"name" : "John"
}
],
"weight" : 4
}
],
"Sent" : [
{
"_id" : 4,
"profile" : [
{
"name" : "Jessica"
}
],
"weight" : 3
}
],
"Friends" : [
{
"_id" : 2,
"profile" : [
{
"name" : "Ana"
}
],
"weight" : 2
}
],
"Others" : [
{
"_id" : 5,
"profile" : [
{
"name" : "Sheldon"
}
],
"weight" : 1
}
]
}
这篇关于如何在Mongo的一个管道中加入和排序几个集合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!