The WHERE clause within an SQL statement forms the selection criteria for choosing individual records. Within a view, the ability to query the data is controlled by the content and structure of the key generated by the map() function.

In general, for each WHERE clause you need to include the corresponding field in the key of the generated view, and then use the key, keys or startkey/endkey combinations to indicate the data you want to select.. The complexity occurs when you need to perform queries on multiple fields. There are a number of different strategies that you can use for this.

The simplest way is to decide whether you want to be able to select a specific combination, or whether you want to perform range or multiple selections. For example, using our recipe database, if you want to select recipes that use the ingredient 'carrot' and have a cooking time of exactly 20 minutes, then you can specify these two fields in the map() function:

function(doc, meta) 
{
  if (doc.ingredients) 
  {
    for(i=0; i < doc.ingredients.length; i++) 
    {
      emit([doc.ingredients[i].ingredient, doc.totaltime], null);
    }
  }
}

Then the query is an array of the two selection values:

?key=["carrot",20]

This is equivalent to the SQL query:

SELECT recipeid FROM recipe JOIN ingredients on ingredients.recipeid = recipe.recipeid 
    WHERE ingredient = 'carrot' AND totaltime = 20

If, however, you want to perform a query that selects recipes containing carrots that can be prepared in less than 20 minutes, a range query is possible with the same map() function:

?startkey=["carrot",0]&endkey=["carrot",20]

This works because of the sorting mechanism in a view, which outputs in the information sequentially, fortunately nicely sorted with carrots first and a sequential number.

More complex queries though are more difficult. What if you want to select recipes with carrots and rice, still preparable in under 20 minutes?

A standard map() function like that above wont work. A range query on both ingredients will list all the ingredients between the two. There are a number of solutions available to you. First, the easiest way to handle the timing selection is to create a view that explicitly selects recipes prepared within the specified time. I.E:

function(doc, meta) 
{ 
  if (doc.totaltime <= 20) 
  {
    ...
  }
}

Although this approach seems to severly limit your queries, remember you can create multiple views, so you could create one for 10 mins, one for 20, one for 30, or whatever intervals you select. It's unlikely that anyone will really want to select recipes that can be prepared in 17 minutes, so such granular selection is overkill.

The multiple ingredients is more difficult to solve. One way is to use the client to perform two queries and merge the data. For example, the map() function:

function(doc, meta) 
{
  if (doc.totaltime &amp;&amp; doc.totaltime <= 20) 
  {
    if (doc.ingredients)
    {
      for(i=0; i < doc.ingredients.length; i++) 
      {
        emit(doc.ingredients[i].ingredient, null);
      }
    }
  }
}

Two queries, one for each ingredient can easily be merged by performing a comparison and count on the document ID output by each view.

The alternative is to output the ingredients twice within a nested loop, like this:

function(doc, meta) 
{
  if (doc.totaltime &amp;&amp; doc.totaltime <= 20) 
  {
    if (doc.ingredients) 
    {
      for (i=0; i < doc.ingredients.length; i++) 
      {
        for (j=0; j < doc.ingredients.length; j++) 
        {
          emit([doc.ingredients[i].ingredient, doc.ingredients[j].ingredient], null);
        }
      }
    }
  }
}

Now you can perform an explicit query on both ingredients:

?key=["carrot","rice"]

If you really want to support flexible cooking times, then you can also add the cooking time:

function(doc, meta) 
{
  if (doc.ingredients) 
  {
    for (i=0; i < doc.ingredients.length; i++) 
    {
      for (j=0; j < doc.ingredients.length; j++) 
      {
        emit([doc.ingredients[i].ingredient, doc.ingredients[j].ingredient, recipe.totaltime], null);
      }
    }
  }
}

And now you can support a ranged query on the cooking time with the two ingredient selection:

?startkey=["carrot","rice",0]&key=["carrot","rice",20]

This would be equivalent to:

SELECT recipeid FROM recipe JOIN ingredients on ingredients.recipeid = recipe.recipeid 
    WHERE (ingredient = 'carrot' OR ingredient = 'rice') AND totaltime = 20