Group by, Count and Sum in CoreData

For a tvOS app I needed to efficiently group by a certain column and then count the resulting rows or sum the value. And since I do everything in Swift 3 these days, I couldn’t find any suitable example on line. But I eventually figured it out.

The biggest change in CoreData – as of Swift 3 – was that fetch requests are now generics which return an array of the generic type. The model editor auto-generates the NSManagedObject sub-classes for us. We don’t have to add any code for that to happen.

The auto-generated code includes a function for a typed fetch request, as in this example:

extension Record {

    @nonobjc public class func fetchRequest() -> NSFetchRequest {
        return NSFetchRequest(entityName: "Record")
    }
}

So querying all Record instances that are currently in the database becomes quite simple:

let fetch: NSFetchRequest = Record.fetchRequest()
do {
   let results = try fetch.execute()
   // results is now an array: [Record]
catch {
   NSLog("Error fetching entity: %@", error.localizedDescription)
}

Note that we have to specify the generic type to be used with the fetch request or else you get an error message that the fetchRequest function is ambiguous.

Group by and Count

I needed to group my records by a certain column and count the records for each of the column’s values. In SQL you would the query for that like this:

SELECT col, count(*) from Record GROUP BY col

It’s a big more involved to achieve the same effect with CoreData. Until now I didn’t even know that this was possible nowadays. The two key parts are that you have to formulate an expression for the count(*) and then specify the key(s) to group by.

First the expression…

let keypathExp = NSExpression(forKeyPath: "col") // can be any column
let expression = NSExpression(forFunction: "count:", arguments: [keypathExp])

let countDesc = NSExpressionDescription()
countDesc.expression = expression
countDesc.name = "count"
countDesc.expressionResultType = .integer64AttributeType

For the purpose of counting records in each group, it does not matter which key path you specify for the key path expression. But you need to have at least one parameter in the arguments array, or else you get an exception.

The second part generates an expression description to give the result a name and result type. This we will use then specifying the columns/expression to return from the grouping.

Now the fetch request that also groups…

let request = NSFetchRequest(entityName: "Record")
request.returnsObjectsAsFaults = false
request.propertiesToGroupBy = ["col"]
request.propertiesToFetch = ["col", countDesc]
request.resultType = .dictionaryResultType

So we are fetching from Record. We want the result to be returned as NSDictionary. We are grouping by “col” and the properties to output in the dictionary should be col and the count. The result type must to be a dictionary, or else you will get an exception:

Invalid fetch request: GROUP BY requires NSDictionaryResultType

The result is still an array, but with a dictionary for each row of the result.

[
   {
      "count": 111,
      "col": "One"
   },
   {
      "count": 222,
      "col": "Two"
   }
]

The exercise to convert that into a single dictionary is left to the reader. But please share your solution to me on Twitter!

Just a Sum

To get a sum of a column’s values instead of the count, you take exactly the same code, but substitute sum: for count:. This time though, the expression must be referencing the column you want to sum up.

let keypathExp1 = NSExpression(forKeyPath: "col")
let expression = NSExpression(forFunction: "sum:", arguments: [keypathExp1])
let sumDesc = NSExpressionDescription()
sumDesc.expression = expression
sumDesc.name = "sum"
sumDesc.expressionResultType = .integer64AttributeType
                
let request = NSFetchRequest(entityName: "Record")
request.returnsObjectsAsFaults = false
request.propertiesToFetch = [sumDesc]
request.resultType = .dictionaryResultType

Note that here I didn’t group, the sum is over the entire set of rows in the CoreData DB. But grouping could easily be added by adding the propertiesToGroupBy.

Conclusion

Besides the grouping functions count and sum there are a few more that might come in handy: min, max and avg. In the same project, I used max to retrieve the latest date from all rows of an entity. As with other fetch requests you can also add a sort descriptor and a predicate to filter.

The functionality to perform such aggregate operations must have been existing in iOS for a very very long time. The facility to group by properties exists since iOS 5, expressions even since iOS 3. This makes me wonder why few people know about it.

At the time of this writing I am still trying to figure out if there is a way to reduce the amount of code for each such aggregation fetch request. If you can think of any, please let me know. In any case: Happy Grouping!

Also published on Medium.

Categories: Recipes