Performing lookup on multiple entities using NSPredicate

Question

Hi,

I've run into a problem when I've been porting my code over from a SQLite database to Core Data.

The data that I'm using comes from a existing database and as such has all the relationships defined using the ID of each of the tables (or entities now that I'm using Core Data). My problem is that I want to query against a single table then use that result to propagate up though the data to get all the other data that I require.

The original database looks like:

CREATE TABLE TecAccessPoints (MAC varchar NOT NULL PRIMARY KEY UNIQUE,ZoneID integer NOT NULL,Floor integer);
CREATE TABLE Zones (ID integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,ZoneName varchar NOT NULL,BuildingID integer,ZoneDescription varchar);
CREATE TABLE Buildings (ID integer NOT NULL PRIMARY KEY AUTOINCREMENT,BuildingName varchar NOT NULL,CampusID integer NOT NULL,BuildingDescription varchar,Long float,Lat float);
CREATE TABLE Campuses (ID integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,CampusName varchar NOT NULL,CampusDescription text, Latitude float, Longitude float);

My original SQL query is:

SELECT DISTINCT Zones.ID, Zones.ZoneName, Buildings.ID, Buildings.BuildingName, Campuses.ID, Campuses.CampusName, Rooms.ID, Rooms.NameNumber, Rooms.Floor 
FROM Zones, TecAccessPoints, Buildings, Campuses, Rooms 
WHERE Campuses.ID = Buildings.CampusID 
   AND Buildings.ID = Zones.BuildingID 
   AND Zones.ID = Rooms.ZoneID 
   AND Zones.ID = TecAccessPoints.ZoneID 
   AND TecAccessPoints.MAC = '%@';

Is there any way that I can replicate this query using NSPredicate (or something similar) or is it the case that I'll have to first perform the lookup

TecAccessPoints.MAC == '%@'

Then do another lookup on the data using the returned data like:

Zones.ID == TecAccessPoints.ZoneID

And so on until I have all my results that I need?

Thanks

James

Answer 1

You can't replicate your query as a Core Data predicate because you can fetch only one entity at a time. However, this may be a case where thinking about things as the object graph rather than the database tables might be much more fruitful. If you have relationships

TecAccessPoints <*-> Zones <*-> Buildings <-> etc.
                           <-*> Rooms

You can easily query TecAccessPoints as you show above then follow the relationships in code, e.g.

TecAccessPoint *tap;
// fetch tap

Campus *campus = tap.zone.building.campus;
NSSet *rooms = tap.zone.rooms;

assuming the relationships are named zone, building, rooms, etc. and you have appropriate NSManagedObject categories to define the associated @properties so that the compiler doesn't complain.

Of course, underneath Core Data is doing a sequence of SQL queries which are each a JOIN across multiple tables. Core Data is quite good about making the performance hit of this minimal, but if you're a DB geek, this may bother you. If so, you'll have to stick with the raw SQLite. Moving to Core Data implies you're willing to think about things at the object graph level and ignore the implementation details for the most part.