Filtering and grouping with Couchbase Lite (Android)

Mobile Couchbase Lite
,
1

Hey all,

I’m in the process of writing a “reports” page for my app that filters and then aggregates data. More specifically, I want to design a view/query combination that first filters data in a view by date range, and then groups/reduces based on a secondary key. Is this possible? Here is my attempt so far:

Map Function:

 String docType = SaleItem.class.getSimpleName();
       String type = (String) document.get(DatabaseUtils.type);
        if (docType.equals(type)) {
            List<Object> keys = new ArrayList<>();
            keys.add(document.get("sale_date"));
            keys.add(document.get("supply_item_id"));
            Map<String, Object> totals = new HashMap<>(3);
            totals.put("total_price", new BigDecimal(document.get("total_price").toString()));
            totals.put("quantity_sold", document.get("quantity_sold"));
            totals.put("supply_item_snapshot", document.get("supply_item_snapshot"));
            emitter.emit(keys, totals);
         }
}

and my reduce function:

 SaleItemAggregate saleItemAggregate = new SaleItemAggregate();
            for (Object value : values) {
                LazyJsonObject jsonValue = (LazyJsonObject) value;
                if (value != null) {
                    saleItemAggregate.addValues(jsonValue);
                }
            }
            return saleItemAggregate;

Here’s my query code:

 mQuery = SaleItem.getQuery(getMyActivity().getDatabase(), SaleItem.AGGREGATE_VIEW);
        mQuery.setDescending(true);
        mQuery.setStartKey(Arrays.asList(new ArrayList<>(), DateTime.now().getMillis()));
        mQuery.setEndKey(Arrays.asList(DateTime.now().withDayOfMonth(1).withTimeAtStartOfDay()));
        mQuery.setGroupLevel(2);

I’m obviously leaving a few things out, but I think I’ve got the essentials in here, and the stuff I left is working for every other query.

My hunch is that this isn’t possible because grouping attempt to match both of the first two keys, but I’d like that confirmed

2

So you want to get a list of dates with the aggregate sales data for the items on that date? I think the only thing wrong in your code is that the groupLevel should be 1, not 2.

(FYI, your reduce function is problematic because it doesn’t actually reduce the amount of data. If you tried to run this on CouchDB it would fail with an error saying that the reduce data is too large. They added this error because this kind of reduce function can blow up on large data sets and take up huge amounts of database space. Couchbase Lite doesn’t check this [yet], because it doesn’t [yet] persist reduced values but computes them at runtime.)

3

Nope.

Each sale item references the item in inventory that was sold. The reduce function (and therefore group level of two) is to aggregate all sales of a specific item together (so for example, say you sold 10 oranges in one sale for $5, and then 3 oranges in another $1.50, the aggregator pulls these sales together based on the inventory item ID of an orange, so it would generate a SaleItemAggregate object with 13 total items sold for a value of $6.50. Apples would then get a similar aggregate object). However, I want to filter what gets aggregated based on dates. So if the first orange was sold last week, I want to give our users the option of excluding that from the total by filtering for only sales made this week.

4

Oh, I see. It looks like your start key is wrong; it should be

mQuery.setStartKey(Arrays.asList(DateTime.now().getMillis(), new HashMap<>()));
5

Hey Jens,

could you do me a huge favour and take a look at this? pushing towards a release and this is the last big thing I need to figure out