CODEWITHSUNDEEP
mongodbmongoosecollections
Kevin H.
Kevin H.

Reputation: 742

MongoDB find document based on existing reference in other collection

I have a situation were i got the following database-structure Collection "User":

[
    { _id: ObjectId("507f1f77bcf86c0000000001"), name: "Mike", status: "ACTIVE", verified: true },
    { _id: ObjectId("507f1f77bcf86c0000000002"), name: "Ben", status: "INACTIVE", verified: true },
    { _id: ObjectId("507f1f77bcf86c0000000003"), name: "Anastasia", status: "ACTIVE", verified: true }
]

Collection "Reports"

[
    { userRef: ObjectId("507f1f77bcf86c0000000001"), reportVerified: true },
    { userRef: ObjectId("507f1f77bcf86c0000000003"), reportVerified: false },
]

As you can see I have a collection with all of my users and a different collection called "Report" were entries references to a user and have a separated flag-field called "reportVerified". Now I want to find all entries from the "User"-collection which have specific properties in the "User"-collection but are also references with a specific property in the "Report"-collection.

Example: I want to find all users which have User-Collection.status "ACTIVE" and have a reference in the "Report"-Table with "reportVerified" set true. This should match only "Mike" in my case. Having the properties of the "Report"-collection in the "User"-collection directly is not an option for me.

The situation would be quite easy if i only got find-criterias either in the "User"-collection (simple find) or in the "Report"-collection (using populate) but I need a combination of both.

Upvotes: 0

Views: 46

Answers (1)

MohamadrezaRahimianGolkhandani
MohamadrezaRahimianGolkhandani

Reputation: 848

The best way would be using aggregate. First you need to use lookup for adding user object to the report object. for example

mongoose.db(dbName).collection(cName).aggregate([
{
  $match :{} // your match condition for report
},
{
   $lookup:
         {
           from: "user-collection-name",
           let: { user_id: "$_id", user_conditon: "$status" },
           pipeline: [
              { $match:
                 { $expr:
                    { $and:
                       [
                         { $eq: [ "$userRef",  "$$user_id" ] }, // for joining collections
                         { $eq: [ conditionInput,  "$$status" ] }, // for querying on user collection
                       ]
                    }
                 }
              }
           ],
           as: "user"
         }
}
])

Upvotes: 1

Related Questions