nosqlcouchdbcouchdb-futonektorp

How to build a multipart query in Ektorp and couchDB?


I have the following record structure (simplified to include only the bits that matter):

_id: couchDB generated ID
_rev: couchDb generated revision number
_creationDate: timestamp for when the record was created
_amount: how much money was paid
_type: how they paid (Visa, MC, etc.)

I need to be able to sum up the totals for each payment type using the creationDate as the start and end key. I'd like to avoid making multiple queries for each type because there is an unknown number of payment types possible. I'd first have to query all available types and then do a single query for each specific type.

My view's map:

   function(doc) {
      if(doc.type=="payment") {
        emit([doc.type, doc.creationDate], doc.amount);
      }
   }

My view's reduce function:

_sum

My query:

   ViewQuery query = new ViewQuery()
      .designDocId(GLOBAL_DOC)
      .viewName(TOTAL_CREDIT_CARD_PAYMENTS_FOR_TODAY)
      .startKey(start)
      .group(true)
      .groupLevel(1)
      .reduce(true);

When I run the query in futon it builds everything as expected, giving the totals for all types grouped by type (and ignoring creationDate since no start or end is specified). When I run it using the query it looks like the start and end dates are being ignored because I get data outside of the expected range included in the totals.

Also, is there a way through futon to run a query with start and end keys? It's make developing these things faster if I knew how to skip the recompile / reinstall steps.

EDIT: The SQL version of what I'm trying to achieve would look something like this:

   SELECT sum(amount)
   FROM payment
   WHERE creationDate >= startDate AND creationDate <= endDate
   GROUP BY type;

Is there a standard pattern to follow to get this behavior from a map/reduce?


Solution

  • In the end it turns out that it can't be done. I had to write one view for getting a list of all of the available groups and then loop through it in my code, calling another group to get the sums for each group.