Grouping a dictionary NSFetchRequest by object ID

Question

I need to return a list of objects along with a count of its related objects. It doesn't seem to be possible to do this in a single dictionary fetch request as I am unable to group the fetch results by objectID.

let objectIDExpression = NSExpressionDescription()
objectIDExpression.name = "objectID"
objectIDExpression.expression = NSExpression.expressionForEvaluatedObject()
objectIDExpression.expressionResultType = NSAttributeType.ObjectIDAttributeType

let countExpression = NSExpressionDescription()
countExpression.name = "count"
countExpression.expression = NSExpression(forFunction: "count:", arguments: [NSExpression(forKeyPath: "entries")])
countExpression.expressionResultType = .Integer32AttributeType

let fetchRequest = NSFetchRequest(entityName: "Tag")
fetchRequest.resultType = .DictionaryResultType
fetchRequest.propertiesToFetch = [objectIDExpression, countExpression]
fetchRequest.propertiesToGroupBy = [objectIDExpression]

var error: NSError?
if let results = self.context.executeFetchRequest(fetchRequest, error: &error) {
    println(results)
}

When this request executes it errors with:

'Invalid keypath expression ((<NSExpressionDescription: 0x7f843bf2d470>), name objectID, isOptional 1, isTransient 0, entity (null), renamingIdentifier objectID, validation predicates (
), warnings (
), versionHashModifier (null)
 userInfo {
}) passed to setPropertiesToFetch:'

I also tested just passing the "objectID" expression name, but that also fails.

Is there therefore no way to group by object ID?

Answer 1

You can get the required count without using propertiesToGroupBy. CoreData seems to infer the correct scope for the count and uses a sub-SELECT instead (strangely, only if the fetch includes an attribute as well as the objectID and count, see below). For example, I have Tag many-many with Items:

First attempt

I can fetch tagName and the count of items as follows:

NSFetchRequest *fetch = [NSFetchRequest fetchRequestWithEntityName:@"Tag"];
NSExpressionDescription *countED = [NSExpressionDescription new];
countED.expression = [NSExpression expressionWithFormat:@"count:(items)"];
countED.name = @"countOfItems";
countED.expressionResultType = NSInteger64AttributeType;
fetch.resultType = NSDictionaryResultType;
fetch.propertiesToFetch = @[@"tagName", countED];
NSArray *results = [self.context executeFetchRequest:fetch error:nil];
NSLog(@"results is %@", results);

which generates the following SQL:

SELECT t0.ZTAGNAME, (SELECT COUNT(t1.Z_3ITEMS) FROM Z_3TAGS t1 WHERE (t0.Z_PK = t1.Z_8TAGS) ) FROM ZTAG t0

Second attempt

Sadly, it seems CoreData gets confused if I try to select the objectID instead of the tagName:

NSExpressionDescription *selfED = [NSExpressionDescription new];
selfED.expression = [NSExpression expressionForEvaluatedObject];
selfED.name = @"self";
selfED.expressionResultType = NSObjectIDAttributeType;
fetch.resultType = NSDictionaryResultType;
fetch.propertiesToFetch = @[selfED, countED];

generates this SQL:

SELECT t0.Z_ENT, t0.Z_PK, COUNT( t1.Z_3ITEMS) FROM ZTAG t0 LEFT OUTER JOIN Z_3TAGS t1 ON t0.Z_PK = t1.Z_8TAGS

which counts all the rows from the outer join (and suggests that you need to group by the objectID, though we know that won't work).

Final attempt

However, include tagName and objectID, and all is well again:

fetch.propertiesToFetch = @[selfED, @"tagName", countED];

gives:

SELECT t0.Z_ENT, t0.Z_PK, t0.ZTAGNAME, (SELECT COUNT(t1.Z_3ITEMS) FROM Z_3TAGS t1 WHERE (t0.Z_PK = t1.Z_8TAGS) ) FROM ZTAG t0

which seems to do the trick. (Sorry for reverting to Objective-C, and for using different entity/attribute names, but I'm sure you get the picture).

Aside

One other curiosity I discovered is that the second attempt above can also be made to work by counting an attribute of the relationship, rather than the relationship itself:

countED.expression = [NSExpression expressionWithFormat:@"count:(items.itemName)"];
fetch.propertiesToFetch = @[selfED, countED];

gives:

SELECT t0.Z_ENT, t0.Z_PK, (SELECT COUNT(t2.ZITEMNAME) FROM Z_3TAGS t1 JOIN ZITEMS t2 ON t1.Z_3ITEMS = t2.Z_PK WHERE (t0.Z_PK = t1.Z_8TAGS) ) FROM ZTAG t0

which will (I think) give the correct counts provided itemName is not nil.

Answer 2

It is perhaps easier to use a NSFetchedResultsController. You can set the sectionNameKeyPath to group and use the resulting NSIndexPaths to construct your dictionary.

That being said, I do not think that it makes any sense to group by objectID because every object ID is by definition unique. So there will be one instance in each group. This is likely why setting propertiesToGroupBy fails.

So, short answer: no.

E.g.

let fetchRequest = NSFetchRequest(entityName: "Tag")
var output = [(NSManagedObjectID, Int)]()
do {
   let results = try context.executeFetchRequest(request) as! [Tag]
   for tag in results { 
       output.append((tag.objectID, tag.entries.count))
   }
} catch {}
// output contains tuples with objectID and count

If entriesis optional, use tag.entries?.count ?? 0.

Answer 3

I played with this for a bit, sure there had to be some way to tell core-data to group by the primary key.

I couldn't figure it out, though I believe it to be possible.

The best I could do was add another unique attribute "uuid" (which I use for all of my entities anyway, for various reasons). You can do this easily enough with NSUUID, or you can take the permanent object ID URI representation and turn it into a string.

Anyway, I think this gives you what you want, but does so by requiring a separate unique attribute.

fetchRequest.propertiesToGroupBy = @[@"uuid"];

I tried a bunch of alternatives as the group-by property but expressionForEvaluatedObject always barfs, and other attempts fell flat.

I'm sure you know this already. Just in case, though it's at least a workaround, even if you don't use it for anything else, until someone comes around who has actually done this before.

FWIW, here is the SQL...

CoreData: sql: SELECT t0.Z_ENT, t0.Z_PK, COUNT( t1.Z_1ENTRIES), t0.ZUUID
    FROM ZTAG t0 LEFT OUTER JOIN Z_1TAGS t1 ON t0.Z_PK = t1.Z_2TAGS
    GROUP BY  t0.ZUUID

Surely, there has to be a way to tell it to substitute t0.Z_PK in the group-by clause. I would image that should be an easy special case for expressionForEvaluatedObject or "objectID" or "self" or "self.objectID"

Good luck, and please report back if you solve it. I'd be very interested.