[SalesForce] Returning COUNT() From SOQL Sub-Query

I have this SOQL statement:

SELECT Id, Name, (SELECT AssigneeId, Assignee.Name FROM Assignments)
FROM PermissionSet 
WHERE Id = (an id here)

I want to return the count of the results in (SELECT AssigneeId, Assignee.Name FROM Assignments). The results returned looks to be in JSON format.

I have tried doing

SELECT Id, Name, COUNT((SELECT AssigneeId, Assignee.Name FROM Assignments))
FROM PermissionSet 
WHERE Id = (an id here)

but that doesn't work. Is there a way to restructure this query or do I have to write Apex to parse through the JSON?

Best Answer

A parent-child subquery (also called a left outer join) that you're performing ends up returning a List<Assignment>, not JSON. It only looks like JSON when you print it with a debug statement.

for(PermissionSet permSet :[<your query here>]){
   // each permission set record has an embedded List<PermissionSetAssignment> with 
   //   your query.
   // Because that's true, the following line will compile
   List<PermissionSetAssignment> children = permSet.Assignments;
}

You'd see the same thing if you tried to debug any other List.

The nice thing about having the subquery return a List is that you can simply call .size() on it to get a count of the records.

// number of child records is simply the size of the list
permSet.Assignments.size();

The one gotcha to look out for is that after a certain, ill-defined, threshold (in number of child records), you may run into the following error

Aggregate Query has too many rows for direct assignment, use FOR loop

In that case, like the error says, you'd need to use a loop to iterate over all the child records for a given parent record. To get the number of child records in this case, you'd need to either increment some variable inside the loop, or store the child records in a collection (List, Map, or Set), and you wouldn't be able to get the count until after that loop finishes.

Some example code of how to handle that situation would be

for(PermissionSet permSet :[<your query here>]){
    // Pretend that we'd get an error if we try to access permSet.Assignments directly

    // Declare a list to hold the children
    // This list is re-created on every loop iteration (so Assignments for one
    //   PermissionSet won't be counted towards the number of Assignments for the next
    //   PermissionSet)
    // If you need to keep the PermissionSetAssignment records for all PermissionSets,
    //   then you'd want to use a Map<Id, List<PermissionSetAssignment>> (keyed 
    //   on the Id of the PermissionSet), and you'd want to declare it outside of all loops.
    List<PermissionSetAssignment> children = new List<PermissionSetAssignment>();

    for(PermissionSetAssignment permSetAssign : permSet.Assignments){
        // Add the children to the list, one at a time
        children.add(permSetAssign);
    }

    // Now that the (inner) loop is done, we can see how many children there are
    system.debug('number of assignments: ' + children.size());
}
Related Topic