mongoDB many to many with one query?

Question

in mysql i use JOIN and one query is no problem. what about mongo?

imagine categories and products.

products may have more categories. categories may have more product. (many to many structure) and administrator may edit categories in administration (categories must be separated)

its possible write product with categories names in one query?

i used this structure

categories {
   name:"categoryName",
         product_id:["4b5783300334000000000aa9","5783300334000000000aa943","6c6793300334001000000006"]
}


products  {
    name:"productName",
category_id:["4b5783300334000000000bb9","5783300334000000000bb943","6c6793300334001000000116"]
}

now i can simply get all product categories, and product in some category and categories alone for editation. but if i want write product with categories names i need two queries - one to get product categories id and second to get categories names from categories by that ids.

is this the right way? or this structure is unsuitable? i would like to have only one query but i dont know if its possible.

Answer 1

Yes it's the only way you have. There are no join possibility.

But you can add a lot of information in your categories or product document like :

categories {
   name:"categoryName",
         product_id:[ {"4b5783300334000000000aa9": 'productName'},{"5783300334000000000aa943":'productName2'},{"6c6793300334001000000006":'productName3'}]
}


products  {
    name:"productName",
category_id:[{"4b5783300334000000000bb9":'categoryName'},{"5783300334000000000bb943":'CategoryName2',{"6c6793300334001000000116":'categoryName3'}]
}

But you need made some callback for update all document when on is change.

Answer 2

Yep, MongoDB is specifically bad at this particular type of operation. However, it's also a matter of scope. If you have 30 million products and you want to join the 3 million Products to their Category, you'll find that it's not very quick in many Databases (even though it's one line of code).

What MongoDB requires here is some de-normalization.

Your collections will probably look like this:

categories {
  _id:"mongoid",
  name:"categoryName",
  ...
}

products  {
  _id:"mongoid",
  name:"productName",
  categories:[
    {"4b5783300334000000000bb9":'categoryName'},
    {"5783300334000000000bb943":'CategoryName2'},
    {"6c6793300334001000000116":'categoryName3'}
  ]
}

Here's how it will work (rough estimate, don't have my Mongo instance handy):

Grab products and their categories

db.products.find({"_id": {$in:[1,2,3]}, {name:1,categories:1})

Grab products with a certain category:

db.products.find({"categories.0": {$in:[x,y,z]}},{categories:1,name:1} }

Yes, it's not quite the same. And you will have to will have to update all of the product records when you change the name of a category. I know this seems weird, but this is standard for denormalization.

BTW, I'm assuming here that you're going to have lots of products and significantly less categories. This method is probably best if you have say 100 items in each category. If you have 5 items in each category, then you'll probably want to store more of the category information in each item.