skip to Main Content

I would like to know how I can make a query to the database bringing all the results that meet the ‘store’ {name} field, but this ‘store’ is saved with ‘_id’ and I use .populate() to bring me the info

/**example: await product.find({store:{name:"specific store"}})*/

const products = await Product.find({ 'store.name' : "Ejemplo1" })
                .populate('store', 'name')

the schemes are as follows:

const storeSchema = mongoose.Schema({
   _id:"it is added automatically by mongoose",
   name: String
})
export default model('Store', storeSchema);

const productSchema = mongoose.Schema({
   store: {
     type: Schema.Types.ObjectId,
     ref: "Store"
   }
})

Basically what I want to achieve is to extract from the DB all the products with a specific ‘store’, but I still can’t do it, I appreciate any help to solve this, whether it is a reference or an example of a suitable query, thanks in advance

i tried this:

const p = await Product.find({})
  .populate({
    path: 'store',
    match: { name: { $eq: 'Store1' } },
    select: 'name -_id',
  })

but it returns the entire collection and does not do the filtering, I receive something like this:

[{
  _id:1,
  ...
  store:null,
  ...
}, {
  _id:2,
  ...
  store:{name:"Store1"},
  ...
}, {
  _id:3,
  ...
  store:{name:"Store2"},
  ...
}]

2

Answers


  1. First solution comes to mind is filtering the data manually using Array.filter() method. This is as simple as shown below:

    const products = await Product.find({}).populate({
      path: 'store',
      match: { name: { $eq: 'Store1' } },
      select: 'name -_id'
    });
    const productsFiltered = products.filter(
      product => product.store !== null
    );
    

    As you can see, this can help well for small amount of retrieved data and is easy to implement. But if you want to apply such filter on database level, then we need to change the schema of your products. Because filtering a data on lookup isn’t possible according to Mongoose documents. I quote the reasoning below:

    For example, suppose you populate() a story’s author and the author doesn’t satisfy match. Then the story’s author will be null.

    const story = await Story.
      findOne({ title: 'Casino Royale' }).
      populate({ path: 'author', name: { $ne: 'Ian Fleming' } }).
      exec();
    story.author; // `null`
    

    In general, there is no way to make populate() filter stories based on properties of the story’s author. For example, the below query won’t return any results, even though author is populated.

    const story = await Story.
      findOne({ 'author.name': 'Ian Fleming' }).
      populate('author').
      exec();
    story; // null
    

    If you want to filter stories by their author’s name, you should use denormalization.

    I would recommend you to read through the official blog post of MongoDB.

    We can apply different types of denormalizations mentioned in the blog post but I will provide a code for one of those types. First we need to change your product schema:

    const productSchema = mongoose.Schema({
       store: {
         id: Schema.Types.ObjectId,
         name: String
       }
    });
    

    Instead of defining store field as a reference to Store model, we denormalized and added both id (to make application-level join) and name (to filter) fields. After that, we can easily get and filter products with specified store name:

    const p = await Product.find({
      'store.name': 'Store1'
    }, 'name -_id');
    

    This way we will always have results filtered by their store.name field. If we want to provide store data other than id and name fields, we need to make application-level joins:

    // Map each product to retrieve only their store ids and filter out to have unique store ids
    const storeIds = p
      .map(
        product => product.store.id
      )
      .filter(
        (value, index, array) => array.indexOf(value) === index
      );
    
    // Retrieve stores with given store ids
    const stores = await Store.find({ _id: { $in: storeIds } });
    
    // Join stores with each product's store.id
    p.forEach(
      product => {
        product.store = stores.find(store => store._id.toString() == product.store.id.toString())
      }
    );
    

    Well, as you can see, there’re some advantages and disadvantages of using denormalization. When you want to update a store’s name, you also need to update all occurences in products for that store. This update can be expensive. Because of this reason, denormalization is a good option if you are going to make a lot of read operations (with store name filtering) on products and infrequently name updates on stores, then this is a good solution.

    Login or Signup to reply.
  2. As mentioned by @M.CaglarTUFAN you cannot filter the parent document based on the child document with populate. Refactoring your schemas is a good option.

    However, if you are unable to do so you can achieve your desired result with the mongodb aggregate framework using $lookup and $match like so:

    const products = await Product.aggregate([
       { 
          $lookup: { //< Lookup is like a Left Join in SQL
             from: "stores", 
             localField: "store", 
             foreignField: "_id", 
             as: "store_details" //< You are adding this field to the output
          } 
       }, 
       { 
          $match: { //< Now from all the results only give me back the store Ejemplo1
             'store_details.name': 'Ejemplo1' 
          } 
       }
    ]).exec();
    

    If your Product collection is large (millions of documents) then bear in mind this will perform the $lookup on all documents so could be inefficient. But without refactoring your schemas this the best single call to the database.

    Your second option involves 2 database queries.

    1. Get the _id of the store you want from the stores collection.
    2. Then use that result to query the products collection and populate matching records.
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search