Search code examples
databasemongodbmongooseassociationsmern

Mongoose: How to filter based on both a model field and associated model fields at the same time?


I have model User:

const UserSchema = new Schema({
  profile: {
    type: Schema.Types.ObjectId,
    ref: "profiles",
  },
  country: {
    type: String,
    required: true,
  },
});

module.exports = User = mongoose.model("users", UserSchema);

I have model Profile:

const ProfileSchema = new Schema({
  user: {
    type: Schema.Types.ObjectId,
    ref: "users",
  },
  institution: {
    type: Schema.Types.ObjectId,
    ref: "institutions",
  },
  videoURL: {
    type: String,
  },
});

As you may have noticed the two models are associated with each other by referencing each other in profile and user field respectively.

I am trying to write a function that would get me the list of institutions of users of a certain country who uploaded a video and then filter that list with a institution_search_pattern:

const getListOfInstitutionsOfUsersWhoUploadedVideosByCountry = function getListOfInstitutionsOfUsersOfWhoUploadedVideosByCountry(
  country_name,
  institution_search_pattern
)

However, this seems like an expensive operation I will have to:

  1. Search users of country_name by using mongoose find with a field filter and populate the profile.institution and profile.videoURL fields
  2. Filter with normal javascript functions on the returned array of users by user.profile.videoURL where videoURL is not undefined or null
  3. Create an array of the list of institutions of those users by using user.profile.institution
  4. Filter again in the created array with the institution_search_pattern provided by the user by using a fuzzy search module
  5. Send back in response the filtered institutions list

Is there a way to perform all of this with mongoose queries without resorting to filtering with javascript functions or modules?
For example, can I use a filter on the User model based on a Profile Model field?
In other words, in one single query:

  1. Filter User.country with a country_name
  2. Filter by Profile.videoURL where videoURL is not undefined or null
  3. Filter by Profile.institution with a pattern (1)

(1): Filtering institutions by a pattern means:

  1. The user sends a string search_pattern
  2. I use that string to perform a fuzzy search on the institution field.

.


Solution

  • UPDATE based on comments: "Removed all institutions related code."

    const country_name = "India";
    
    const users = await UserSchema.aggregate([
        {
            $match: { country: country_name }
        },
        {
            $lookup: {
                from: "profiles",
                let: { profiles_id: "$profile" },
                pipeline: [
                    {
                        $match: {
                            videoURL: { $nin: [undefined, null] },
                            $expr: { $eq: ["$_id", "$$profiles_id"] }
                        }
                    }
                ],
                as: "profiles"
            }
        },
        { $unwind: "$profiles" }
    ]);
    

    Why are you maintaining users reference in profiles? Its redundant. Instead have a reference to profiles only in users collection. All the tasks that you mentioned can be performed in single query. Check this query (change it precisely to your requirement):

    const country_name = "India";
    const institution_pattern = /^Insti/;
    
    const users = await UserSchema.aggregate([
        {
            $match: { country: country_name }
        },
        {
            $lookup: {
                from: "profiles",
                let: { profiles_id: "$profile" },
                pipeline: [
                    {
                        $match: {
                            videoURL: { $nin: [undefined, null] },
                            $expr: { $eq: ["$_id", "$$profiles_id"] }
                        }
                    },
                    {
                        $lookup: {
                            from: "institutions",
                            localField: "institution",
                            foreignField: "_id",
                            as: "institution"
                        }
                    },
                    { $unwind: "$institution" }
                ],
                as: "profiles"
            }
        },
        { $unwind: "$profiles" },
        {
            $match: {
                "profiles.institution.name": {
                    $regex: institution_pattern,
                    $options: "i"
                }
            }
        }
    ]);
    

    Output

    {
        "_id" : ObjectId("604cb4c36b2dcb17e8b152b8"),
        "profile" : ObjectId("604cb4b16b2dcb17e8b152b5"),
        "country" : "India",
        "profiles" : {
            "_id" : ObjectId("604cb4b16b2dcb17e8b152b5"),
            "institution" : {
                "_id" : ObjectId("604cb49a6b2dcb17e8b152b2"),
                "name" : "Institute 1"
            },
            "videoURL" : "http://abc1.xyz"
        }
    }
    

    Test data:

    usres collection:

    /* 1 createdAt:3/13/2021, 6:19:07 PM*/
    {
        "_id" : ObjectId("604cb4c36b2dcb17e8b152b8"),
        "profile" : ObjectId("604cb4b16b2dcb17e8b152b5"),
        "country" : "India"
    },
    
    /* 2 createdAt:3/13/2021, 6:19:07 PM*/
    {
        "_id" : ObjectId("604cb4c36b2dcb17e8b152b9"),
        "profile" : ObjectId("604cb4b16b2dcb17e8b152b6"),
        "country" : "India"
    },
    
    /* 3 createdAt:3/13/2021, 6:19:07 PM*/
    {
        "_id" : ObjectId("604cb4c36b2dcb17e8b152ba"),
        "profile" : ObjectId("604cb4b16b2dcb17e8b152b7"),
        "country" : "U.S"
    }
    

    profiles collection

    /* 1 createdAt:3/13/2021, 6:18:49 PM*/
    {
        "_id" : ObjectId("604cb4b16b2dcb17e8b152b5"),
        "institution" : ObjectId("604cb49a6b2dcb17e8b152b2"),
        "videoURL" : "http://abc1.xyz"
    },
    
    /* 2 createdAt:3/13/2021, 6:18:49 PM*/
    {
        "_id" : ObjectId("604cb4b16b2dcb17e8b152b6"),
        "institution" : ObjectId("604cb49a6b2dcb17e8b152b3")
    },
    
    /* 3 createdAt:3/13/2021, 6:18:49 PM*/
    {
        "_id" : ObjectId("604cb4b16b2dcb17e8b152b7"),
        "institution" : ObjectId("604cb49a6b2dcb17e8b152b4"),
        "videoURL" : "http://abc3.xyz"
    }
    

    institutions collection:

    /* 1 createdAt:3/13/2021, 6:18:26 PM*/
    {
        "_id" : ObjectId("604cb49a6b2dcb17e8b152b2"),
        "name" : "Institute 1"
    },
    
    /* 2 createdAt:3/13/2021, 6:18:26 PM*/
    {
        "_id" : ObjectId("604cb49a6b2dcb17e8b152b3"),
        "name" : "Institute 2"
    },
    
    /* 3 createdAt:3/13/2021, 6:18:26 PM*/
    {
        "_id" : ObjectId("604cb49a6b2dcb17e8b152b4"),
        "name" : "Institute 3"
    }