Friday, 21 August 2020

Mongoose Aggregate Query For Getting Data From 4 Collection By Id and Group By Action

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.



from Mongoose Aggregate Query For Getting Data From 4 Collection By Id and Group By Action

No comments:

Post a Comment