So i have Cheques collection and Payees collection, Every Cheque has it's corosponding Payee ID.

What i'm trying to do is some quries on Cheques, but i need to preform the searching after populating the payee (to get the name)

    const search = req.query.search || "";
    const cheques = await Cheque
        .find({
            isCancelled: false,
            dueDate: { $gte: sinceDate, $lte: tillDate }
        })
        .select("_id serial dueDate value payee")
        .skip(page * limit)
        .limit(limit)
        .sort({ dueDate: -1, serial: 1 })
        .populate({
            path: "payee",
            select: "name"
        })

I guess what i'm tryna do is fit this somewhere in my code,

   match: {
    name: { $regex: search, $options: "i" }
   },

I have tried to put the match within the populate, but then it will still find all cheques even if they don't satisfy the population match but populate as null.

CodePudding user response：

I hate this answer and I hope someone is going to post a better one, but I've surfed the web for that with no luck. The only method I was able to find is to use the $lookup method in aggregation.

So you'll have to change your code from calling .find() to .aggregate().

It's not the sad news, it's great, stable and no problems at all. but I hated that because it's going to change some patterns you might be following in your code.

const search = req.query.search || "";
    const cheques = await Cheque
        .aggregate([
             {
               $lookup: { // similar to .populate() in mongoose
                from: 'payees', // the other collection name
                localField: 'payee', // the field referencing the other collection in the curent collection
                foreignField: '_id', // the name of the column where the cell in the current collection can be found in the other collection
                as: 'payee' // the field you want to place the db response in. this will overwrite payee id with the actual document in the response (it only writes to the response, not on the database, no worries)
             },
             { // this is where you'll place your filter object you used to place inside .find()
               $match: {
                 isCancelled: false,
                 dueDate: { $gte: sinceDate, $lte: tillDate }
                 'payee.branch': 'YOUR_FILTER', // this is how you access the actual object from the other collection after population, using the dot notation but inside a string.
               }
             },
             { // this is similar to .select()
               $project: {_id: 1, serial: 1, dueDate: 1, value: 1, payee: 1}
             },
             {
               $unwind: '$payee' // this picks the only object in the field payee: [ { payeeDoc } ] --> { payeeDoc }
             }
        ])
        .skip(page * limit)
        .limit(limit)
        .sort({ dueDate: -1, serial: 1 })

Notice how you can no longer chain .select() and .populate() on the model query the way you used to do it on .find(), because now you're using .aggregate() which returns a different class instance in mongoose.

• you can call .projcet() instead of doing it inside the aggregation array if you want to, but as far as I know, you can't use the .select() method.

My opinion-based solution to this problem is to include the payee information you need for filtering in the Cheque collection.

In my senario, this happen when I was filtering for the sake of my users-roles and permissions, so someone can not see what another one is seeing.

It's up to you, but this makes it easier later when you want to generate the reports (I assume you're working on a payment service).

CodePudding user response：

The populate() feature provided by mongoose first fetches all the Cheques with given conditions and then makes another query to payee with _ids to populate the fields you wanted. https://mongoosejs.com/docs/api.html#query_Query-populate

by putting match in populate you're filtering which cheques need to be populated but not the cheques themselves.

A simple solution for this is to filter the cheques which are populated as null and return them for your use.

If you see more queries of this sort and/or the collection is huge, it's better you add the payee name in the Cheques collection itself if that fits your purpose.