Using NSPredicate with Core Data for deep relationships

Question

I have an NSArrayController, companiesController bound to a top level Core Data entity, Companies.

A Company has many Department's, and a Department has many Employee; these are represented by the 1-to-many relationships, departments and employees.

Based on the attribute salary of an Employee I thought I could dynamically do this for filtering based on salary inside a UI-called method:

NSPredicate *predicate = [NSPredicate predicateWithFormat:@"ANY departments.employees.salary < %@", [NSNumber numberWithInt:23000]];
[companiesController setFilterPredicate:predicate];

Alas, this gives me the error: -[NSCFSet compare:]: unrecognized selector sent to instance.

Arleyne

Answer 1

Multiple to-many keys are not allowed in this case.

Instead, you could do the following:

1. Modify the data model by adding a "filter" flag (Boolean) attribute to the Department entity.
2. Create a method to: fetch all the Department objects, set the filter flag to YES for the departments that meet the criteria of the second half of your predicate, set the filter flag to NO for the other departments, and save.
3. Use the filter flag in the Company predicate.

Code changes (step 3):

    //NSPredicate *predicate = [NSPredicate predicateWithFormat:@"ANY departments.employees.salary < %@", [NSNumber numberWithInt:23000]];
    [self setDeptFilter:23000];
    NSPredicate *predicate = [NSPredicate predicateWithFormat:@"ANY depts.filter == YES"];
    [companiesController setFilterPredicate:predicate];

And the new method (step 2):

- (void)setDeptFilter:(NSUInteger)salary {
    NSFetchRequest *fetchRequest = [[NSFetchRequest alloc] init];

    NSEntityDescription *entity = [NSEntityDescription entityForName:@"Department" inManagedObjectContext:self.managedObjectContext];
    [fetchRequest setEntity:entity];

    NSError *error = nil;

    // fetch all Department objects
    NSArray *array = [self.managedObjectContext executeFetchRequest:fetchRequest error:&error];

    [fetchRequest release];

    if (error) {
        NSLog(@"Error fetching Departments %@, %@", error, [error userInfo]);
        abort();
    }

    NSPredicate *predicate = [NSPredicate predicateWithFormat:@"ANY emps.salary < %@",[NSNumber numberWithInteger:salary]];
    NSArray *filterArray = [array filteredArrayUsingPredicate:predicate];

    // set filter flag to YES for the departments that meet the criteria
    for (Department *dep in filterArray) {
        dep.filter = [NSNumber numberWithBool:YES];
    }

    NSMutableArray *diffArray = [array mutableCopy];
    [diffArray removeObjectsInArray:filterArray];

    // set filter flag to NO for the departments that do NOT meet the criteria
    for (Department *dep in diffArray) {
        dep.filter = [NSNumber numberWithBool:NO];
    }

    [diffArray release];

    // save
    if ([self.managedObjectContext hasChanges] && ![self.managedObjectContext save:&error]) {
        NSLog(@"Unresolved error %@, %@", error, [error userInfo]);
        abort();
    } 
}

Ancohuma

Thanks, this works. Does seem like a workaround, but that's just Core Data's issues I guess. – Arleyne

I would assume it has something to do with how Core Data translates the NSPredicate into SQLite statement(s). The syntax is ambiguous: do you mean ANY or ALL Employee(s) in a particular Department. Also, I am a SQL novice, but my guess is that even if your original predicate can be done in a straightforward SQL statement, it would require a potentially large join which Apple may consider too memory or performance intensive for a single fetch. – Ancohuma

Also, I spent quite a while on this so an up-vote would be appreciated :-). – Ancohuma

Answer 2

You could also do this using subqueries.

Get all departments. The 'of' relationship is the inverse of company to-many departments:

-(void)printDepartmentsWithSalaryHigherThan:(int)salary inContext:(NSManagedObjectContext *)context {    
    NSFetchRequest *request = [[NSFetchRequest alloc ]init];
    request.entity = [NSEntityDescription entityForName:@"Department" inManagedObjectContext:context];
    request.predicate = [NSPredicate predicateWithFormat:@"SUBQUERY(employees, $emp, $emp.salary > %@ ).@count > 0", [NSNumber numberWithInt:salary]];

    for(Department *dep in [context executeFetchRequest:request error:nil]){
        NSLog(@"Department: %@", dep.depName);
        NSLog(@"in Company: %@", dep.of.compName);
    }
    [request release];
}

Or, if you have more companies and just want the companies that have an employee with a salary 'higher than' some amount. A subquery based on the result of a subquery

-(void)printCompaniesWithHigherSalaryThan:(int)salary inContext:(NSManagedObjectContext *)context {
    NSFetchRequest *request = [[NSFetchRequest alloc ]init];
    request.entity = [NSEntityDescription entityForName:@"Company" inManagedObjectContext:context];
    request.predicate = [NSPredicate predicateWithFormat:@"SUBQUERY(departments, $dep, SUBQUERY($dep.employees,$emp,$emp.salary > %@).@count > 0 ).@count > 0", [NSNumber numberWithInt:salary]];

    for(Company *c in [context executeFetchRequest:request error:nil]){
        NSLog(@"Company: %@", c.compName);
    }
    [request release];
}

Cuticula

Does this work with an SQLite store for both Mac OS and iOS? From the Apple documentation (from the iOS 5.0 library: Core Data Programming Guide > Persistent Store Features > Fetch Predicates and Sort Descriptors -- may be different for Mac OS): "There are additional constraints on the predicates you can use with the SQLite store: You cannot necessarily translate “arbitrary” SQL queries into predicates." – Wilonah

Best real world predicate SubQuery example I have came across yet – Theatre

+1. I faced a similar issue, the data model was a bit more complex. EntityA--->> EntityB <<-->> Entity C. Though Gerry's solution would work, that would require some changes in the data model, using SubQueries I feel is a bit more elegant to do it. I did it using Subqueries. Had not used them until now :) Thanks for the great tip. I think this should be accepted as an alternate answer!! – Virtuosity

The second SUBQUERY(...SUBQUERY(... example did not work for me. Probably was just doing something wrong, but I changed the first piece to "SUBQUERY(departments.employees, $emp, $emp.salary > %@).@count > 0" and it passed ;) , so +1 – Turro