couchdb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jason Smith <>
Subject Re: Query design questions
Date Wed, 08 Apr 2009 16:41:15 GMT
Hi, Chris.  For me personally, even using the word "query" is 
inaccurate.  I think of it is having one input where I place my data, 
and several outputs of my data (views I define) that list my data in a 
1-D array which I can take slices of.

Chris Kilmer wrote:
> I have some design questions.  First about query design and then about
> document design.
> 1. Query design:  We have a set of existing documents, Project, User,
> and Membership.  Business constraints: A user is associated with many
> projects through membership.  A user can also be marked as a project
> owner through the membership. A project can also retrieve a list of
> it's owners.  Given the constraints, some sample documents are:
> User
> {'id' : 'xxx'}
> {'id' : 'yyy'}
> Project
> {'id' :  'aaa'}
> {'id' : 'ccc'}
> Membership
> {'user_id' : 'xxx', 'project' :  'aaa', 'is_owner' : true}
> {'user_id' : 'yyy', 'project' :  'aaa', 'is_owner' : false}
> {'user_id' : 'xxx', 'project' :  'bbb', 'is_owner' : true}
> {'user_id' : 'xxx', 'project' :  'ccc', 'is_owner' : true}
> What I'm wondering, is:
> 1. What is the easiest way to retrieve all the projects where a user
> is the(an) owner? (our current implementation uses two queries)
> 2. What is the easiest way to retrieve all the owners for a particular project?

> On another front, I realize the way the documents above are laid out
> is 'very relational'.  I would love some ideas about on modeling the
> relationships (should I even be using that word?) in a more document
> centric manner.

There is a relevant article about relations in App Engine which you 
should read if you haven't.

Many-to-many is halfway down.  In short, besides the "relationship" way, 
you can keep a list of "foreign keys" right inside your document. 
(Sorry for mixing the terminology.)  CouchDB maps make it easier to 
massage your data into nice clean lists of data structures that tell you 
exactly what you need and quickly.

In your specific example, what if you had a map that does something like 
this (not tested) in a projects-by-owner" view?

     if(doc.type == 'User')
         emit([, null], null);

     else if(doc.type == 'Membership' && doc.is_owner)
         emit([doc.user_id, doc.project], doc.project);

The sort key is a 2-item list.  You want "all X about some user" which 
means all your users will have to clump together in the view output 
since you can only take an array slice in CouchDB.  That is why the 
key's first item is always a user ID.  With all users clumped together, 
the second item determines sort order per user, and null guarantees that 
the user document will come first.  Beneath that will be all project IDs 
that the user owns.

So if you query with a startkey=[, null] and 
endkey=[, {}], then discard the first item in the response, 
you will have a list of project IDs that the user owns.

The advantage of getting rid of the membership documents and going with 
the list-of-keys method is that you have both the project data and the 
user data available to you at the same time.  So you could loop through 
your project's owners list and emit similar data as above, but instead 
of just a project ID, you could emit actual project data so that you 
wouldn't have to make another request to get followup data.

Hope that helps!  Your mileage may vary.  Some restrictions apply.  See 
site for details.

Jason Smith
Proven Corporation
Bangkok, Thailand

View raw message