2

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?

  • Are you working in Apex, or some other language (like Java or PHP) that issues queries through one of the Salesforce APIs? – Derek F Mar 16 '17 at 16:39
  • @DerekF I'm in Apex. – akcorp2003 Mar 16 '17 at 17:00
2

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());
}
| improve this answer | |
  • Hm. I tried querying it in anonymous Apex List<PermissionSet> stuff = [that query] and when I returned the size of the list, it only has 1. – akcorp2003 Mar 16 '17 at 16:57
  • @akcorp2003 If you're querying the PermissionSet using an Id, then of course you'd only get a maximum of 1 row returned. The subquery for Assignment itself is a List<Assignment> embedded into each returned PermissionSet. To get the number of assignments, you'd do suff[0].Assignments.size() – Derek F Mar 16 '17 at 17:00
  • Yup! I got the same result as Adrian Larson's answer. After my SOQL statement, I just had stuff[0].Assignments.size(). – akcorp2003 Mar 16 '17 at 17:07
  • So when you say that there is that error, will I have to iterate through the Assignments and internally increment a counter to keep track of the number of records? – akcorp2003 Mar 16 '17 at 17:10
  • @akcorp2003 Yep, or add each PermissionSetAssignment record to a separate List so you could call .size() on it after the loop finishes. I'll edit my answer, adding some example code. – Derek F Mar 16 '17 at 17:13
4

If what you want could be done, the correct syntax would be:

SELECT Id, Name, (SELECT count() FROM Assignments)
FROM PermissionSet 
WHERE Id = <some_id>

But it's not supported. You'll see this error:

COUNT() can only be used with root queries

However, you could use an aggregate query here, perhaps:

SELECT PermissionSetId, PermissionSet.Name, count(Id) records
FROM PermissionSetAssignment
WHERE PermissionSetId = '<some_id>'
GROUP BY PermissionSetId, PermissionSet.Name

It will serialize slightly differently, but seems like it should give you what you are looking for. If you add some field aliases, it won't be too dissimilar:

system.debug(JSON.serialize([
    SELECT PermissionSetId Id, PermissionSet.Name Name, count(Id) Assignments
    FROM PermissionSetAssignment
    WHERE PermissionSetId = '<some_id>'
    GROUP BY PermissionSetId, PermissionSet.Name
]));
// yields:
[{
    "attributes": {
        "type": "AggregateResult",
        "url": "/services/data/v39.0/sobjects/AggregateResult/<some_id>"
    },
    "Id": "<some_id>",
    "Name": "Permission_Set_Name",
    "Assignments": 2
}]
| improve this answer | |
  • Yup! The SOQL query returns an accurate number. However, the JSON might be a little hairy to parse through. Will this query encounter an error like what DerekF mentioned? – akcorp2003 Mar 16 '17 at 17:12
  • 1
    @akcorp If you have more than 50k assignments, you will hit errors, but no, not the child record count error. You should be able to ignore the "attributes" property, for the most part, and any query data you serialize will have that. – Adrian Larson Mar 16 '17 at 17:14
1

I think following query will serve your purpose

SELECT COUNT(AssigneeId), PermissionSetId 
FROM PermissionSetAssignment
WHERE PermissionSetId IN: permissionSetIds
GROUP BY PermissionSetId

For more information, refer PermissionSetAssignment

| improve this answer | |
0

You can use COUNT(Id) and GROUP BY to group the counts. No need to use a sub-query either, if I'm understanding what you're wanting.

You can get data from related objects using the relationship field name with the __r. followed by the related object field you want.

I'm not sure what your data structure is (my org doesn't use Assignments or anything) but here's an example SOQL from my org (which is an academic institution):

SELECT Interest_Setup_Record__r.Name, Count(Id) FROM Academic_Plan__c 
    WHERE Interest_Setup_Record__r.Degree_Level__c = 'Masters' 
    GROUP BY Interest_Setup_Record__r.Name

This SOQL gets counts for Application Plans for every academic major at the Masters degree level in our institution and displays them grouped by the academic major.

The output looks something like this:

Name               | count(Id)
Accounting MBA/MS  |   9 
Anthropology MA/MS |   4
Biology MA/MS      |  12

So Biology MA/MS has 12 different academic plan options for students, and so on.

Is that in line with that you're looking to achieve?

| improve this answer | |
  • Yes. This is pretty much what I'm looking for. The example SOQL query is something I took from a Salesforce tutorial on PermissionSets. – akcorp2003 Mar 16 '17 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.