Mongoose WHERE OR LIKE syntax on referenced schemas

Question

I have the following 2 schemas (Clinic and User):

const ClinicSchema = new Schema({
name: {
  type: String,
  unique: true,
  required: true
},
createdBy: {
  type: Schema.Types.ObjectId,
  ref: 'user'
},
createdAt: Date,
updatedBy: {
  type: Schema.Types.ObjectId,
  ref: 'user'
},
updatedAt: Date

});

And Here is the user Schema

const UserModelSchema = new Schema({
email: {
  type: String,
  required: true,
},
password: {
  type: String,
  required: true,
},
firstName: {
  type: String,
  required: true,
},
lastName: {
  type: String,
  required: true,
},
roles: {
  type: [String],
  required: true,
}

});

I want to write a query that will search a string that is contained in the clinic name OR createdBy(user) name OR createdBy(user) last name, and return all the clinics where either the clinic name matches part of the search string OR the created by name matches part of the search string OR the created by last name matches part of the search string here is the pseudo SQL alternative of what I am trying to explain:

SELECT * FROM clinics
JOIN users on clinics.createdBy = users.id
WHERE clinics.name LIKE '%STRING%'
OR users.firstname LIKE '%STRING%'
OR users.lastname LIKE '%STRING%'

I have been searching for this solution for the past 2 days and can't seem to be able to figure it out, more specifically I am trying to add the WHERE OR functionality to the following query:

const clinicsQuery = Clinic.find({
  name: new RegExp(req.query.searchTerm, 'i')
});

....

const clinicsList = await clinicsQuery
  .limit(limit)
  .skip(skip)
  .populate('createdBy', ['firstName', 'lastName']);

Longer explanations of this on [Querying after populate in Mongoose](https://stackoverflow.com/questions/11303294/querying-after-populate-in-mongoose). But in short, you have a very relational design here for a non-relational database engine, and this is not a good thing. You really should start looking at your schema for places where you can avoid implementing "joins" and "embed" the data instead. Also "open" regular expressions which are not anchored to the "start of the string" are a really bad idea, as they need to scan "everything". — Neil Lunn, Apr 15 '18 at 21:30

Julien TASSIN · Accepted Answer · 2018-04-15T21:31:54.127

1

It can be performed with a $lookup

const clinicsQuery = Clinic.aggregate([{
   $lookup:
     {
       from: 'user',
       localField: 'createdBy',
       foreignField: '_id',
       as: 'user'
     }}, 
     { $unwind: "$user"}
     {
       $match: {
         $or: [
          {"user.firstname":  new RegExp(req.query.searchTerm, 'i')}, 
          {"user.lastname":  new RegExp(req.query.searchTerm, 'i')}, 
          { name:  new RegExp(req.query.searchTerm, 'i')}
        ]
       }
     },
     {$limit: limit}
]);

User will be in "user" field in the result. But you won't have a mongoose ready object :/

edited Apr 15 '18 at 21:31

answered Apr 15 '18 at 21:08

Julien TASSIN

5,004
1
25
40

That's not quite correct. `$lookup` returns an "array", so no not "everything" in that array matches the conditions in the following `$match`, it only means "something" in the array matched, and that's not the same as the SQL example given. You would actually `$unwind` directly after the lookup. In fact it's a [**pattern** you should be aware of](https://docs.mongodb.com/manual/core/aggregation-pipeline-optimization/#lookup-unwind-coalescence) `$lookup + $unwind + $match` and then "optionally" using `$group` again to rebuild the array is the most efficient solution. – Neil Lunn Apr 15 '18 at 21:13
Also typo's. Missing a set of braces `{}` around the `$lookup` stage. – Neil Lunn Apr 15 '18 at 21:14
well seen the typo, thx, it's corrected. You're definitely right too about the `$unwind`, I'm adding it it right now. – Julien TASSIN Apr 15 '18 at 21:25

Mongoose WHERE OR LIKE syntax on referenced schemas

1 Answers1