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:
- Search users of country_name by using mongoose find with a field filter and populate the
profile.institution
andprofile.videoURL
fields- Filter with normal javascript functions on the returned array of users by
user.profile.videoURL
wherevideoURL
is notundefined
ornull
- Create an array of the list of institutions of those users by using
user.profile.institution
- Filter again in the created array with the
institution_search_pattern
provided by the user by using a fuzzy search module- 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): Filtering institutions by a pattern means:
.
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"
}