How to use OR in a couchdb view

Question

I use ruby on rails and couchdb but I have problem view in couchdb

in sql I want:

select * from user where username = ... or email = ... and status = ...

How do I create view like that?

Answer 1

That query is a join of your data. (No, it is not an actual JOIN statement, but I mean, you are asking 3 different questions and "joining" the results in the answer.)

Solution 1: Just query multiple times.

Suppose you make 2 views, the value is the status and the keys are like this:

• by_username
• by_email

Keep a map (Java), or dict (Python), hash (Perl), object (Javascript), or whatever your key/value data structure is. You want to keep a set of matching users, and add to it.

Query each view. If the status is what you are need, add the usernames to your set. When all queries are complete, you have the full answer.

Oh no! But that is so slow! In fact, very often, solutions are slow "in theory" but actually they are plenty fast for the requirements. You have a Rails server. It probably has high-speed, low-latency (LAN) access to CouchDB. Every query in CouchDB is always an efficient index scan. So you make 3 requests, bam, bam, bam! The client queries Rails over a low-speed, high-latency connection (the Internet), it probably will not notice.

Alternatively, depending on your language and skills, you can do these queries simultaneously (asynchronously) and the query time will, basically, be fast.

Solution 2: The secret multiple-keys query

CouchDB actually supports some "OR" queries to a view. See the CouchDB view options for details.

You need one view to store all of the information. I suggest array keys, [status, key_type, key_value]. For example, if you have two users, Alice and Bob, the view keys would be:

["reading" , "email"   , "[email protected]"]
["reading" , "username", "alice"]
["sleeping", "email"   , "[email protected]"]
["sleeping", "username", "bob"]

How would you query for status = "sleeping" and username = "X" or email = "Y"?

This becomes multiple queries to the view, each with a different key:

key=["sleeping", "username", "X"]
key=["sleeping", "email"   , "Y']

Fortunately, you can query for multiple keys at the same time.

POST /db/_design/example/_view/state_and_identifiers
Content-Type: application/json

{"keys": [ ["sleeping", "username", "X"]
         , ["sleeping", "email"   , "Y']
         ]
}

CouchDB will return all of the results in one response.

Summary

The second solution is very powerful, however you must do a lot of work to get the right query. And this technique cannot always satisfy any SQL query. SQL is more powerful for asking any question you can think of. For CouchDB, you always have to teach it what to do first. That is inconvenient. But the result is, queries are guaranteed to be fast.

Considering the inconvenience of solution 2, I personally prefer solution 1. Can you do a "join" in CouchDB? Sure! Joins are so simple. Just make multiple queries until you have the data you need. What if your application needs to "join" very often, and using CouchDB is difficult and buggy? That is a powerful sign that CouchDB may not be appropriate for your application.