问题描述
我有 3 个收藏
const userSchema = new mongoose.Schema ({用户名 : {类型:字符串,修剪:真实},名称 : {类型:字符串,修剪:真实},头像:{类型:字符串}最后一次露面: {类型:日期,默认值:Date.now},地位: {类型:布尔值,默认值:真}})const hsVideoSchema = new mongoose.Schema ({名称 : {类型:字符串,修剪:真实,要求:真},网址:{类型:字符串,修剪:真实,要求:真},上传者:{类型:mongoose.Schema.Types.ObjectId,参考:'用户'},地位: {类型:布尔值,默认值:真}})const fsVideoSchema = new mongoose.Schema ({名称 : {类型:字符串,修剪:真实,要求:真},网址:{类型:字符串,修剪:真实,要求:真},上传者:{类型:mongoose.Schema.Types.ObjectId,参考:'用户'},地位: {类型:布尔值,默认值:真}})
现在,为了保留用户的操作历史,我创建了如下的历史模型:
const historySchema = new mongoose.Schema ({用户身份 : {类型:mongoose.Schema.Types.ObjectId,参考:'用户'},hs_videoId :{类型:mongoose.Schema.Types.ObjectId,ref: 'HsVideo',默认值:空},fs_videoId :{类型:mongoose.Schema.Types.ObjectId,ref: 'FsVideo',默认值:空},行动 : {类型:字符串,修剪:真实,枚举:['下载','查看','喜欢','报告']}})
因此,当用户执行任何操作时,我将在历史记录集合中添加新记录.一次,在 2 个字段 (hs_videId & fs_videoId) 中,一个字段将为 null,另一个字段将具有 ref 文档的 id.在历史集合中,可以有相同的 hs_videId/fs_videId 具有不同的动作('downloaded'、'viewed'、'liked'、'reported').
我正在寻找通过传递 user_id 来获取用户历史记录的查询,并使用 2 个子数组获取所有视频历史记录数组:HsVideos 和 FsVideos.两个子数组都应该有动作的子数组,它将包含视频的完整详细信息(名称,url,uploadedBy(UserArray),状态).
我应该写什么查询来得到想要的结果?
我已经尝试过的查询:
User.aggregate([{ $match: {_id : ObjectId('5f3a90110132e115db700201')} },{$查找:{来自:历史",如:历史",管道:[{$匹配:{user_id : ObjectId('5f3a90110132e115db700201')}}]},},]).exec(函数(错误,结果){if(err) console.log(err);返回 res.status(200).json(results);})
请帮忙!任何帮助将不胜感激.谢谢.
1
我期待以下结果:
[{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),姓名":约翰",状态":真,用户名":jony"FsVideos":[{看过:[{_id":ObjectId(5a934e000102030405000001"),网址":http://example.com/video.mp4",上传者":{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A",状态":真,用户名":A"},},{_id":ObjectId(5a934e000102030405000001"),网址":http://example.com/video.mp4",上传者":{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A",状态":真,用户名":A"},}],已下载:[{_id":ObjectId(5a934e000102030405000001"),网址":http://example.com/video.mp4",上传者":{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A",状态":真,用户名":A"},},{_id":ObjectId(5a934e000102030405000001"),网址":http://example.com/video.mp4",上传者":{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A",状态":真,用户名":A"},}],喜欢:[{_id":ObjectId(5a934e000102030405000001"),网址":http://example.com/video.mp4",上传者":{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A",状态":真,用户名":A"},},{_id":ObjectId(5a934e000102030405000001"),网址":http://example.com/video.mp4",上传者":{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A",状态":真,用户名":A"},}],报道:[{_id":ObjectId(5a934e000102030405000001"),网址":http://example.com/video.mp4",上传者":{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A",状态":真,用户名":A"},},{_id":ObjectId(5a934e000102030405000001"),网址":http://example.com/video.mp4",上传者":{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A",状态":真,用户名":A"},}],}],HsVideos":[{看过:[{_id":ObjectId(5a934e000102030405000001"),网址":http://example.com/video.mp4",上传者":{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A",状态":真,用户名":A"},},{_id":ObjectId(5a934e000102030405000001"),网址":http://example.com/video.mp4",上传者":{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A",状态":真,用户名":A"},}],已下载:[{_id":ObjectId(5a934e000102030405000001"),网址":http://example.com/video.mp4",上传者":{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A",状态":真,用户名":A"},},{_id":ObjectId(5a934e000102030405000001"),网址":http://example.com/video.mp4",上传者":{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A",状态":真,用户名":A"},}],喜欢:[{_id":ObjectId(5a934e000102030405000001"),网址":http://example.com/video.mp4",上传者":{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A",状态":真,用户名":A"},},{_id":ObjectId(5a934e000102030405000001"),网址":http://example.com/video.mp4",上传者":{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A",状态":真,用户名":A"},}],报道:[{_id":ObjectId(5a934e000102030405000001"),网址":http://example.com/video.mp4",上传者":{_id":ObjectId(5f3a90110132e115db700201"),头像":default.png",last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A",状态":真,用户名":A"},}],}]}]这完全是一个糟糕的schema结构,你真的需要更新它,因为这会导致内存使用和查询的执行时间,
您需要将查找与管道和嵌套查找结合使用,
$match
user
你的条件$lookup
withhistories
集合和结果将在HsVideos
$match
获取user_id
的$lookup
用于hsVideo
集合和结果将在hs_videoId
$match
获取hsVideo
详情$lookup
带有updatedBy
的用户集合,结果将在updatedBy
$unwind
解构结果updatedBy
将是对象$unwind
解构hs_videoId
因为它的数组和我们需要的对象$group
通过action
并将必填字段推送到v
数组$project
显示和隐藏必填字段
history
User.aggregate([{ $match: { _id: ObjectId("5f3a90110132e115db700201") } },{$查找:{来自:历史",让:{ user_id:$_id";},如:HsVideos",管道:[{ $match: { $expr: { $eq: ["$user_id", "$$user_id"] } } },{$查找:{来自:hsVideo",让:{ hs_videoId:$hs_videoId";},如:hs_videoId",管道:[{ $match: { $expr: { $eq: ["$$hs_videoId", "$_id"] } } },{$查找:{来自:用户",本地字段:uploadedBy",外国字段:_id",如:上传者"}},{ $unwind: "$uploadedBy";}]}},{ $unwind: "$hs_videoId";},{$组:{_id: "$action",v:{$推:{_id: "$_id",网址:$hs_videoId.url",名称:$hs_videoId.name",上传者:$hs_videoId.uploadedBy";}}}},{ $project: { _id: 0, k: "$_id", v: 1 } }]}},
- 我在重复上面几行的解释,重复
FsVideos
中
HsVideos
的相同流程{$查找:{来自:历史",让:{ user_id:$_id";},如:FsVideos",管道:[{ $match: { $expr: { $eq: ["$user_id", "$$user_id"] } } },{$查找:{来自:fsVideo",让:{ fs_videoId:$fs_videoId";},如:fs_videoId",管道:[{ $match: { $expr: { $eq: ["$$fs_videoId", "$_id"] } } },{$查找:{来自:用户",本地字段:uploadedBy",外国字段:_id",如:上传者"}},{ $unwind: "$uploadedBy";}]}},{ $unwind: "$fs_videoId";},{$组:{_id: "$action",v:{$推:{_id: "$_id",网址:$fs_videoId.url",名称:$fs_videoId.name",上传者:$fs_videoId.uploadedBy";}}}},{ $project: { _id: 0, k: "$_id", v: 1 } }]}},
- $addFields 将
FsVideos
和HsVideos
数组转换为对象,action
作为 key 和 vlaue
{$addFields:{FsVideos: { $arrayToObject: "$FsVideos";},HsVideos: { $arrayToObject: "$HsVideos";}}}]).exec(函数(错误,结果){if(err) console.log(err);返回 res.status(200).json(results);})
I have 3 collections
const userSchema = new mongoose.Schema ({
username : {
type : String,
trim: true
},
name : {
type : String,
trim: true
},
avatar : {
type : String
}
last_seen: {
type: Date,
default: Date.now
},
status: {
type : Boolean,
default: true
}
})
const hsVideoSchema = new mongoose.Schema ({
name : {
type : String,
trim: true,
required : true
},
url : {
type : String,
trim: true,
required : true
},
uploadedBy: {
type: mongoose.Schema.Types.ObjectId,
ref: 'User'
},
status: {
type : Boolean,
default: true
}
})
const fsVideoSchema = new mongoose.Schema ({
name : {
type : String,
trim: true,
required : true
},
url : {
type : String,
trim: true,
required : true
},
uploadedBy: {
type: mongoose.Schema.Types.ObjectId,
ref: 'User'
},
status: {
type : Boolean,
default: true
}
})
Now, to keep user's action history, i created History Model as below :
const historySchema = new mongoose.Schema ({
user_id : {
type: mongoose.Schema.Types.ObjectId,
ref: 'User'
},
hs_videoId : {
type: mongoose.Schema.Types.ObjectId,
ref: 'HsVideo',
default: null
},
fs_videoId : {
type: mongoose.Schema.Types.ObjectId,
ref: 'FsVideo',
default: null
},
action : {
type : String,
trim: true,
enum:['downloaded','viewed','liked','reported']
}
})
So, i will add new record in history collection when user will perform any action. At a time, out of 2 fields (hs_videId & fs_videoId) one field will be null and another will have id of ref document. In history collection, there can be same hs_videId/fs_videId with different action ('downloaded','viewed','liked','reported').
I am looking for query to get user's history by passing user_id and get all video history array with 2 sub arrays : HsVideos and FsVideos. Both sub array should have action's sub-array, which will have complete details of video (name, url,uploadedBy(UserArray),status).
What query i should write to get desire result ?
Query i tried already :
User.aggregate([
{ $match: {_id : ObjectId('5f3a90110132e115db700201')} },
{
$lookup: {
from: "histories",
as: "history",
pipeline: [
{
$match: {
user_id : ObjectId('5f3a90110132e115db700201')
}
}
]
},
},
]).exec(function(err, results){
if(err) console.log(err);
return res.status(200).json(results);
})
Please help ! Any help will be appreciated. Thanks.
EDIT : 1
I am expecting below result :
[
{
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "John",
"status": true,
"username": "jony"
"FsVideos": [
{
Viewed :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
},
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
Downloaded :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
},
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
Liked :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
},
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
Reported :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
},
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
}
],
"HsVideos": [
{
Viewed :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
},
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
Downloaded :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
},
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
Liked :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
},
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
Reported :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
}
]
}
]
This is totally a bad structure of schema, you really need to update it, because this will cause memory usage and execution time of the query,
You need to use lookup with pipeline and nested lookup,
$match
your conditions foruser
$lookup
withhistories
collection and result will be inHsVideos
$match
gethistories
ofuser_id
$lookup
forhsVideo
collection and result will be inhs_videoId
$match
gethsVideo
details$lookup
with user collection forupdatedBy
and result will be inupdatedBy
$unwind
deconstruct resultupdatedBy
and it will be object$unwind
deconstrucths_videoId
because its array and we need object$group
byaction
and push required fields inv
array$project
to show and hide required fields
User.aggregate([
{ $match: { _id: ObjectId("5f3a90110132e115db700201") } },
{
$lookup: {
from: "histories",
let: { user_id: "$_id" },
as: "HsVideos",
pipeline: [
{ $match: { $expr: { $eq: ["$user_id", "$$user_id"] } } },
{
$lookup: {
from: "hsVideo",
let: { hs_videoId: "$hs_videoId" },
as: "hs_videoId",
pipeline: [
{ $match: { $expr: { $eq: ["$$hs_videoId", "$_id"] } } },
{
$lookup: {
from: "user",
localField: "uploadedBy",
foreignField: "_id",
as: "uploadedBy"
}
},
{ $unwind: "$uploadedBy" }
]
}
},
{ $unwind: "$hs_videoId" },
{
$group: {
_id: "$action",
v: {
$push: {
_id: "$_id",
url: "$hs_videoId.url",
name: "$hs_videoId.name",
uploadedBy: "$hs_videoId.uploadedBy"
}
}
}
},
{ $project: { _id: 0, k: "$_id", v: 1 } }
]
}
},
- i am repeating the above lines of explanation, repeat the same flow of
HsVideos
inFsVideos
{
$lookup: {
from: "histories",
let: { user_id: "$_id" },
as: "FsVideos",
pipeline: [
{ $match: { $expr: { $eq: ["$user_id", "$$user_id"] } } },
{
$lookup: {
from: "fsVideo",
let: { fs_videoId: "$fs_videoId" },
as: "fs_videoId",
pipeline: [
{ $match: { $expr: { $eq: ["$$fs_videoId", "$_id"] } } },
{
$lookup: {
from: "user",
localField: "uploadedBy",
foreignField: "_id",
as: "uploadedBy"
}
},
{ $unwind: "$uploadedBy" }
]
}
},
{ $unwind: "$fs_videoId" },
{
$group: {
_id: "$action",
v: {
$push: {
_id: "$_id",
url: "$fs_videoId.url",
name: "$fs_videoId.name",
uploadedBy: "$fs_videoId.uploadedBy"
}
}
}
},
{ $project: { _id: 0, k: "$_id", v: 1 } }
]
}
},
- $addFields to convert
FsVideos
andHsVideos
array to object,action
as key and vlaue
{
$addFields: {
FsVideos: { $arrayToObject: "$FsVideos" },
HsVideos: { $arrayToObject: "$HsVideos" }
}
}
])
.exec(function(err, results){
if(err) console.log(err);
return res.status(200).json(results);
})
这篇关于Mongoose 聚合查询,用于从 4 个按 Id 和按操作分组的集合中获取数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!