CODEWITHSUNDEEP
node.jsmongodbmongoosemongodb-queryaggregation-framework
aitchkhan
aitchkhan

Reputation: 1952

Query MongoDb to get counts from collection on array of reference id

i have total 3 collections: 1. users 2. posts 3. actions

My collections look like these:

users:

{ 
    "_id" : ObjectId("57ee65fef5a0c032877725db"), 
    "fbId" : "EAAXZA4sZCZBKmgBAKe0JpJPrp7utWME6xbHT9yFD", 
    "name" : "Aftab", 
    "email" : "[email protected]", 
    "gender" : "male", 
    "__v" : NumberInt(0), 
    "updatedAt" : ISODate("2016-10-10T05:11:35.344+0000"), 
    "score" : NumberInt(90)
}

actions: 

{ 
    "_id" : ObjectId("57f7a0ba3a603627658afdd3"), 
    "updatedAt" : ISODate("2016-10-07T13:18:50.815+0000"), 
    "createdAt" : ISODate("2016-10-07T13:18:50.815+0000"), 
    "userId" : ObjectId("57ee65fef5a0c032877725db"), 
    "postId" : ObjectId("57f4b5e98899081203883a1b"), 
    "type" : "like"
}
{ 
    "_id" : ObjectId("57f7a0ba3a603627658afdd4"), 
    "updatedAt" : ISODate("2016-10-07T13:18:50.815+0000"), 
    "createdAt" : ISODate("2016-10-07T13:18:50.815+0000"), 
    "userId" : ObjectId("57ee65fef5a0c032877725db"), 
    "postId" : ObjectId("57f4b5d58899081203883a1a"), 
    "type" : "dismiss"
}

posts:

{ 
    "_id" : ObjectId("57f24593e272b5199e9351b9"), 
    "imgFileLocation" : "http://xxxx/buybye-platform/uploads/image-1475495315229", 
    "description" : "cool cool", 
    "title" : "Bad Image ", 
    "userId" : ObjectId("57f21e3d0b787d0f7ad76dd0"), 
    "__v" : NumberInt(0)
}
{ 
    "_id" : ObjectId("57f4b5d58899081203883a1a"), 
    "imgFileLocation" : "http://xxx/buybye-platform/uploads/image-1475655125125", 
    "description" : "cool & cool", 
    "title" : "Good Image", 
    "userId" : ObjectId("57f21e3d0b787d0f7ad76dd0"), 
    "__v" : NumberInt(0)
}

user can create posts, and other users can perform actions on those posts

posts collection has a reference of userId and actions collection has ref of userId(who performed that action), postId(on which post) and action-type(like/dislike/dismiss)

I need to query to get all actions performed on a specific user posts

I have able to get all posts against a user, which is pretty straight-forward and is an array. now I need to get all actions performed on every single post of this posts array.

Upvotes: 1

Views: 1573

Answers (1)

vladzam
vladzam

Reputation: 5908

If you want a solution that leverages the aggregation framework, you can use the $lookup stage that was introduced starting with MongoDB v3.2.

For example, if you want to return a result set containing the details of a post and an array of all the actions performed on that particular post, you can run the following aggregation query:

/*
 * QUERY #1
 */
db.posts.aggregate([
    {
        $lookup: {
            from: 'actions',
            localField: '_id',
            foreignField: 'postId',
            as: 'post_actions'
        }
    }
]);

/*
 * RESULT SET #1
 */
{
    "_id" : ObjectId("57ff4512a134e614a7178c1d"),
    "imgFileLocation" : "http://xxxx/buybye-platform/uploads/image-1475495315229",
    "description" : "cool cool",
    "title" : "Bad Image ",
    "userId" : ObjectId("57f21e3d0b787d0f7ad76dd0"),
    "__v" : 0,
    "post_actions" : [
        {
            "_id" : ObjectId("57ff4563a134e614a7178c1e"),
            "updatedAt" : ISODate("2016-10-07T13:18:50.815Z"),
            "createdAt" : ISODate("2016-10-07T13:18:50.815Z"),
            "userId" : ObjectId("57ee65fef5a0c032877725db"),
            "postId" : ObjectId("57ff4512a134e614a7178c1d"),
            "type" : "like"
        },
        {
            "_id" : ObjectId("57ff4564a134e614a7178c1f"),
            "updatedAt" : ISODate("2016-10-07T13:18:50.815Z"),
            "createdAt" : ISODate("2016-10-07T13:18:50.815Z"),
            "userId" : ObjectId("57ee65fef5a0c032877725db"),
            "postId" : ObjectId("57ff4512a134e614a7178c1d"),
            "type" : "share"
        }
    ]
}

Otherwise, if you want to only retrieve the actions for a specific array of posts, you can add a $match stage in the aggregation pipeline:

const postIdsArray = [
    ObjectId("57ff4512a134e614a7178c1d"),
    ObjectId("57ee65fef5a0c032877725db")
];

/*
 * QUERY #2
 */
db.posts.aggregate([
    {
        $match: {
            _id: {
                $in: postIdsArray
            }
        }
    },
    {
        $lookup: {
            from: 'actions',
            localField: '_id',
            foreignField: 'postId',
            as: 'post_actions'
        }
    }
]);

Furthermore, if you would want to only retrieve the total count of actions performed on a post, you can add an $unwind stage and then $group all the results:

/*
 * QUERY #3
 */
db.posts.aggregate([
    {
        $lookup: {
            from: 'actions',
            localField: '_id',
            foreignField: 'postId',
            as: 'post_actions'
        }
    },
    {
        $unwind: '$post_actions'
    },
    {
        $group: {
            _id: '$_id',
            posts: { $sum: 1 }
        }
    }
]);

/*
 * RESULT SET #3
 */
{ "_id" : ObjectId("57ff4512a134e614a7178c1d"), "posts" : 2 }

UPDATE #1

If you want to retrieve only the actions that are of a certain type (e.g.: like, share etc.), you can add an additional $match stage in your aggregation pipeline, after you $unwind the post_actions array retrieved in the $lookup stage.

For example, the first query would become:

/*
 * UPDATED QUERY #1
 */
db.posts.aggregate([
    {
        $lookup: {
            from: 'actions',
            localField: '_id',
            foreignField: 'postId',
            as: 'post_actions'
        }
    },
    {
        $unwind: '$post_actions'
    },
    {
        $match: {
            "post_actions.type": 'like'
        }  
    }
]);

The second query would become:

const postIdsArray = [
    ObjectId("57ff4512a134e614a7178c1d"),
    ObjectId("57ee65fef5a0c032877725db")
];

/*
 * UPDATED QUERY #2
 */
db.posts.aggregate([
    {
        $match: {
            _id: {
                $in: postIdsArray
            }
        }
    },
    {
        $lookup: {
            from: 'actions',
            localField: '_id',
            foreignField: 'postId',
            as: 'post_actions'
        }
    },
    {
        $unwind: '$post_actions'
    },
    {
        $match: {
            "post_actions.type": 'like'
        }
    }
]);

The third query would become:

/*
 * UPDATED QUERY #3
 */
db.posts.aggregate([
    {
        $lookup: {
            from: 'actions',
            localField: '_id',
            foreignField: 'postId',
            as: 'post_actions'
        }
    },
    {
        $unwind: '$post_actions'
    },
    {
        $match: {
            "post_actions.type": 'like'
        }
    },
    {
        $group: {
            _id: '$_id',
            posts: { $sum: 1 }
        }
    }
]);

Upvotes: 3

Related Questions