All Things Mobile

The Latest News & Knowledge

Convert SQL Statement to an NSPredicate for use with Core Data

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];



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;

  }


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>