Query a Many-to-Many relation in MongoDB

Question

I am reading MongoDB in Action and when talking about querying many-to-many relationships in a Document, I'm having difficulty understanding how he wrote his example query (using the Ruby driver).

The query is finding all products in a specific category, where there is a products and category collection. The author says "To query for all products in the Gardening Tool category, the code is simple:

db.products.find({category_ids => category['id']})

A PRODUCT doc is like this:

doc =
{ _id: new ObjectId("4c4b1476238d3b4dd5003981"),
  slug: "wheel-barrow-9092",
  sku: "9092",
  name: "Extra Large Wheel Barrow",
  description: "Heavy duty wheel barrow...",

  details: {
    weight: 47,
    weight_units: "lbs",
    model_num: 4039283402,
    manufacturer: "Acme",
    color: "Green"
  },

  category_ids: [new ObjectId("6a5b1476238d3b4dd5000048"),
                    new ObjectId("6a5b1476238d3b4dd5000049")],

  main_cat_id: new ObjectId("6a5b1476238d3b4dd5000048"),

  tags: ["tools", "gardening", "soil"],

}

And a CATEGORY doc is like this:

doc =
{  _id: new ObjectId("6a5b1476238d3b4dd5000048"),
   slug: "gardening-tools",
   ancestors: [{ name: "Home",
                 _id: new ObjectId("8b87fb1476238d3b4dd500003"),
                 slug: "home"
                },

                { name: "Outdoors",
                 _id:  new ObjectId("9a9fb1476238d3b4dd5000001"),
                 slug: "outdoors"
               }
   ],

   parent_id: new ObjectId("9a9fb1476238d3b4dd5000001"),

   name: "Gardening Tools",
   description: "Gardening gadgets galore!",
}

Can someone please explain it a little more to me? I still can't understand how he wrote that query :(

Thanks all.

score 3 · Accepted Answer · answered May 28 '12 at 21:48

The query is searching the products collection for all products with a value of category['id'] in the field category_ids

When you search a field that contains an array for a specific value, MongoDB automatically enumerates each value in that array searching for matches.

To construct the query, you must first notice that the category collection defines your category hierarchy, and that each category has a unique ID (stored, as is usual in MongoDB, in the _id field)

You must also notice that the product collection has a field that stores a list of category ids, category_ids, that reference the unique ids of the category collection.

Therefore, to find all products in a particular category, you search the category_ids field of the product collection for the unique ID of the category you're interested in, which you get from the category collection.

If I were to write a query for the Mongo javascript based shell interpreter, mongothat find products in the Gardening Tools category, I would do the following:

Look up the ID of the Gardening Tools category (which, as noted before, is stored in the _id field of the category collection)
- In this case, the value in your example is ObjectId("6a5b1476238d3b4dd5000048")
Insert the value into a query that searches through the category_ids field of the product collection
- This is the query that you give in your question, which for the mongo shell I would write as: db.products.find({category_ids : new ObjectId("6a5b1476238d3b4dd5000048")})

I hope that's clearer than the original explanation!

(As an aside: I'm not quite sure what language your query is written in, is it perhaps PHP? In any case, javascript seems to be the language of choice for examples in the MongoDB docs because the MongoDB server installs the mongo command line interpreter alongside the server itself, so everyone has access to it)

Thanks for detailed explanation. Mongodb in Action book is using Ruby code for language examples like this. — Bohn, May 28 '12 at 22:03
So in his example 'id' has a special meaning and must be passed as is? That's where I get confused. — Bohn, May 28 '12 at 22:09
Without being able to see the book, it looks like `category_id` might be an array/variable which the author has filled with the id of the category they want (step 1 in my answer above). I don't know Ruby, so it's hard to know how to interpret the code. Thanks for the accept :) — chrisbunney, May 29 '12 at 08:31

Query a Many-to-Many relation in MongoDB

1 Answers1