Master data: is it possible to use a custom function in a group by

Question

Is it possible to use a custom function (strftime) individually when creating an NSFetchRequest in objective-c? The sql statements are 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 methods and solutions, I came to the conclusion that it is impossible to do this using the main data in one sql-selection. I am using NSFetchRequest with setPropertiesToGroupBy (iOS 5.0 only).

UPDATE There is an example to get this behavior with basic data provided by apple. However, I included "-com.apple.CoreData.SQLDebug 1" to display the SQL executed by the master data. It seems that the underlying data is not fulfilling my desired SQL. It simply selects all of them with the first sql query, and then iterates over the groups that execute multiple "SELEC .. WHERE ID IN ...." SQL queries. Excerpt from the 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. Build 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 query will return results grouped by year, so that every year encountered will become a section.

If you need monthly grouping, you can change the calendar components accordingly.

For count (*) add NSExpression to your fetchRequest:

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