Group by one property while retrieving more with core data

Question

I have a table in Core Data with the following properties:

date, name, phone

I want to retrieve a list of 5 unique phone numbers with names ordered by date. So in MySQL it would be something like:

SELECT name, phone FROM myTable GROUP BY phone ORDER BY date   

So if I have this list of data:

01/23/2015 someName1 12345678
01/21/2015 someName2 12345678
01/21/2015 someOtherName1 987654321

I would like to retrieve a list of unique phone numbers with the date field controlling which name to be associated with any duplicated numbers. In this case, the desired result would be:

01/23/2015 someName1 12345678
01/21/2015 someOtherName1 987654321

However doing this with a NSFetchRequest seems a bit complicated since the setPropertiesToGroupBy requires to contain the same list of properties as defined in setPropertiesToFetch.

In other words, this is how I would think I would be able to do it:

    NSEntityDescription *entity = [NSEntityDescription  entityForName:@"someEntity" inManagedObjectContext:someContext];
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    [request setEntity:entity];
    [request setResultType:NSDictionaryResultType];

    [request setPropertiesToFetch:@[@"name", @"phone"]];
    [request setPropertiesToGroupBy:@[@"phone"]];
    [request setFetchLimit:5];

    NSSortDescriptor *sortByCreatedDate = [[NSSortDescriptor alloc] initWithKey:@"date" ascending:NO];
    [request setSortDescriptors:@[sortByCreatedDate]];

However I will get an exception on the propertiesToGroupBy unless I include the name field as well.

How can I achieve what I want without having to manually go through the list?

Answer 1

With a proviso that I think this will work; I leave it to you to test it to death:

You cannot add any other attributes to propertiesToFetch, beyond those in the propertiesToGroupBy, but it seems you can include the objectID in the properties to fetch. To do so, build an NSExpression and associated NSExpressionDescription for the "evaluated object":

NSExpression *selfExp = [NSExpression expressionForEvaluatedObject];
NSExpressionDescription *selfED = [[NSExpressionDescription alloc] init];
selfED.name = @"objID";
selfED.expression = selfExp;
selfED.expressionResultType = NSObjectIDAttributeType;

Now define another expression/description to get the max(date):

NSExpression *maxDate = [NSExpression expressionForKeyPath:@"date"];
NSExpression *indexExp = [NSExpression expressionForFunction:@"max:" arguments:@[maxDate]];
NSExpressionDescription *maxED = [[NSExpressionDescription alloc] init];
maxED.name = @"maxDate";
maxED.expression = indexExp;
maxED.expressionResultType = NSDateAttributeType;

Then include these two expression descriptions in the list of properties to fetch:

[request setPropertiesToFetch:@[@"phone", maxED, selfED]];
[request setPropertiesToGroupBy:@[@"phone"]];

When you run the fetch, each item in the resulting array will have a key "objID" containing the objectID for the relevant object. You can unpack that, to access the name, phone, etc, with something along these lines:

NSArray *results = [self.context executeFetchRequest:request error:&error];
for (NSDictionary *dict in results) {
    NSDate *maxDate = dict[@"maxDate"];
    NSString *phone = dict[@"phone"];
    NSManagedObjectID *objID = [dict valueForKey:@"objID"];
    NSManagedObject *object = [self.context objectWithID:objID];
    NSString *name = [object valueForKey:@"name"];
}

One particular aspect I am unsure of is how this will behave if two rows have exactly the same date.