CouchDB view query with multiple key values

Question

I am currently trying to create a view and query to fit this SQL query:

SELECT * FROM articles
WHERE articles.location="NY" OR articles.location="CA"
ORDER BY articles.release_date DESC

I tried to create a view with a complex key:

function(doc) { 
  if(doc.type == "Article") { 
    emit([doc.location, doc.release_date], doc) 
  }
}

And then using startkey and endkey to retrieve one location and ordering the result on the release date.

.../_view/articles?startkey=["NY", {}]&endkey=["NY"]&limit=5&descending=true

This works fine.

However, how can I send multiple startkeys and endkeys to my view in order to mimic

WHERE articles.location="NY" OR articles.location="CA" ?

Answer 1

My arch nemesis, Dominic, is right.

Furthermore, it is never possible to query by criteria A and then sort by criteria B in CouchDB. In exchange for that inconvenience, CouchDB guarantees scalable, dependable, logarithmic query times. You have a choice.

• Store the view output in its own database, and make a new view to sort by criteria B
• or, sort the rows afterward, which can be either
  • Sort client-side, once you receive the rows
  • Sort server-side, in a _list function. The is great, but remember it's not ultimately scalable. If you have millions of rows, the _list function will probably crash.

Answer 2

The short answer is, you currently cannot use multiple startkey/endkey combinations.

You'll either have to make 2 separate queries, or you could always add on the lucene search engine to get much more robust searching capabilities.

It is possible to use multiple key parameters in a query. See the Couchbase CouchDB documentation on multi-document fetching.