AppWorks By Rade | Eccles

OBJECTIVE

Translate a SQL statement with multiple conditions into an NSPredicate for use with Core Data

BACKGROUND

The Rade | Eccles team is hard at work to release another fantastic financial utility app very soon that EVERYONE who spends money can use.  This app is using Core Data to manage the data store.  As a developer I have been using SQL for roughly 20 years.  SQL and databases are second nature to me.  Core Data has a fantastic ability to abstract out the complexities of dealing with a specific database product or even the use of raw SQL statements which is great for those who don’t have much database experience.

But I found myself, as many others who have used Core Data, needing to set aside my database and SQL knowledge and learn how to perform what would be a simple SQL statement using a Core Data NSPredicate. In other words, an old dog needed to learn a new trick.

After reviewing the documentation provided by Apple, looking at some sample programs, and searching StackOverflow.com I still couldn’t quite find what I was looking for.  Ultimately I ended up doing what any developer does when a problem presents itself which needs to be resolved… I dove in and used the trial and error method until I figured out what I needed.

I needed to build a Core Data predicate with multiple conditions.  If I were using SQL I would have written something like the following (pseudo-SQL):

SELECT * FROM TRANSACTIONS
WHERE CATEGORY IN (categoryList)
AND LOCATION IN (locationList)
AND TYPE IN (typeList)
AND NOTE contains[cd] "some text"
AND DATE >= fromDate
AND DATE <= toDate

IMPLEMENTATION

The first concept to understand was that I needed to build each condition as its own NSPredicate then use NSCompoundPredicate and pass it an array of all the individual predicates for each condition.  Since all of my conditions are AND conditions I can use
the following statement to build a single NSPredicate from multiple predicates:

predicate = [NSCompoundPredicate andPredicateWithSubpredicates:predicates];

It was obvious how I needed to create the date range condition and “Note” text field condition.  What took a bit more research was using the IN clause to specify a list of values to be substituted.  I found the Apple documentation a bit misleading in this area. In the end it turned out to be quite simple.  I could use the IN clause in the following manner:

predicate = [NSPredicate predicateWithFormat:@"Category.Name IN %@", reportCategories];

In this case reportCategories is simply an array of string values.

SOLUTION

When I put all this together I ended up with a method like this:

NOTE: In this example Category, Location, and Type are Core Data entities
with an attribute called Name.  The resulting predicate is used against a
Core Data entity called Transactions which contains:
NSDate's for both the "fromDate" and "toDate" attributes
Relationships to the Category, Location, and Type entities
An NSString for the "note" attribute.

- (NSPredicate *)buildCustomReportPredicate {
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
 NSString *documentsDirectory = [paths objectAtIndex:0]; // Get documents folder

 NSMutableArray *predicates = [[NSMutableArray alloc] initWithCapacity:5];

 // date predicate
 NSPredicate *predicate = [NSPredicate predicateWithFormat:@"(Date >= %@) AND (Date <= %@)", self.periodFromDate, self.periodToDate];
 [predicates addObject:predicate];

 // categories predicate
 NSMutableArray *reportCategories = [NSMutableArray arrayWithContentsOfFile:[documentsDirectory stringByAppendingPathComponent:@"ReportCategories"]];
 if ([reportCategories count] > 0) {
 predicate = [NSPredicate predicateWithFormat:@"Category.Name IN %@", reportCategories];
 [predicates addObject:predicate];
 }

 // locations predicate
 NSMutableArray *reportLocations = [NSMutableArray arrayWithContentsOfFile:[documentsDirectory stringByAppendingPathComponent:@"ReportLocations"]];
 if ([reportLocations count] > 0) {
 predicate = [NSPredicate predicateWithFormat:@"Location.Name IN %@", reportLocations];
 [predicates addObject:predicate];
 }

 // types predicate
 NSMutableArray *reportTypes = [NSMutableArray arrayWithContentsOfFile:[documentsDirectory stringByAppendingPathComponent:@"ReportTypes"]];
 if ([reportTypes count] > 0) {
 predicate = [NSPredicate predicateWithFormat:@"Type.Name IN %@", reportTypes];
 [predicates addObject:predicate];
 }

 // note predicate
 NSString *note = [[NSUserDefaults standardUserDefaults] objectForKey:@"ReportNote"];
 if (![note isEqualToString:@""]) {
 predicate = [NSPredicate predicateWithFormat:@"Note contains[cd] %@", note];
 [predicates addObject:predicate];
 }

 // build the compound predicate
 predicate = [NSCompoundPredicate andPredicateWithSubpredicates:predicates];

 [predicates release];
 return predicate;

}