核心数据：是否可以在组by中使用自定义函数 [英] Core Data: Is it possible to use custom function in group by

问题描述

当在objective-c中创建NSFetchRequest时，是否可以逐个使用自定义函数（strftime）？ sql语句在sqlite中完全有效：

 从注释组中选择date，count（*）strftime但是，经过一天的挖掘和尝试各种方法和解决方案后，我会在一天之内尝试各种方法和解决方案。得出结论，它是不可能做到与核心数据在单个sql fetch。我使用NSFetchRequest与setPropertiesToGroupBy（仅iOS 5.0）。 
 
 
 
 UPDATE。有一个示例用于获取这种行为与核心数据由苹果提供。但是，我已启用-com.apple.CoreData.SQLDebug 1显示由核心数据执行的SQL。看来，核心数据没有执行我所需的SQL。它只是用第一个sql查询选择它们，然后循环通过执行多个SELEC .. WHERE ID IN ....SQL查询的组。从控制台摘录：
  2012-07-05 10：34：57.244 DateSectionTitles [1139：fb03] CoreData：sql：SELECT 0，t0.Z_PK从ZEVENT t0 ORDER BY t0.ZTIMESTAMP 
 2012-07-05 10：34：57.245 DateSectionTitles [1139：fb03] CoreData：注释：sql连接获取时间：0.0010s 
 2012- 07-05 10：34：57.246 DateSectionTitles [1139：fb03] CoreData：注释：总获取执行时间：52行为0.0015s。 
 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：注释：sql连接fetch时间：0.0012s 
 2012-07-05 10：34：57.249 DateSectionTitles [1139：fb03] ：注释：总提取执行时间：0.0022s为20行。 
 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：注释：sql连接提取时间：0.0303s 
 2012-07-05 10：34：57.281 DateSectionTitles [1139：fb03] ：注释：总提取执行时间：0.0310s为20行。 
  
 
 
解决方案
组装 NSFetchedResultsController 类似于：
  NSFetchedResultsController * aFetchedResultsController = 
 [[NSFetchedResultsController alloc] initWithFetchRequest：fetchRequest 
 managedObjectContext：someManagedObjectContext 
 sectionNameKeyPath：@yearOnly//< == 
 cacheName：@cacheAsneeded]; 
 
然后在NSManagedObject类中添加一个方法，用于 sectionNameKeyPath  
   - （int）yearOnly {// 
 NSCalendar * cal = [NSCalendar currentCalendar]; 
 return [[cal components：NSYearCalendarUnit fromDate：self.date] year]; 
} 
  
请求将返回按年份分组的结果，成为一个部分。 
 
 
 
如果您还需要按月份进行分组，则可能需要适当更改日历组件。
 
 
 
计数（*）向您的fetchRequest添加 NSExpression ：
  NSExpression * many = [NSExpression expressionForFunction：@count：arguments：[NSArray arrayWithObject：[NSExpression expressionForKeyPath：@someAttribute]]]; 
  
 
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.

解决方案
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"]]];


                        
这篇关于核心数据：是否可以在组by中使用自定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！