Cloudant 1 to many function

Question

I’ve just started to use Cloudant and I just can’t get my head around the map functions. I’ve been fiddling with the data below but it isn’t working out as I expected.

The relationship is, a user can have many vehicles. A vehicle belongs to 1 user. The vehicle ‘userId’ is the key of the user. There is a bit of redundancy as in user the _id and userId is the same, guess later is not required.

Anyhow, how can I find for a/every user, the vehicles which belong to it? The closest I’ve come through trial and error is a result which displays the owner of every vehicle, but I would like it the other way round, the user and the vehicles belonging to it. All the examples I’ve found use another document which ‘joins’ two or more documents, but I don’t need to do that?

Any point in the right direction appreciated - I really have no idea.

function (doc) {
 if (doc.$doctype == "vehicle")
 {
     emit(doc.userId, {_id: doc.userId});
 }
}

EDIT: Getting closer. I'm not sure exactly what I was expecting, but the result seems a bit 'messy'. Row[0] is the user document, row[n > 0] are the vehicle documents. I guess it's fine when a startkey/endkey is used, but without the results are a bit jumbled up.

function (doc) {
     if (doc.$doctype == 'user') {
        emit([doc._id, 0], doc);
     } else if (doc.$doctype == 'vehicle') {
        emit([doc.userId, 1, doc._id], doc);
     }
}

A user is described as,

{
  "_id": "user:10",
  "firstname": “firstnamehere",
  "secondname": “secondnamehere",
  "userId": "user:10",
  "$doctype": "user"
}

a vehicle is described as,

{
  "_id": "vehicle:4002”,
  “name”: “avehicle”,
  "userId": "user:10",
  "$doctype": "vehicle",
}

Answer 1

You're getting in the right direction! You already got that right with the global IDs. Having the type of the document as part of the ID in some form is a very good idea, so that you don't get confused later (all documents are in the same "pot").

Here are some minor problems with your current solution (before getting to your actual question):

• Don't emit the doc as value in emit(key, value). You can always ask for the document that belongs to a view row by querying with include_docs=true. Having the doc as view value increases the view indexes a lot. When you don't need a specific value, use emit(key, null).

• You also don't need the ID in the emit value. You'll get the ID of the document that belongs to a view row as part of the row anyway.

View Collation

Now to your problem of aggregating the vehicles with their user. You got the basic pattern right. This pattern is called view collation, you can read more about it in the CouchDB docs (ignore that it is in the "Couchapp" section).

The trick with view collation is that you return two or more types of documents, but make sure that they are sorted in a way that allows for direct grouping. Thus it is important to understand how CouchDB sorts the view result. See the collation specification for more information on that one. An important key to understanding view collation is that rows with array keys are sorted by key elements. So when two rows have the same key[0], they sort by key[1]. If that's equal as well, key[2] is considered, and so on.

Your map function frist groups users and vehicles by user ID (key[0]). Your map function then uses the fact that 0 sorts before 1 in the second element of the key, so your view will contain the following:

• user 1
• vehicle of user 1
• vehicle of user 1
• vehicle of user 1
• user 2
• user 3
• vehicle of user 3
• user 4
• etc.

As you can see, the vehicles of a user immediately follow their user. Thus you can group this result into aggregates without performing expensive sort or lookup operations.

Note that users are sorted according to their ID, and vehicles within users also according to their ID. This is because you use the IDs in the key array.

Creating Queries

Now that view isn't worth much if you can't query according to your needs. A view as you have it supports the following queries:

• Get all users with their vehicles
• Get a range of users with their vehicles
• Get a single user with its vehicles
• Get a single user without vehicles (you could also use the _all_docs view for that though)

Example query for "all users between user 1 and user 3 (inclusive) with their vehicles"

We want to query for a range, so we use startkey and endkey in the query:

startkey=["user:1", 0]
endkey=["user:3", 1, {}]

Note the use of {} as sentinel value, which is required so that the end key is larger than any row that has a key of ["user:3", 1, (anyConceivableVehicleId)]