0

how can i translate the following sqlite query to core data

select name, firstname, class, telephone, entryDateTime, counselor, count() 
  from myTable 
  group by strftime('yy-mm-dd', entryDateTime), name, class, counselor 
  order by entryDateTime desc;

what did i try?

i want to group by date(yy-mm-dd), class, counselor, and name. entryDateTime is in UTC but it should be converted to local time.

thanks in advance

Community
  • 1
  • 1
Hashmat Khalil
  • 1,826
  • 1
  • 25
  • 49

1 Answers1

1

The most flexible method for "group by"-like queries is the NSFetchedResultsController. Please see my answer of a couple of days ago, which should help you.

To solve the problem of subgrouping based on more than one attribute, you just use the sectionNameKeyPath as a subgroup and do the other grouping from there after fetching.

Alternatively, consider putting the attributes you need into a different entity and group by that entity.

Community
  • 1
  • 1
Mundi
  • 79,884
  • 17
  • 117
  • 140
  • i saw your answer already. i need to group on more than one attribute and use count aggregate. i wont be using sections. but i should count and group by more than one attribute and sort desc on date(yy-mm-dd). how can i achieve that with nsfetchcontroller? – Hashmat Khalil Jun 06 '13 at 06:57
  • You could group by the first attribute (the date) and then deal with the subgrouping when you need it. I am also suggesting another alternative in my edited answer. – Mundi Jun 06 '13 at 11:55
  • well i was thinking about sectionNameKeyPath for date with transient property for yy-mm-dd. but i wonder how to group and count other attributes on that result. any clue? – Hashmat Khalil Jun 06 '13 at 12:04
  • One interesting avenue is to explore the option of using `NSDictionaryResultType` for your fetch request and trying to tweak the predicate with the request's `propertiesToGroupBy` attribute. Get ready for `NSExpression`, `NSExpressionDescription` and similarly verbose APIs. – Mundi Jun 06 '13 at 14:12
  • if you have checked the link in my question. i was on that path. converting date from utc to local prevented the whole process. – Hashmat Khalil Jun 07 '13 at 09:42