How do you construct the predicate for NSFetchRequest setHavingPredicate:?

Question

For the life of me I can not seem to get this to work.

Assume our entity is an managed object with a status field and an order field.

How would I go about getting all orderedEntries having more than one order that are the same?

Please no answers telling me to just do a subquery with @count in the main predicate, since I know of that solution, the point of this post is to understand how to use the having predicate in core data, which would probably be faster than a subquery anyways. (unless you explain why I can not use a having clause)

The following code would return an array of dictionaries with the number of orders per order number. What I want is to be able to add a having clause to restrict my request to only return the dictionaries representing objects of those orders that have a count greater than 1.

Here is the code so far and my attempts at a having predicate:

NSFetchRequest *fetchRequest = [[NSFetchRequest alloc] init];
NSEntityDescription *entity = [NSEntityDescription entityForName:@"OrderedEntry"
                                          inManagedObjectContext:context];
[fetchRequest setEntity:entity];
[fetchRequest setResultType:NSDictionaryResultType];

NSPredicate *predicate = [NSPredicate predicateWithFormat:
                 @"(status == %@)",[NSNumber numberWithInt:EntryStatusAlive]];


[fetchRequest setPredicate:predicate];


NSExpression *keyPathExpression = [NSExpression expressionForKeyPath: @"order"]; // Does not really matter
NSExpression *maxExpression = [NSExpression expressionForFunction: @"count:"
                                                        arguments: [NSArray arrayWithObject:keyPathExpression]];
NSExpressionDescription *expressionDescription = [[NSExpressionDescription alloc] init];
[expressionDescription setName: @"orderCount"];
[expressionDescription setExpression: maxExpression]; 
[expressionDescription setExpressionResultType: NSInteger32AttributeType];

[fetchRequest setPropertiesToFetch: [NSArray arrayWithObjects:expressionDescription,@"order",nil]];


[fetchRequest setPropertiesToGroupBy:[NSArray arrayWithObjects:@"order",nil]];
//[fetchRequest setHavingPredicate:[NSPredicate predicateWithFormat:@"@count > 1"]];
//[fetchRequest setHavingPredicate:[NSComparisonPredicate predicateWithLeftExpression:maxExpression rightExpression:[NSExpression expressionForConstantValue:[NSNumber numberWithInteger:1]] modifier:NSDirectPredicateModifier type:NSGreaterThanPredicateOperatorType options:NSCaseInsensitivePredicateOption]];

NSError *error;

NSArray * array = [context executeFetchRequest:fetchRequest error:&error];

Answer 1

I ended up going with this for anyone interested

-(BOOL)ordersAreSaneOnDay:(NSNumber*)dayNumber forUser:(User*)user inContext:(NSManagedObjectContext*)context {
NSFetchRequest *fetchRequest = [[NSFetchRequest alloc] init];
NSEntityDescription *entity = [NSEntityDescription entityForName:@"BasicEntry"
                                          inManagedObjectContext:context];
[fetchRequest setEntity:entity];
[fetchRequest setResultType:NSDictionaryResultType];

NSPredicate *predicate = [NSPredicate predicateWithFormat:
                 @"(status == %@) && ((type != %@) && (type != %@) && (dayNumber == %@))  && ((user == NIL) || (user == %@))",[NSNumber numberWithInt:EntryStatusAlive],[NSNumber numberWithInt:EntryTypeTask],[NSNumber numberWithInt:EntryTypeCompletedTask],dayNumber,user];


[fetchRequest setPredicate:predicate];


NSExpression *keyPathExpression = [NSExpression expressionForKeyPath: @"order"]; // Does not really matter
NSExpression *maxExpression = [NSExpression expressionForFunction: @"count:"
                                                        arguments: [NSArray arrayWithObject:keyPathExpression]];
NSExpressionDescription *expressionDescription = [[NSExpressionDescription alloc] init];
[expressionDescription setName: @"orderCount"];
[expressionDescription setExpression: maxExpression]; 
[expressionDescription setExpressionResultType: NSInteger32AttributeType];

[fetchRequest setPropertiesToFetch: [NSArray arrayWithObjects:expressionDescription,@"order",nil]];
[expressionDescription release];

[fetchRequest setPropertiesToGroupBy:[NSArray arrayWithObjects:@"order",nil]];
//[fetchRequest setHavingPredicate:[NSPredicate predicateWithFormat:@"self.order.@count > 1"]];
//[fetchRequest setHavingPredicate:[NSComparisonPredicate predicateWithLeftExpression:maxExpression rightExpression:[NSExpression expressionForConstantValue:[NSNumber numberWithInteger:1]] modifier:NSDirectPredicateModifier type:NSGreaterThanPredicateOperatorType options:NSCaseInsensitivePredicateOption]];

NSError *error;

NSArray * array = [context executeFetchRequest:fetchRequest error:&error];
array = [array filteredArrayUsingPredicate:[NSPredicate predicateWithFormat:@"orderCount > 1"]];
//NSLog(@"it worked %@",array);
[fetchRequest release];

if ([array count]) return FALSE;
return TRUE;

}

Answer 2

I've got this working using the following:

[fetchRequest setHavingPredicate:[NSPredicate predicateWithFormat:@"$orderCount > 1"]];

Use the name of your expressionDecription as variable $orderCount. Alternatively you can use

NSExpression *countExpression = [NSExpression expressionForVariable:@"orderCount"];
[fetchRequest setHavingPredicate:[NSPredicate predicateWithFormat:@"%@ > 1", countExpression]];