Core Data - Simple JOIN-type Fetch

Question

I am needing to do a "join"-type operation on two tables/models from my Core Data storage, and I'm having a difficult time finding good examples out there for getting something like this to work.

It's fairly simple what I need to do. I have two models/tables: locations and location_categories, where locations contains a location/establishment's details (along with a unique ID), and the location_categories table contains category_id's with associated location_id's. I need to select all the locations where, given a category_id, it joins the two tables/models. If it was straight SQL, it would look like this (and this query does work when I run it against the actual SQLite DB):

// pretend we passed in a catID of 29:
SELECT * FROM zlocation where zlocid in (select zlocid from zloccategory where zcatid = 29)

So as you can see, it's pretty straight-forward. Is there a way to do this without having to do two fetches and for-loops? My current objective-c code to do this is below, and it works fine, but of course it's horribly slow and inefficient, due to a lot of data being in both tables. The for-loops are causing a huge slowdown, and my gut tells me that this work really should be done on the DB/Core-Data side of things:

- (NSMutableArray *) fetchLocsWithCatID:(NSString *)catID {
    NSMutableArray *retArr = [[NSMutableArray alloc] init];

    NSManagedObjectContext *context = [self managedObjectContext];
    NSFetchRequest *fetchRequest = [[NSFetchRequest alloc] init];
    [fetchRequest setEntity:[NSEntityDescription entityForName:MODELNAME_LOCCATEGORIES inManagedObjectContext:context]];
    [fetchRequest setPredicate: [NSPredicate predicateWithFormat:@"catID == %@", catID]];

    NSError *error;
    NSArray *locCats = [context executeFetchRequest:fetchRequest error:&error];
    NSArray *allLocsArr = [self fetchAllDataForTable:MODELNAME_LOCATION]; // this retrieves contents of entire table

    for (Location *currLoc in allLocsArr) {
        for (LocCategory *currLocCat in locCats) {
            if ([currLoc.locID isEqualToString:currLocCat.locID]) {
                if (![retArr containsObject:currLoc]) {
                    [retArr addObject:currLoc];
                }
            }
        }
    }

    return retArr;
}

Any thoughts on whether this is possible with one single predicate/fetch? Or dunno.. maybe there is a more efficient way to join those two NSArrays via the objective-c code?

Thanks in advance for any direction you can offer on this!

Answer 1

The best way would be to use relationships.

This way when you fetch a Location you can also access the LocationCategory like myLocation.locationCategory

You would need to set up your relationsship like so:

(Entity)LocationCategory.(relationship)locations <->> (Entity)Location.(relationship)locationCategory

It's a to-many relationship. Don't forget the inverse either.

Once that's set up you just need to connect relationships up where ever you create the entity instances. Like:

 Location *myLocation = ...
 LocationCategory *myLocationCategory = ...
 myLocation.locationCategoy = myLocationCategory;

Also make sure you are using the same MOC to fetch the objects. I strongly recommend MagicalRecord to easily manage creating objects and fetching in the specified moc.

Answer 2

Read about core data and relationships. It will automatically create Set of other objects of related entities. So in your above case, if you have Locations as one entity, Location_Categories as another category, all you will need to do is create a relationship in Locations table which will point to the Location Categories entity.

You can do this in the Core Data Modeling screen.

Once you do that, the Location Managed Object class will have an NSSet of Location Categories. Similarly, Location Categories Entity will have an object of Location in it.

So all you will need to do is one query of Location Categories just like how you do above. But instead of doing additional queries you will need to do

for (LocationCategory *locationCategory in locationCats) {
    NSLog(@"Location name is %@",locationCategory.location.locationName);
    // You can ofcourse add it to an arrary or what ever
}

Any core data tutorial should be able to help you on this.