CouchDB Views: Joins and Subqueries

Question

I posted this to the CouchDB-Users mailing list, but I thought I'd cast my net a little wider.

[ links destroyed due to stackoverflow's new user spam rules :-( ]

Hopefully one of you smart people can help (or at least someone can definitively tell me what I'm trying to do is impossible, and I'll have to look into alternatives... I heard MySQL is on the uptake (-: ).

Background information: I've set up an example database here: scoates-test.couchone.com/_utils/database.html?follow / scoates-test.couchone.com/follow

I have two types of documents. type=user and type=asset.

Example user: scoates-test.couchone.com/_utils/document.html?follow/c988a29740241c7d20fc7974be05f67d

Example asset: scoates-test.couchone.com/_utils/document.html?follow/c988a29740241c7d20fc7974be061d62

Users can follow other users (the type=user document's "following" field). The above example user (username=bob) is "following" 2 users:

"following": [
   "c988a29740241c7d20fc7974be05ec54", // username=aaron
   "c988a29740241c7d20fc7974be060bb4" // username=dale
]

Assets are owned by a specific user. The above example asset is owned by c988a29740241c7d20fc7974be061d62 (username=bob).

Hopefully that makes sense.

I'd like to request assets that belong to users that bob is following (aaron and dale), and I can't put my finger on view code that will allow this. I can easily emit all assets that belong to c988a29740241c7d20fc7974be05f67d.

I could do it in two requests. First, I'd ask CouchDB for c988a29740241c7d20fc7974be05f67d, and then POST the following as "keys" to a view that returns assets belonging to those keys, but you'll note that my type=asset documents also have a "when" field, and I want to be able to order by that by emitting [doc.owner, doc.when] as the key, and then using startKey/endKey. So POST of keys is out, I think.

I could just join on the application side (query the following keys, make one request for each of these keys, then sort on the app side), but this breaks pagination really badly (I'd need to request a limit of pageSize for each following), and that gets out of control very quickly (if a user is following 1000 users, that's 10,000 records per page).

Neither of these solutions works for me. I'd like to do it in CouchDB.

I'm truly stumped. Please help.

S

Answer 1

There's a fundamental rule to CouchDB views, which can help you solve this kind of problem.

Querying a CouchDB view returns zero, one or more key-value-id tuples such that the key, value and id are computed from the same document. You may optionally ask for the document with the provided id (which may or may not be the document that was used to compute the tuple).

If you think about it, it's fairly logical: every tuple is the consequence of an emit(), so all the emitted data can only come from a single document.

In your specific case, you need the tuple key to contain:

• The current user, because you only want documents visible by that user
• The document's "owner", because you need to sort by that value.
• The document's "when", because you need to sort by that value.

According to the fundamental rule, you need a single CouchDB document to contain all of these. Your current schema (current user and document owner in the "user" document, document owner and when in the "document" document) does not bring together the required data, so you need to change it.

My suggestion would be to use a "sharing" document with the following structure:

{ 
   owner : 'id-of-aaron', 
   followers : [ 'id-of-bob', 'id-of-mike', 'id-of-melissa' ],
   docs : {
     'id-of-doc-1' : '2010-09-08',
     'id-of-doc-2' : '2010-09-07',
     'id-of-doc-3' : '2010-11-27'
   }
}

You need to keep this structure updated whenever an user follows/unfollows the owner, or the owner adds or removes a document. From there, you can simply emit:

for (var docid in doc.docs) 
  for (var i in doc.followers) {
    emit ([doc.followers[i], doc.owner, doc.docs[docid]], { _id : docid });
  }
}