CouchDB: Querying Many-To-Many "Relationship"

Question

Given I have the following products:

{
   "_id": "2666df80782596200fca49557d757870",
   "_rev": "3-99382057f6c484526835f1042753ccf2",
   "type": "product",
   "name": "Shirt",
   "hersteller": "oska",
   "price": 11.15
}

{
   "_id": "2666df80782596200fca49557d758e8c",
   "_rev": "1-01cc88e69e5ff30f0d011fdf61fbedbc",
   "type": "product",
   "name": "Pullover",
   "hersteller": "acme",
   "price": 7.58
}

And an order

{
   "_id": "2666df80782596200fca49557d758228",
   "_rev": "2-0b4b3a8605893b60c962b8ae78f0b775",
   "type": "order",
   "orderDate": "01.01.2012",
   "positions": [
       "2666df80782596200fca49557d757870",
       "2666df80782596200fca49557d758e8c"
   ]
}

I want to query all orders with _id, orderDate and sum of all positions prices. How do I approach this?

Example:

[
 { "_id": "2666df80782596200fca49557d758228", "orderDate": "01.01.2012", "total": 18.73 }
]

Edit:
This example is made up and I know that a order's total shouldn't change the product's prices change for historical orders. It's bad example for something where a true "join" would be necessary. Just imagine I gotta change the totals when a product's price changes. Hence, I don't want to simply denormalize the totals field. It's feasible to "fix" the totals in the orders when a product changes. However, when I have a lot of different document types refering to the product, I need to change them type-by-type with some function. Sean's answer put the idea into my head that I could fix denormalized fields with a changes listener, but I'd need one listener for every time some doc type is related to another - sounds like a lot of work.

See http://stackoverflow.com/questions/3033443/best-way-to-do-one-to-many-join-in-couchdb — Ryan Ramage, Mar 28 '12 at 15:57
Hey Ryan, thanks for the comment. I knew this before, I think I've read any SO thread on joining :-) The one you linked is about "one-to-many joins", and I'm asking for "many-to-many" join. I know that I can ?include_docs=true in current couchdb versions, which will effectiveley fetch the related products for an order, but I can't reduce on the results to calculate the total. I don't see a reason why reduce wouldn't work here. — Paul Schyska, Mar 28 '12 at 16:56
OK, reading http://stackoverflow.com/questions/3033443/best-way-to-do-one-to-many-join-in-couchdb again implies it's not an "one-to-many join" that's being discussed. I think what I was missing was the show function here. I don't understand why I can't use reduce still :-) — Paul Schyska, Mar 28 '12 at 17:09

Sean Summers · Accepted Answer · 2012-03-29T03:08:20.463

I believe the best way would be to have a _changes listener that updates the order documents with the price value of each product in postions when the record becomes historical (ie, read only). It just seems wrong that a price can change for a past order, and you can't print a correct report/invoice out without going through down the rabbit hole price date ranges....

To solve the problem as you stated it, if you have some client side code that can work with JSON, the trick pointed to under Linked Documents may help. Given a map function like:

function(doc) {
 if(doc.type == 'order') {
  for(var position in doc.positions) emit(doc._id,{_id: doc.positions[position]});
 }
}

you can use ?include_docs=true&key="2666df80782596200fca49557d758228" to return your example order with line items including price.

Unfortunately, you can't use include_docs with a reduce, so this might be a good starting point for a _changes daemon like I mentioned to create an order summary document that looks like what you want for a final answer...

[edit to add solution] Well, I've found a way, but it's not pretty.

Given a design document with this view map function:

function(doc) {
 if(doc.type == 'order') {
  for(var p in doc.positions) emit(doc.orderDate, {_id: doc.positions[p]});
 }
}

and this list function:

function(head, req) {
 var count = 0;
 var dates = {};
 var totals = {};

 while(r = getRow()) {
  if(!(r.id in dates)) {
   count += 1;
   dates[r.id] = r.key;
   totals[r.id] = 0; 
  }
  totals[r.id] += r.doc.price;
 }
 start({'headers': {'Content-Type': 'application/json'}});
 send('[\n');
 for(var order in dates) {
  count -= 1;
  send(JSON.stringify({'_id': order, 'orderDate': dates[order], 'total': totals[order]}));
  send((count > 0)?',\n':'\n');
 }
 send(']\n');
}

Your desired result is at

/db/_design/[Design Doc Name]/_list/[List Function Name]/[Map Function Name]?include_docs=true

BTW, in this case, the key values are the orderDate...

Are you sure that your emit will get those product ids? Because you have an if statement narrowing down your range to orders, and then you try to emit the ids out of this circle. — anvarik, Mar 28 '12 at 23:18
Thanks for your detailed answer! I knew someone was going to say the order's prices will becoming historical and read-only :-) This example was made up and was a bad example for something where a true "join" would be necessary. However, a _changes listener is maybe a good solution for denormalizing - when the original doc changes, it could go through all docs where I denormalized a value and change them too. The linked documents solution looks good - I played around last night after reading http://goo.gl/838Zj and got something similar, but didn't get to post here. gonna try your code later — Paul Schyska, Mar 29 '12 at 07:18
OK, I can confirm that it's a solution. It's not pretty that the list function will get called for every request. If we found a solution that works with reduce, the result ("totals") would be stored in the b-tree. — Paul Schyska, Mar 29 '12 at 09:19
If the order doc has prices copied to the positions array, reduce would work perfect, and no linked document performance hit either. — Sean Summers, Mar 29 '12 at 14:32
And the list function gets called for every key, not for every order (ie, ?startkey="01.01.2012"&endkey="02.01.2012" would work in one shot). The getRow() iterator is where the looping happens, not the list function. — Sean Summers, Mar 29 '12 at 14:35

CouchDB: Querying Many-To-Many "Relationship"

1 Answers1