Core Data complex query

Question

I have model that have field date. I need to fetch all count of records for all day of year or if no record for this day presented fetch as 0. Now i fetch all record for year and make it by hands. Can I do it using core data queries?

Answer 1

Typically in Core Data it is much faster to do all the fetching you will need to do up front rather than breaking it up into multiple fetches.

I tried to count records by day of the year in two ways:

1. Fetch all the records for a year. Find distinct dates for those records. Loop over the dates and count the records.

2. Loop over each day in the year. Perform a count-only fetch for the records with that date.

With a small database (48 records), the first approach was about 90 times faster. I imagine the performance of the first approach would get worse as more records are added and the second approach would stay about the same.

Here is the code:

- (void)doFullLoadTestWithYear:(int)year {
 NSFetchRequest *fetchRequest = [[NSFetchRequest alloc] init];

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

 NSCalendar *gregorian = [[[NSCalendar alloc] initWithCalendarIdentifier:NSGregorianCalendar] autorelease];
 NSDateComponents *comps = [[[NSDateComponents alloc] init] autorelease];
 [comps setDay:1];
 [comps setMonth:1];
 [comps setYear:year];
 NSDate *start = [gregorian dateFromComponents:comps];
 [comps setDay:31];
 [comps setMonth:12];
 NSDate *end = [gregorian dateFromComponents:comps];

 NSPredicate *yearPred = [NSPredicate predicateWithFormat:@"date >= %@ && date <= %@",start,end];
 [fetchRequest setPredicate:yearPred];

 NSError *error = nil;
 NSArray *array = [self.managedObjectContext executeFetchRequest:fetchRequest error:&error];
 [fetchRequest release];
 if (error) {
  NSLog(@"Error during fetch: %@",[error localizedDescription]);
  abort();
 }
 int dateCount = 0, recordCount = 0;

 NSArray *dates = [array valueForKeyPath:@"@distinctUnionOfObjects.date"];

 for (NSDate *date in dates) {
  NSPredicate *pred = [NSPredicate predicateWithFormat:@"date == %@",date];

  NSArray *records = [array filteredArrayUsingPredicate:pred];

  dateCount++;
  recordCount += [records count];

  NSLog(@"%d record(s) with date %@",[records count],date);
 }

 NSLog(@"Record count for year is %d",recordCount);
 NSLog(@"Distinct dates count is %d",dateCount);
}

- (void)doSeparateTestWithYear:(int)year {
 NSFetchRequest *fetchRequest = [[NSFetchRequest alloc] init];

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

 NSCalendar *gregorian = [[[NSCalendar alloc] initWithCalendarIdentifier:NSGregorianCalendar] autorelease];
 NSDateComponents *comps = [[[NSDateComponents alloc] init] autorelease];
 [comps setDay:1];
 [comps setMonth:1];
 [comps setYear:year];
 NSDate *start = [gregorian dateFromComponents:comps];
 [comps setYear:year+1];
 NSDate *end = [gregorian dateFromComponents:comps];

 NSDateComponents *oneDay = [[[NSDateComponents alloc] init] autorelease];
 [oneDay setDay:1];

 int dateCount = 0, recordCount = 0;

 NSDate *date = start;
 while (![date isEqual:end]) {
  NSPredicate *dayPred = [NSPredicate predicateWithFormat:@"date == %@",date];
  [fetchRequest setPredicate:dayPred];

  NSError *error = nil;
  int count = [self.managedObjectContext countForFetchRequest:fetchRequest error:&error];
  if (error) {
   NSLog(@"Error during fetch: %@",[error localizedDescription]);
   abort();
  }

  if (count > 0) {
   NSLog(@"%d record(s) with date %@",count,date);
   dateCount++;
   recordCount += count;
  }

  date = [gregorian dateByAddingComponents:oneDay toDate:date options:0];
 }

 NSLog(@"Record count for year is %d",recordCount);
 NSLog(@"Distinct dates count is %d",dateCount);

 [fetchRequest release];
}