How to query a couchdb view using a composite key?

Question

I have a couchdb view "record_by_date_product" with the following definition:

function(doc) {
  emit([doc.logtime, doc.product_id], doc);
}

I am trying to run a query which is something like:

(logtime > fromdate & logtime < todate)  & product_id in (1,2,6)

Is this possible with this view?

I am also using couchdb python library to access couchdb. Here is a code snippet:

server = couchdb.Server()
db = server['mydb']

results = db.view('_design/record_by_date_product/_view/record_by_date_product')

This page http://packages.python.org/CouchDB/client.html#viewresults specifies that we can use a startkey and endkey. But I am not able to get it working.

Thanks

Answer 1

I think I just found the exact answer:

Design a view 'sampleview' which is like:

{
   "records_by_date_product": {
       "map": "function(doc) {\n  emit([doc.prod_id, doc.logtime], doc);\n}"
   }
}

Let us say that the query parameters are:

prod_id in [1,3]
from_date = '2010-01-01 00:00:00'
to_date = '2010-01-02 00:00:00'

Then you will have to run 2 separate queries on the same view:

http://localhost:5984/db/_design/sampleview/_view/records_by_date_product?startkey='\["1,2010-01-01%2000:00:00"\]'&endkey='\[1,"2010-01-02%2000:00:00"\]'

http://localhost:5984/db/_design/sampleview/_view/records_by_date_product?startkey='\[2,"2010-01-01%2000:00:00"\]'&endkey='\[2,"2010-01-02%2000:00:00"\]'

Notice that the same query is run each time except that the prod_id is changed in the second query. The results have to be collated later. Hope this helps!

Answer 2

That exact query is not possible. As the documentation suggests, you can get everything in a view in a particular key range. Views are sorted data structures, so all CouchDB does to fulfill this request is locate the start key and begin returning items until you hit the end key.

The strategy you should use for this query depends on characteristics of the data itself. Most importantly, will you waste a lot of time weeding out items if you use only the first part of the key (logtime) and iterate through those in Python, weeding out items where product_id won't match? If so, you should consider writing another view that is primarily sorted by product_id. If not, go ahead and use the weed-out approach.