How to translate this SQL Query into a NSFetchRequest

Question

how can I translate this SQL Query into an NSFetchRequest:

select max(ZSENTAT), * from ZMESSAGE where ZISINCOMING == 1 group by ZROOTMESSAGEID;

This is how far I got:

NSPredicate *predicate = [NSPredicate predicateWithFormat:@"isIncoming == %@ AND sentAt == @max.sentAt", [NSNumber numberWithBool:YES]];
NSFetchRequest *request = _fetchedResultsController.fetchRequest;
request.predicate = predicate;
request.propertiesToGroupBy = @[ @"rootMessageId" ];

This results in an exception: 'NSInvalidArgumentException', reason: 'SELECT clauses in queries with GROUP BY components can only contain properties named in the GROUP BY or aggregate functions

Thanks Chris

Answer 1

The error arises because you need to restrict the fetch to only those properties in the "group by" and any aggregate functions. In your case you need to limit it to rootMessageId and max(sentAt). To do this you have to specify the NSFetchRequest's propertiesToFetch. Note, if you do that, then you must specify its resultType as NSDictionaryResultType and that will restrict you from using the change tracking (ie delegate methods) of NSFetchedResultsController.

If you do want to achieve your fetch: specifying the rootMessageId is easy, specifying the max(sentAt) takes a little more work. You have to create an NSExpression and NSExpressionDescription. Take a look at the Apple docs for these, but the following should get you started:

NSExpression *maxExpression = [NSExpression expressionWithFormat:@"max:(sentAt)"];
NSExpressionDescription *maxDesc = [[NSExpressionDescription alloc] init];
maxDesc.name = @"maxSentAt";
maxDesc.expression = maxExpression;
maxDesc.expressionResultType = NSDateAttributeType; // I assume sentAt is a NSDate
request.propertiesToFetch = @[@"rootMessageId", maxDesc];
request.propertiesToGroupBy = @[@"rootMessageId"];
request.resultType = NSDictionaryResultType;
NSPredicate *predicate = [NSPredicate predicateWithFormat:@"isIncoming == %@",[NSNumber numberWithBool:YES]];
request.predicate = predicate;
NSArray *results = [self.managedObjectContext executeFetchRequest:request error:&error];
etc

The results array will contain dictionaries with two keys: "rootMessageId" and "maxSentAt".