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.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.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
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
, orSet
), 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