CouchDB - Basic design for joins data

Just playing around with CouchDb and CouchApp, what amazing tech ! Very surprised, seams to be very powerful. After playing and reading a lot, as I'm a old relational database user, I still questioning myself about how to design some basic things.

Here is my question :

1/ I have a document of type 'user' and document of type 'item'

2/ My Couchdb contains the following documents :

{ "_id": "...", "type": "user", "email":"u1@gmail.com" ... }
{ "_id": "...", "type": "us开发者_JAVA技巧er", "email":"u2@gmail.com" ... }
{ "_id": "...", "type": "user", "email":"u3@gmail.com" ... }
{ "_id": "...", "type": "user", "email":"u4@gmail.com" ... }

{ "_id": "...", "type": "item", "title":"My title",
  created_by:"u1@gmail.com", modified_by:"u3@gmail.com" }

3/ Now I want a view or something to fetch document by type=item and _id with informations for each users (creator & modifier)

I have seen a way to emulate a simple join here : http://www.cmlenz.net/archives/2007/10/couchdb-joins

But I can't adapt it for two joins, I'am playing around with key format since few hours, testing lots things, but nothing works.

I think, I'm missing something important with CouchDb map/reduce, if someone has some help I will appreciate it.

PS : Don't answer me to insert 'user' document inside 'item' document. This not my question.

Relax, relax ... :-)

---

I think you should put _ids in created_by and modified_by:

{ "_id": "u1", "type": "user", "email":"u1@gmail.com" ... }
{ "_id": "u2", "type": "user", "email":"u2@gmail.com" ... }
{ "_id": "u3", "type": "user", "email":"u3@gmail.com" ... }
{ "_id": "u4", "type": "user", "email":"u4@gmail.com" ... }

{ "_id": "anitem", "type": "item", "title":"My title",
  created_by:"u1", modified_by:"u3" }

so you can use the following map function and query it with ?key="anitem"&include_docs=true:

function(doc) {
  if (doc.type === "item") {
    emit(doc._id, 1);
    emit(doc._id, { _id: doc.created_by });
    emit(doc._id, { _id: doc.modified_by });
  }
}

You can read Jan Lehnardt's post about it for more details.

As a side note, I generally put the type in the _id so it is easier to get unique keys, and you do not need a view if you only want to filter by type:

{ "_id": "user/username1", "email":"u1@gmail.com" ... }
{ "_id": "user/username2", "email":"u2@gmail.com" ... }
{ "_id": "user/username3", "email":"u3@gmail.com" ... }
{ "_id": "user/username4", "email":"u4@gmail.com" ... }

{ "_id": "item/itemid1", "title":"My title",
  created_by:"user/username1", modified_by:"user/username3" }

and the map function is

function(doc) {
  if (doc._id.slice(0, 4) === "item/") {
    emit(doc._id, 1);
    emit(doc._id, { _id: doc.created_by });
    emit(doc._id, { _id: doc.modified_by });
  }
}

UPDATE: Due to bug COUCHDB-1229, use of / in doc._id may cause problems. Depending on your use-case, it may be better to use another separator, e.g. : or _.

---

What do you mean here "two joins"? Is this "tableA join tableB ON ... AND ..."?

Now I want a view or something to fetch document by type=item and _id with informations for each users

This can be done without two/more joins. Anyway, my advise is to divide your data in 2 databases: items & users. The example above suits only few simple tasks. But when your data grows big (say 10K users & 100K items) it becomes very hard to process your data and it's kinda sucks that all your documents differ from each other with only one field.