قالب وردپرس درنا توس
Home / IOS Development / Group by, Count and Sum in CoreData

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. Og siden jeg gjorde alt i Swift 3 disse dagene, kunne jeg ikke finde nogen passende eksempel på nettet. Men jeg endelig figured it out.

The biggest change in CoreData – as of Swift 3 – was that fetch requests are now generics that return an array of the generic type. The model editor auto-generates the NSManagedObject sub-classes for us.

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 become quite simple:

 let fetch: NSFetchRequest = Record.fetchRequest ()
do {
   easy 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 zou u de query voor dat als volgt vinden:

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

It's a big more involved to achieve the same effect with CoreData. Until now I did not even know that this was possible nowadays. .

First the expression … count (*) and then specify the key (s) to group by . 1

9659004] easy keypathExp = NSExpression (forKeyPath: "col") // can be any column
light 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.

The second part generates an expression description to give the result a name and result type.

Now the request request that also groups …

 easy 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.

Invalid fetch request: GROUP BY requires NSDictionaryResultType

The result is still an array, but with a dictionary for each row of the result. 19659004] [
{
“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 refer to the column you want to summarize.

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

Note that here I did not 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. I det samme prosjektet, brukte jeg maks to hente den nyeste datoen fra alle rader eller en enhet. As with other fetch requests, you can also add a black 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.

At the time of this writing I am still trying to figure out if there is a way to Reducer 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




Source link