CODEWITHSUNDEEP
mongodbjoinmongodb-query
FireVulcain
FireVulcain

Reputation: 43

Converting this SQL query to Mongodb query

I have come up with a SQL query since i am more familiar with it, but i need help converting this query into a Mongodb query.

So, I have 4 collections :

Post


{
    title: String,
    content: String,
    sub_id: String,
}

User


{
    user_id: String,
    user_name: String,
}

Sub


{
    sub_id: String,
    sub_name: String,
}

Follow


{
    sub_id: String,
    user_id: String,
}

The idea is to retrieve all posts that come from subs that the user is following.

And here is the SQL query that seems to fit the idea:

SELECT Post.* 
FROM Post
JOIN Subs ON Post.sub_id = Subs.sub_id
JOIN Follow ON Post.sub_id = Follow.sub_id
JOIN User ON Follow.user_id = User.user_id
WHERE User.user_id = "user id here"

I should probably use the aggregate $lookup but i have never used it and i am quite lost with the process.

Upvotes: 1

Views: 214

Answers (1)

Dheemanth Bhat
Dheemanth Bhat

Reputation: 4452

Try this:

let userId = "user1";

db.Post.aggregate([
    {
        $lookup: {
            from: "Sub",
            localField: "sub_id",
            foreignField: "sub_id",
            as: "Sub"
        }
    },
    { $unwind: "$Sub" },
    {
        $lookup: {
            from: "Follow",
            localField: "sub_id",
            foreignField: "sub_id",
            as: "Follow"
        }
    },
    { $unwind: "$Follow" },
    {
        $lookup: {
            from: "User",
            let: { user_id: "$Follow.user_id" },
            pipeline: [
                {
                    $match: {
                        user_id: userId,
                        $expr: { $eq: ["$user_id", "$$user_id"] }
                    }
                }
            ],
            as: "User"
        }
    },
    { $unwind: "$User" }
])

Upvotes: 1

Related Questions