NSPredicate something equivalent of SQL's GROUP BY

Question

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 1

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.

Answer 2

Assuming you're using CoreData... according to the Apple Documentation

You cannot necessarily translate “arbitrary” SQL queries into predicates or fetch requests. There is no way, for example, to convert a SQL statement such as

SELECT t1.name, V1, V2
    FROM table1 t1 JOIN (SELECT t2.name AS V1, count(*) AS V2
        FROM table2 t2 GROUP BY t2.name as V) on t1.name = V.V1

into a fetch request. You must fetch the objects of interest, then either perform a calculation directly using the results, or use an array operator.

If you need to do complex queries like this, you might be better using SQLite.