在mongodb中执行三个集合的联接? [英] Perform joins in mongodb with three collections?
问题描述
我正在使用$ lookup在mongoDB中加入不同的集合.现在我在这里面临一个问题，假设我在下面给出了3个集合.
I am using $lookup for joining different collections in mongoDB. Now i am facing a problem here suppose i have 3 collections given below.
user_movies
{
"_id": "_id" : ObjectId("5834ecf7432d92675bde9d83"),
"mobile_no": "7941750156"
"movies" : ["dallas00", "titanic00", "green_mile00"]
}
电影
{
"_id": "_id" : ObjectId("4834eff7412d9267556d9d52"),
"movie_name" : "Dallas Buyer's Club",
"movie_id": "dallas00",
"active": 0
}
电影评论
{
"_id": "_id" : ObjectId("1264eff7412d92675567h576"),
"movie_id" : "dallas00",
"comment": "what a great movie.",
"time_posted": "1480516635131"
},
{
"_id": "_id" : ObjectId("1264eff7412d92675567h578"),
"movie_id" : "dallas00",
"comment": "awesome movie.",
"time_posted": "1480516635141"
},
{
"_id": "_id" : ObjectId("1264eff7412d92675567h567"),
"movie_id" : "titanic00",
"comment": "titanic awesome movie.",
"time_posted": "1480516635132"
},
{
"_id": "_id" : ObjectId("1264eff7412d92675567h579"),
"movie_id" : "green_mile00",
"comment": "Tom hanks did awesome movie.",
"time_posted": "1480516635133"
}
用户电影"是我存储用户喜欢的电影的集合，电影"是我存储有关电影的所有详细信息的集合，而"movie_comments"是我存储与电影相关的评论的集合.
User Movies is a collection where i am storing user liked movies, Movies is a collection where i am storing all details about the movie and movie_comments is the collection where i am storing comments associated to the movie.
现在，我想编写一个查询，在其中我要向用户显示他们喜欢的电影列表，但所有电影都必须具有"active":1，以及与该特定电影相关的评论.现在我尝试使用$ lookup，下面给出了我的代码.
Now i want to write a query where i am going to show users the list of their movies they have liked but all movies must have "active" : 1, and also the comments associated to that particular movie. Now i tried to use $lookup my code is given below.
db.user_movies.aggregate(
{$match : {mobile_no : mobile_no}},
{ "$unwind": "$movies" },
{$lookup: {from: "movies",localField: "movies",foreignField: "movie_id",as: "bmarks"}},
{$unwind : "$bmarks"},
{$match : {"bmarks.active": 1}},
{ $group : { _id : "$_id", movies : {$push : "$bmarks"}, movie_ids: {$push : "$bmarks.movie_id"}}},
{$lookup: {from: "movie_comments", localField: "",foreignField: "movie_id",as: "comments"}},
{$unwind : "$comments"},
{$sort: {time_posted: -1}},
{$group: {_id: '$_id', comments : {$push : "$comments"}}},
我试图编写一个查询来执行所有这些功能，并且我能够对前两个集合(user_movies和电影)执行联接，但是我无法在同一查询中对第三个集合执行查找.我要发送的是我的首次查找的输出数组，该数组是 movie_ids ，并通过 movie_comments 将其发送到下一个查找.这样我就可以在我的movie_ids数组中获得与该电影相关的所有评论.
I am trying to write a single query for doing all this functionality and i am able to perform join on first two collections that is user_movies and movies but i am not able to perform lookup on third collection in the same query. What i want is to send output array of my first lookup which is movie_ids and send it to next lookup with movie_comments. So that i can have all comments associated with the movie i have in my movie_ids array.
现在任何人都可以告诉我为什么我没有得到任何输出，我可以像第二次使用它一样使用查找(将localfield用作$ group字段)还是不能执行此操作?一个查询中的功能.
Now can anyone please tell me why i am not getting any output, can i use lookup like i am using it second time (which is using localfield as a $group field) or not than how can i perform this functionality in a one single query.
更新-现在只剩下一件事了，我想以一种排序的方式获得结果，这样电影将按照它们出现在"user_movies"数组中的方式进行排序(根据它们在数组中的位置) )及其对应的评论将按照时间发布"的降序排列.
Update- Now there is only one thing left, i want to get results in a sorted manner such that movies will be sorted like they appear in "user_movies" array(according to their position in array) and their corresponding comments will sorted according to 'timeposted' in decreasing order.
因此，对于上述文档，我希望我的输出像
So for above documents i want my output to be like
{
"movie_id": "dallas00" // dallas00 is first because it appear first in "user_movies" movies array.
"movie_name": "Dallas Buyer's Club",
"comments": ["what a great movie.","awesome movie."]
},
{
"movie_id": "titanic00" // titanic00 is second because it appear second in "user_movies" movies array.
"movie_name": "Titanic",
"comments": ["titanic awesome movie."]
},
{
"movie_id": "green_mile00" // green_mile00 is third because it appear third in "user_movies" movies array.
"movie_name": "Green mile",
"comments": ["Tom hanks did awesome movie."]
},
好一件事，如果我可以在第一次执行查找时获得与之匹配的电影数组字符串的位置，那么我就可以根据我要赋予的权重进行排序(将更多的权重赋予以前的位置，依此类推).所以现在我要做的是{{$ sort:{'movie_weight':-1，'time_posted':-1}}.现在，我将对结果进行排序，以便根据电影在"user_movies"数组中的位置对电影进行排序，并根据与电影相对应的"time_posted"对评论进行排序.
Ok One Last thing if can get position of movies array string which is matched when i perform first lookup then i can sort according to weight which i will give(more weight to previous position and so on). So now what i will do is {{$sort: { 'movie_weight': -1, 'time_posted': -1}}. Now i will get my results sorted such that movies will be sorted according to their position in array "user_movies" array and comments will be sorted according to "time_posted" corresponding to their movies.
更新代码
{$match : {mobile_no : mobile_no}},
{$unwind: { path: "$movies", includeArrayIndex: "movieposition"}},
{$lookup: {from: "movies",localField: "movies",foreignField: "movie_id",as: "bmarks"}},
{$unwind : "$bmarks"},
{$match : {"bmarks.active": 1}},
{$group : { "_id" : "$bmarks.movie_id", movie_names : {$push : "$bmarks.movie_name"}, movie_ids: {$push : "$bmarks.movie_id"}, movie_position: {$push : "$movieposition"}}},
{$unwind : "$movie_ids"},
{$unwind : "$movie_names"},
{$lookup: {from: "movie_comments", localField: "movie_ids", foreignField: "movie_id", as: "comments"}},
{$unwind : "$comments"},
{$sort: { 'movie_position': -1, 'comments.time_posted': -1 }},
{$group: {_id: { movie_id: '$comments.movie_id', movie_name: '$movie_names' }, cmnts_ids: {$push: '$comments._id'}}},
{$project: {_id: 0, movie_id: '$_id.movie_id', movie_name: '$_id.movie_name', tot_cmnts: {"$size" : "$cmnts_ids"}, top_cmnts_ids: {$slice: ['$cmnts_ids', 0, 4]}}}},
{$skip: page*page_size},
{$limit: page_size}, */
推荐答案
好的，看来您遇到了很多问题.我会尝试总结一下.
Okay, looks like you've bunch of problems. I'll try to sum them up.
问题1:您提供的汇总代码无法编译.
Problem 1 : the aggregate code you've provided doesnt compile.
db.user_movies.aggregate(
{$match : {mobile_no : mobile_no}},
{$unwind: "$movies" },
{$lookup: {from: "movies",localField: "movies",foreignField: "movie_id",as: "bmarks"}},
{$unwind : "$bmarks"},
{$match : {"bmarks.active": 1}},
{$group : { _id : "$_id", movies : {$push : "$bmarks"}, movie_ids: {$push : "$bmarks.movie_id"}}},
{$lookup: {from: "movie_comments", localField: "",foreignField: "movie_id",as: "comments"}},
{$unwind : "$comments"},
{$sort: {time_posted: -1}},
{$group: {_id: '$_id', comments : {$push : "$comments"}}},
修复:
db.user_movies.aggregate([
{$match : {mobile_no : mobile_no}},
{$unwind: "$movies" },
{$lookup: {from: "movies",localField: "movies",foreignField: "movie_id",as: "bmarks"}},
{$unwind : "$bmarks"},
{$match : {"bmarks.active": 1}},
{$group : { _id : "$_id", movies : {$push : "$bmarks"}, movie_ids: {$push : "$bmarks.movie_id"}}},
{$lookup: {from: "movie_comments", localField: "",foreignField: "movie_id",as: "comments"}},
{$unwind : "$comments"},
{$sort: {time_posted: -1}},
{$group: {_id: '$_id', comments : {$push : "$comments"}}}])
现在假定您使用的是您提供的相同数据.
Now assume you're using the same data you have provided.
问题2:{$match : {"bmarks.active": 1}}与任何条目都不匹配.
Problem 2 : {$match : {"bmarks.active": 1}} doesnt match any entry.
修复:{$match : {"bmarks.active": 0}}
问题3:未定义查找字段{$lookup: {from: "movie_comments", localField: "",foreignField: "movie_id",as: "comments"}}
Problem 3 : No look up field defined {$lookup: {from: "movie_comments", localField: "",foreignField: "movie_id",as: "comments"}}
修复:{$lookup: {from: "movie_comments", localField: "movie_ids",foreignField: "movie_id",as: "comments"}}
问题4:没有用于上一次查找的movie_id的放空阶段
Problem 4 : No unwind stage for movie_ids for previous look up
修复:{$unwind : "$movie_ids"}
问题5:没有时间发布的字段{$sort: {time_posted: -1}}
Problem 5 : No time posted field {$sort: {time_posted: -1}}
修复:排序前包含字段
因此，要将所有内容放在一起，您需要进行汇总以使其看起来像下面的内容，以提取每部电影的评论.
So, to put everything together you need aggregate to look like something below for extracting the comments for each movie.
db.user_movies.aggregate([
{$match : {mobile_no : mobile_no}},
{$unwind: "$movies"},
{$lookup: {from: "movies",localField: "movies",foreignField: "movie_id",as: "bmarks"}},
{$unwind : "$bmarks"},
{$match : {"bmarks.active": 0}},
{$group : { _id : "$_id", movies : {$push : "$bmarks"}, movie_ids: {$push : "$bmarks.movie_id"}}},
{$unwind : "$movie_ids"},
{$lookup: {from: "movie_comments", localField: "movie_ids",foreignField: "movie_id",as: "comments"}},
{$unwind : "$comments"},
{$group: {_id: '$_id', comments : {$push : "$comments"}}}])
样本输出
{
"_id": ObjectId("5834ecf7432d92675bde9d83"),
"comments": [{
"_id": ObjectId("583d96d7e35f6e9c53c9e894"),
"movie_id": "dallas00",
"comment": "what a great movie."
}, {
"_id": ObjectId("583d96d7e35f6e9c53c9e895"),
"movie_id": "dallas00",
"comment": "awesome movie."
}]
}
更新:
db.user_movies.aggregate([
{$match : {mobile_no : mobile_no}},
{$unwind: {path: "$movies", includeArrayIndex: "moviePosition"}},
{$sort : {moviePosition:1}},
{$lookup: {from: "movies",localField: "movies",foreignField: "movie_id",as: "bmarks"}},
{$unwind :"$bmarks"},
{$group : {_id : "$_id", movies : {$push : {movie_name:"$bmarks.movie_name", movie_id:"$bmarks.movie_id"} }}},
{$unwind : "$movies"},
{$lookup: {from: "movie_comments", localField: "movies.movie_id",foreignField: "movie_id",as: "comments"}},
{$unwind : "$comments"},
{$group: {_id: "$movies.movie_id", movie_name: {$first:"$movies.movie_name"}, comments : {$push : {comment:"$comments.comment", time_posted:"$comments.time_posted"}}}},
{$sort : {time_posted:-1}},
{$project:{_id:0, movie_id:"$_id", movie_name:1, comments: "$comments.comment"}}
]).pretty();
这篇关于在mongodb中执行三个集合的联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
