Subquery in NSPredicate Core Data iOS

Question

I want to make a predicate that looks like the equivalent in SQL:

select f.type, f.variety, f.price
from (
   select type, min(price) as minprice
   from fruits group by type
) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;

But seeing as a I can't sort this with a predicate with a block in core data, I was wondering how I would write this using the [NSPredicate predicateWithFormat:] method.

To be clear, I have a table 'fruits' with columns type, variety and price. I want to select one row for each different type where the price is the minimum among its type.

salo.dm salo.dm · Accepted Answer · 2013-08-27T03:55:46

In Core Data you would use an NSExpression to get a minimum value, and the fetch would return results in dictionary form. So, the setup would be a bit different.

You could perform a fetch to get each of the minimums, doing something like what follows:

NSFetchRequest *request = [[NSFetchRequest alloc] init];
request.entity = [NSEntityDescription entityForName:@"Fruit" inManagedObjectContext:myContext];
request.predicate = [NSPredicate predicateWithFormat:@"type = %@", @"banana"];

request.resultType = NSDictionaryResultType;

NSExpressionDescription *minExDescr = [[NSExpressionDescription alloc] init];
[minExDescr setName:@"myMinimum"];
[minExDescr setExpression:[NSExpression expressionForFunction:@"min:" 
                                                    arguments:[NSArray arrayWithObject:
                                                               [NSExpression expressionForKeyPath:@"price"]]]];
[minExDescr setExpressionResultType:NSFloatAttributeType];

request.propertiesToFetch = [NSArray arrayWithObject:minExDescr];

NSError *err = nil;
NSArray *bananaResults = [self.moContext executeFetchRequest:request error:&err];

Since the result of the fetch is a dictionary, you would retrieve the minimum value as follows:

NSDictionary *resultsDictionary = [bananaResults lastObject];
NSNumber *bananaMinimum = [resultsDictionary objectForKey:@"myMinimum"];

Note that this will only give you the minimum value and not the row (or managed object), as you requested. You can then get the managed object with the minimum value thru a second fetch with a compound predicate, to match type and price. (I don't know whether in Core Data you can get the managed object directly in one fetch. Maybe someone else can comment.)

The predicate for the second fetch would be as follows:

NSPredicate *predicate = [NSCompoundPredicate andPredicateWithSubpredicates:
                          [NSArray arrayWithObjects:
                           [NSPredicate predicateWithFormat:@"type = %@", @"banana"],
                           [NSPredicate predicateWithFormat:@"price = %@", bananaMinimum],
                           nil]];

All of this is assuming you know the types ahead of time. If you don't (i.e. if they're changing), you can get all the distinct types with a preliminary fetch using setReturnsDistinctResults. You would again have to setResultType to NSDictionaryResultType. Also, setPropertiesToFetch to an array containing your type attribute, to get all the distinct types.
Just follow the documentation for setDistinctResults and the same logic as above to set up the fetch.

None of this code is tested, so there might be a typo.

Subquery in NSPredicate Core Data iOS

1 Answers