NSPredicate something equivalent of SQL's GROUP BY

Michael picture Michael · Jul 27, 2011 · Viewed 7.7k times · Source

To simplify: There are 3 columns in a table named cards.

id packTitle term

id is a column - integers from 0.....100

packTitle - string describing packs, lets say there are 3 kinds of pack PACK1, PACK2, PACK3

term - different unsorted names of 101 items.

by SQL statement

select packTitle from cards group by packTitle;

I can get list of 3 items.

Could you suggest NSPredicate equivalent of SQL statement GROUP BY. I need to get an array to populate in UITableView.

Answer

Barry Wark picture Barry Wark · Jul 27, 2011

CoreData is an object graph management framework, not a SQL data store. Thus, you should—as quickly as possible—get yourself out of the SQL mindset. NSPredicate is intended as a—no surprise—predicate for qualifying objects in the object graph. Thus you can fetch all the objects that match a query, but that is not the same as grouping those results. If you want to do aggregate operations, use Key-Value coding's collection operators. Under the hood, Core Data may convert these to sensible SQL, but that's exclusively an implementation detail.

In this case, you can get the set of unique packTitle values with

[myCards valueForKeyPath:@"@distinctUnionOfObjects.packTitle"];

which will give you the distinct set of packTitle values in the myCards collection. If you want this for all cards in the Core Data stores, you will have to run a query for all cards, then apply this collection operation.

The alternative is to make a PackInformation entity, or some such which contains a title property. You can then have only 3 of these entities in the data store, referencing them from the appropriate pack entities. It's trivial to fetch all three (or whatever the final number is) and get their titles.

As others have said, if what you're trying to do is fundamentally reporting from a relational data set, you may better off going with straight SQLite. It all depends on how much other benefit you get from Core Data.