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

Admeasurement

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!

Admeasurement

You're only one person, right? You just answered your own question and said "Hope this helps." Anyway, this is not going to work, because the order of the keys is wrong. The range between [start_date, 1] and [end_date, 1] will include a lot of things you don't want, like everything in the date range for product id=2. This will work if you emit the product id before the date. – Tragic

Hehhe! I thought this will help others as well! :) You are right, I will have to reverse the key order and then query the view. Let me do an edit to the solution. Thanks! – Admeasurement

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.

Tragic

Answer 3

How about this solution:

1. I create a view for each product with logtime as the index.
2. Access each view if required and filter theresults using the range - [fromdate todate]
3. Do 3 for each product in the input parameters and collate the results

This has a drawback that for every product we will have to create a view and this looks like a manual process.

Just a thought! Let me know your views.

Admeasurement

It can be simpler than that. Just reverse your key order so that documents are sorted first by product, then by logtime. Query the same view three times, looking for each product in the time range you want. This is the second suggestion in the answer I submitted, so if you like it please mark it as accepted :) – Tragic

Hello, How should I "look for each product in the time range"? I think this is precisely the question I asked. The fact is that we can send only a date or a range [startdate enddate] to a view. But we cannot send both the range and the product ID at the same time. I am considering using lists which accept any number of query parameters. Will give an update. Thanks! – Admeasurement

You can look for the range and the product ID at the same time. CouchDB views can use composite keys, and you gave an example of how to build one. All you have to do is reverse the order of your key so that it's [doc.product_id, doc.logtime]. To query it, use startkeys and endkeys in the same two-item format, as per the documentation you linked to. – Tragic

For the record, you can use temporary views, and it's also possible (given permission) to write views into Couch just like any other data. – Wilding