Best way to get count of related documents

[wbi...@attic.nl]

I need to present a list of Orders and a count of the OrderLines for each order. I currently use view collation (http://docs.couchdb.org/en/latest/couchapp/views/collation.html) and create a view that returns orders and orderlines and 'manually' count the orderlines, using a mapblock like this:

if ([doc[@"doctype"] @"orderline]) {
    emit(@[@(orderid)], nil);
}
if ([doc[@"doctype"] @"order"]) {
    emit(@[@(id), @"name"], nil);
}

Is there a better way to do this ? 

[Jens Alfke]

Yes, that’s pretty much how you’d do it with map/reduce. You also have a reduce function that counts the number of values, and then use a groupLevel=1 in the query, right?

—Jens

[wbi...@attic.nl]

On Sunday, 17 April 2016 03:07:33 UTC+2, Jens Alfke wrote:

Yes, that’s pretty much how you’d do it with map/reduce. You also have a reduce function that counts the number of values, and then use a groupLevel=1 in the query, right?

Hmm, no. If I do that I end up with rows with just the orderId and a count (or not ?), but I also need the name. 

Or should I only emit the orderId and use prefetch to get the order properties ? But I'm only interested in prefetching Orders, not Orderlines.

Just to be clear: I want to (efficiently) present a UI like so:

Order #1, Jones, 3 lines

Order #2, Smith, 6 lines

etc

[Jens Alfke]

Could you post the actual map function you’re using? What you posted in the first message won’t compile and I can’t tell from it what the docs look like. And/or show the JSON of the doc types involved.

—Jens

[James Nocentini]

I’m not sure whether group level should always use a reduce function or not. Let’s take the example where the key in the emit call is [location, name]. Using group level = 1, get counts for rows with the same location and group level = 2 to get rows with the same location and name. But in many other places throughout an app, I might just need the docs in a given location with any name, in that case I use ["aplace"] for the start key and ["aplace", {}] as the end key.

Depending on the info the app requires, you can use different query options on the view. The full map function would definitely help, I’m not familiar with two emit calls in the map block (even less so when using group level at the same time).

James

[wbi...@attic.nl]

On Sunday, 17 April 2016 23:22:05 UTC+2, Jens Alfke wrote:

Could you post the actual map function you’re using? What you posted in the first message won’t compile and I can’t tell from it what the docs look like. And/or show the JSON of the doc types involved.

    [self.view setMapBlock: MAPBLOCK({

        if ([doc[@"doctype"] isEqualToStringCaseInsensitive: @"orderline"]) {

            NSInteger orderId = [doc[@"orderId"] intValue];

            if (orderId != -1) {

                emit(@[@(orderId)], @1);

            }

        }

        if ([doc[@"doctype"] isEqualToStringCaseInsensitive: @"order"]) {

            NSInteger orderId = [doc[@"id"] intValue];

            NSString *firstName = [doc[@"firstName"] length] ? doc[@"firstName"] : @"";

            NSString *lastName = [doc[@"surName"] length] ? doc[@"surName"] : @"";

            NSString *email = [doc[@"email"] length] ? doc[@"email"] : @"";

            NSString *phone = [doc[@"phone"] length] ? doc[@"phone"] : @"";

            NSNumber *vip = [doc[@"vip"] intValue] ? @1 : @0;

            NSString *avatarUrl = [doc[@"avatarUrl"] length] ? doc[@"avatarUrl"] : @"";

            emit(@[@(orderId), firstName, lastName, email, phone, vip, avatarUrl], @0);

        }

    }) reduceBlock: ^id(NSArray *keys, NSArray *values, BOOL rereduce) {

        return [CBLView totalValues: values];

    } version: @"0.44"];

 

I dont see how this could work; grouplevel 1 wont give me the extra properties I need and grouplevel 7 wont give me proper aggregation.

(what I need is several properties of doc type X and the count of doc type Y that is related to an instance of type X, in a single query)

[Jens Alfke]

You want groupLevel=1 because you want to aggregate rows based on the first element of the key (the order ID.)

In the first emit() call, just emit the orderID as the key and no value:
emit(@(orderId), nil);

In the second emit() call in your map function, make the key just the orderID and put the rest of the info in the value, i.e.
emit(@(orderId), @[firstName, lastName, email, phone, vip, avatarUrl]);

Now your reduce function will be called with an array of values, the last of which will contain the order info, the rest empty. It then returns the order info, appending the count of values minus 1.

—Jens

[wbi...@attic.nl]

On Wednesday, 20 April 2016 21:12:42 UTC+2, Jens Alfke wrote:

Now your reduce function will be called with an array of values, the last of which will contain the order info, the rest empty. It then returns the order info, appending the count of values minus 1.

Awesome, I would never have found that solution - thanks a lot !