Mongoose lookup across 3 collections using foreign key

Question

I have found a few questions that relate to this (here and here) but I have been unable to interpret the answers in a way that I can understand how to do what I need.

I have 3 collections: Organisations, Users, and Projects. Every project belongs to one user, and every user belongs to one organisation. From the user's id, I need to return all the projects that belong to the organisation that the logged-in user belongs to.

Returning the projects from the collection that belong to the user is easy, with this query:

const projects = await Project.find({ user: req.user.id }).sort({ createdAt: -1 })

Each user has an organisation id as a foreign key, and I think I need to do something with $lookup and perhaps $unwind mongo commands, but unlike with SQL queries I really struggle to understand what's going on so I can construct queries correctly.

EDIT: Using this query

const orgProjects = User.aggregate(
    [
        {
            $match: { _id: req.user.id }
        },
        {
            $project: { _id: 0, org_id: 1 }
        },
        {
            $lookup: {
                from: "users",
                localField: "organisation",
                foreignField: Organisation._id,
                as: "users_of_org"
            }
        },
        {
            $lookup: {
                from: "projects",
                localField: "users_of_org._id",
                foreignField: "user",
                as: "projects"
            }
        },
        {
            $unset: ["organisation", "users_of_org"]
        },
        {
            $unwind: "$projects"
        },
        {
            $replaceWith: "$projects"
        }
    ])

Seems to almost work, returning the following:

Aggregate {
  _pipeline: [
    { '$match': [Object] },
    { '$project': [Object] },
    { '$lookup': [Object] },
    { '$lookup': [Object] },
    { '$unset': [Array] },
    { '$unwind': '$projects' },
    { '$replaceWith': '$projects' }
  ],
  _model: Model { User },
  options: {}
}

score 2 · Answer 1 · answered Mar 14 '21 at 14:11

2

assuming your documents have a schema like this, you could do an aggregation pipeline like below with 2 $lookup stages.

db.users.aggregate(
[
    {
        $match: { _id: "user1" }
    },
    {
        $project: { _id: 0, org_id: 1 }
    },
    {
        $lookup: {
            from: "users",
            localField: "org_id",
            foreignField: "org_id",
            as: "users_of_org"
        }
    },
    {
        $lookup: {
            from: "projects",
            localField: "users_of_org._id",
            foreignField: "user_id",
            as: "projects"
        }
    },
    {
        $unset: ["org_id", "users_of_org"]
    },
    {
        $unwind: "$projects"
    },
    {
        $replaceWith: "$projects"
    }
])

answered Mar 14 '21 at 14:11

Dĵ ΝιΓΞΗΛψΚ

5,068
3
13
26

Thank you for the answer, I will give it a try - do I need to do an aggregation pipeline, can I not do it with just a query? And the bit that stands out to me that I don't understand is "$project: { _id: 0, org_id: 1 }", what is this line doing/coming from? – bevc Mar 14 '21 at 14:29
1

@bevc you need aggregation queries to do joins/lookups in mongo. the find interface can't do it. you can actually delete that $project stage and it will still work. it's just a habit i have, to only work with the actual fields/data that the subsequent stages need. there we're specifying not to include the users id and only to include the org_id of matched user. no big deal if you skip the initial projection. since it's gonna match only 1 document, memory usage won't even be a concern. but if the match stage matches many thousands of docs, then a project would make sense i suppose – Dĵ ΝιΓΞΗΛψΚ Mar 14 '21 at 14:34
1

@bevc if you have full control of the schema, storing the `org_id` on the project documents would enable you to get rid of 1 lookup stage. you could also get rid of the remaining lookup stage if you store the `org_id` in the claims/session/cookies/whatever of the logged in user so you won't have to fetch it from the db. – Dĵ ΝιΓΞΗΛψΚ Mar 14 '21 at 14:38
This almost seems to work, if I replace the first lookup with the name of the organisation id field as the localfield and 'Organisation._id' as the foreign field using my schema, I get this back: Aggregate { _pipeline: [ { '$match': [Object] }, { '$lookup': [Object] }, { '$lookup': [Object] }, { '$unset': [Array] }, { '$unwind': '$projects' }, { '$replaceWith': '$projects' } ], _model: Model { User }, options: {} } – bevc Mar 14 '21 at 15:24
1

@bevc can you add some sample data to mongoplayground and share the link here, I'll have a look. – Dĵ ΝιΓΞΗΛψΚ Mar 15 '21 at 01:40
Thank you very much for offering to look at it for me, I have added the query to the question above replacing what I think is correct for my code/schema, and I have made a mongoplayground showing how the db looks to me here https://mongoplayground.net/p/wEqv9h-867k (I'm not sure whether the 'ObjectId' makes any difference) – bevc Mar 15 '21 at 09:06
1

@bevc here you go: https://mongoplayground.net/p/rE4YDGJnMCp note: ObjectId("xyz") is not valid. only valid objectsid hex strings will work with `ObjectId()`. – Dĵ ΝιΓΞΗΛψΚ Mar 15 '21 at 09:15
Thank you very much, but when I try it in my code I still get the same object returned I'm afraid! ("Aggregate: {_pipeline....}") – bevc Mar 15 '21 at 09:37

Mongoose lookup across 3 collections using foreign key

1 Answers1