NSPredicate to-many relationships

Question

How can I find all the categories that have subcategories with more than 1 items? I am using the following code, but I get "multiple to-many keys not allowed here".

Do I need to use a SUBQUERY?

NSFetchRequest *request = [NSFetchRequest fetchRequestWithEntityName:@"Categories"];
NSPredicate *predicate = [NSPredicate predicateWithFormat:@"ANY subcategories.items > 0"];
[request setPredicate:predicate];

Help! :)

Answer 1

Based on my experience, you should change the predicate format string in

NSPredicate *predicate = [NSPredicate predicateWithFormat:@"ANY subcategories.items > 0"];    

from

@"ANY subcategories.items > 0"

into

@"subcategories.items.@count > 0"

Note that the ANY keyword needs to go as well, or you'll be querying the property the wrong way.

Answer 2

In your NSPredicate you should use subcategories.items.@count >0 instead.

Sorr, that would work only if categories-subcategories wer one - to one.

Really,what you should do, is delete all the SubCategories wich have no items, I think.

Than tou should just check if there are any subcategories and it would mean that they are not empty. subcategories.@count >0

Answer 3

You can only use one to-many relationship in a SQL backend. This will do it, but you have to add the "back-link" into an ordered set (to preserve order if you use sorting - and uniqueness because you may get multiple subcategories pointing back to the same category). Not ideal, but not bad, considering the limitations.

// Search for all subcategories with item count > 0
NSFetchRequest *request = [NSFetchRequest fetchRequestWithEntityName:@"Subcategory"];
request.predicate = [NSPredicate predicateWithFormat:@"items.@count > 0"];
NSError *error = nil;
// They should have an inverse relationship to their category...
// We have to iterate and insert them in to a set, but there is no additional
// disk access.
NSMutableOrderedSet *results = [NSMutableOrderedSet orderedSet];
[[self.projectDatabase.managedObjectContext executeFetchRequest:request error:&error] enumerateObjectsUsingBlock:^(Subcategory *obj, NSUInteger idx, BOOL *stop) {
    [results addObject:obj.category];
}];

EDIT

Unfortunately, I don't think this is possible with the SQL store. First, let me stress that I am not anywhere close to an SQL expert, and I usually try to design my CoreData stores so that these types of queries are not necessary. However, the following SUBQUERY works with an InMemory store, so it's a somewhat properly formatted subquery.

fetchRequest.predicate = [NSPredicate predicateWithFormat:@"SUBQUERY(subcategories, $s, $s.items.@count > 0).@count > 0"];

However, that predicate gives an error when using an SQL store. The docs do say there are a fair number of limitations when using predicates with SQL stores, so I'm neither surprised, nor inclined to spend too much more time researching this.

Keypath containing KVC aggregate where there shouldn't be one; failed to handle $s.items.@count