NSFetchRequest: Query for distinct values and count other properties values

Question

I try to set up an NSFetchRequest for an entity Location with properties like countryand city:

country  | city
————————————————
Germany  | Berlin
USA      | San Francisco
USA      | New York
Germany  | Munich
Germany  | Munich
USA      | San Francisco
Germany  | Stuttgart

The NSFetchRequest should return the country (or a Location object with the appropriate country) and the number of cities.

[
    { country: 'Germany', cityCount: 3 },
    { country: 'USA', cityCount: 2 }
]

I know that I can just fetch all entries and 'count it myself' but I am interested in how to set up an appropriate fetch request (or if it's possible to do so) and would like to see how you would do it! :)

This is certainly possible. Are you using Swift or Objective-C? — pbasdf, Aug 29 '15 at 11:04
I spoke too soon; it is not easy to achieve the "distinct" requirement whilst also using "group by" to count. Perhaps that's why you asked. — pbasdf, Aug 29 '15 at 12:11

score 0 · Answer 1 · answered Aug 29 '15 at 17:42

I had to resort to two separate fetches in order to achieve (I think) what you want. The first fetch gets objectIDs for one object for each distinct combination of country and city. The second fetch is filtered, using an IN predicate, to just these objects. It uses NSExpression and propertiesToGroupBy to get the counts for each country:

    // Step 1, get the object IDs for one object for each distinct country and city 
    var objIDExp = NSExpression(expressionType: NSExpressionType.EvaluatedObjectExpressionType)
    var objIDED = NSExpressionDescription()
    objIDED.expression = objIDExp
    objIDED.expressionResultType = .ObjectIDAttributeType
    objIDED.name = "objID"
    var fetch = NSFetchRequest(entityName: "Location")
    fetch.propertiesToFetch = [objIDED]
    fetch.propertiesToGroupBy = ["country", "city"]
    fetch.resultType = .DictionaryResultType
    let results = self.managedObjectContext!.executeFetchRequest(fetch, error: nil)

    // extract the objectIDs into an array...
    let objIDArray = (results! as NSArray).valueForKey("objID") as! [NSManagedObjectID];

    // Step 2, count using GROUP BY
    var countExp = NSExpression(format: "count:(SELF)")
    var countED = NSExpressionDescription()
    countED.expression = countExp
    countED.expressionResultType = .ObjectIDAttributeType
    countED.name = "count"
    var newFetch = NSFetchRequest(entityName: "Location")
    newFetch.predicate = NSPredicate(format: "SELF IN %@", objIDArray)
    newFetch.propertiesToFetch = ["country", countED]
    newFetch.propertiesToGroupBy = ["country"]
    newFetch.resultType = .DictionaryResultType
    let newResults = self.managedObjectContext!.executeFetchRequest(newFetch, error: nil)
    println("\(newResults!)")

This will be inefficient: if you have a large number of distinct countries and cities, the IN predicate will slow things down. You might find it's more efficient to fetch everything and count them.

I have never used the IN predicate before, thats indeed a pretty cool approach. I hoped it would be possible within one NSFetchRequest... . If there isn't any other solution during the next week, I will accept your answer! Thank your very much! :) — ItsTCalling, Aug 29 '15 at 19:15
This solution is longer and less efficient than fetching and parsing in memory using KVC. — Mundi, Aug 29 '15 at 21:43

score 0 · Accepted Answer · answered Aug 29 '15 at 21:42

The correct answer to this question to refactor the data model in order to avoid redundancy.

The country strings are repeated unnecessarily in the table. Additionally, you make a simple query gratuitously complicated. The model should reflect your data, and writing out "USA" for every American city is just not smart or efficient.

Your data model should look like this

Country <----->> City

Now you can just fetch all countries and get the cities with cities.count.

I endet up using this approach... But sometimes you don't have this opportunity ;) — ItsTCalling, Sep 04 '15 at 07:14

NSFetchRequest: Query for distinct values and count other properties values

2 Answers2