Matt Connolly's Blog

my brain dumps here…

Monthly Archives: June 2012

iOS Core Data: Group by and count results

I looked in the usual places and couldn’t find any decent examples of using core data to do a group by and count. So I’m making one here.

Let’s suppose you have a bunch of Records, and each Record has a Status attribute among others, and you want a break down of how many have each Status. The SQL would be:

SELECT `Status`, COUNT(*) FROM `Records` GROUP BY `Status`

How do we do this with Core Data?

start with a fetch request:

NSFetchRequest* fetch = [NSFetchRequest fetchRequestWithEntityName:@"Record"];

set up an attribute description and expression description for the two values we want in the results:

NSEntityDescription* entity = [NSEntityDescription entityForName:@"Record"
NSAttributeDescription* statusDesc = [entity.attributesByName objectForKey:@"status"];
NSExpression *keyPathExpression = [NSExpression expressionForKeyPath: @"url"]; // Does not really matter
NSExpression *countExpression = [NSExpression expressionForFunction: @"count:"
                                                          arguments: [NSArray arrayWithObject:keyPathExpression]];
NSExpressionDescription *expressionDescription = [[NSExpressionDescription alloc] init];
[expressionDescription setName: @"count"];
[expressionDescription setExpression: countExpression];
[expressionDescription setExpressionResultType: NSInteger32AttributeType];

tell the fetch request to only fetch these, and group by the status attribute description

[fetch setPropertiesToFetch:[NSArray arrayWithObjects:statusDesc, expressionDescription, nil]];
[fetch setPropertiesToGroupBy:[NSArray arrayWithObject:statusDesc]];
[fetch setResultType:NSDictionaryResultType];
NSError* error = nil;
NSArray *results = [myManagedObjectContext executeFetchRequest:fetch

And voila. The result is an array of dictionaries, each with “status” and “count” keys and corresponding values. Even though the group by guarantees the grouped values be unique, there could be more than one grouped column, so it does make sense.
If you think that’s a lot of code, it is. Let’s compare that with the Rails solution:

Record.count(:group => :status)

And that returns a dictionary (Hash), keyed by status, with the counts in the values

Rails: beware a column named ‘type’

It’s been said many times to not use the column name ‘type’, because rails uses this to define the subclass of a model that should be loaded. (great feature, by the way).

But if you have an existing database with a column called “type” and you want to interface to that database with active record, this is a great tip (thanks to this post):

class MyModel < ActiveRecord::Base
  self.inheritance_column = "inheritance_type"
Ahh… back to work.