Core Data: Is it possible to use custom function in group by

Question

Is it possible to use custom function (strftime) in group by part when crafting NSFetchRequest in objective-c? The sql statements is perfectly valid in sqlite:

select date, count(*) from note group by strftime('%Y%m%d', date)

However, after a day of digging and trying various ways and solutions I came to the conclusion it's impossible to do it with core data in single sql fetch. I'm using NSFetchRequest with setPropertiesToGroupBy (iOS 5.0 only).

UPDATE. There's an example for getting such behavior with core data provided by apple. However, I have enabled "-com.apple.CoreData.SQLDebug 1" for showing SQL performed by core data. It appears, core data is not performing my desired SQL. It just selects them all with a first sql query, and then loops through the groups performing multiple "SELEC .. WHERE ID IN ...." SQL queries. An excerpt from console:

2012-07-05 10:34:57.244 DateSectionTitles[1139:fb03] CoreData: sql: SELECT 0, t0.Z_PK     FROM ZEVENT t0 ORDER BY t0.ZTIMESTAMP
2012-07-05 10:34:57.245 DateSectionTitles[1139:fb03] CoreData: annotation: sql   connection fetch time: 0.0010s
2012-07-05 10:34:57.246 DateSectionTitles[1139:fb03] CoreData: annotation: total fetch    execution time: 0.0015s for 52 rows.
2012-07-05 10:34:57.247 DateSectionTitles[1139:fb03] CoreData: sql: SELECT 0, t0.Z_PK, t0.Z_OPT, t0.ZTIMESTAMP, t0.ZTITLE FROM ZEVENT t0 WHERE  t0.Z_PK IN  (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)  ORDER BY t0.ZTIMESTAMP LIMIT 20
2012-07-05 10:34:57.248 DateSectionTitles[1139:fb03] CoreData: annotation: sql connection fetch time: 0.0012s
2012-07-05 10:34:57.249 DateSectionTitles[1139:fb03] CoreData: annotation: total fetch execution time: 0.0022s for 20 rows.
2012-07-05 10:34:57.250 DateSectionTitles[1139:fb03] CoreData: sql: SELECT 0, t0.Z_PK, t0.Z_OPT, t0.ZTIMESTAMP, t0.ZTITLE FROM ZEVENT t0 WHERE  t0.Z_PK IN  (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)  ORDER BY t0.ZTIMESTAMP LIMIT 20
2012-07-05 10:34:57.281 DateSectionTitles[1139:fb03] CoreData: annotation: sql connection fetch time: 0.0303s
2012-07-05 10:34:57.281 DateSectionTitles[1139:fb03] CoreData: annotation: total fetch execution time: 0.0310s for 20 rows.

Answer 1

Yes you can. Assemble your NSFetchedResultsController similar to:

  NSFetchedResultsController *aFetchedResultsController =
  [[NSFetchedResultsController alloc] initWithFetchRequest:fetchRequest 
  managedObjectContext:someManagedObjectContext
  sectionNameKeyPath:@"yearOnly" // <== 
  cacheName:@"cacheAsneeded"];

Then inside your NSManagedObject class add a method to be used with sectionNameKeyPath

- (int)yearOnly { // 
   NSCalendar *cal = [NSCalendar currentCalendar];
   return [[cal components:NSYearCalendarUnit fromDate:self.date] year];
}

The request will return the results grouped by the year so that each encountered year will become a section.

If you need a grouping by month too, you might want to change the calendar components appropriately.

For the count(*) add an NSExpression to your fetchRequest:

 NSExpression * many = [NSExpression expressionForFunction:@"count:" arguments:[NSArray arrayWithObject:[NSExpression expressionForKeyPath:@"someAttribute"]]];