SOQL: Count number of child records

  • This is a basic SOQL question. I have a number of Application (custom) objects that are children to the User (standard) object.

    I want to count the number of Applications associated with each User.

    Looking at the documentation I can get a query like this to work:

    SELECT Name, (SELECT id FROM Applications__r) FROM User
    

    But how do I get a count associated with each User?

  • You could use your current query and get the size attribute of the list after the query finishes:

    List<User> users = [SELECT Name, (SELECT id FROM Applications__r) FROM User];
    for (User u : users) {
        // do something with it...
        u.Applications__r.size();
    }
    

    Alternatively, you can use an AggregateResult query which would query the child and group by the User Id lookup. This would not retrieve any information about Users that don't have any Applications, though. Also, if the User field is not required, all Applications that don't have a User would be put in the grouping where User__c is null.

    // Assumes you have a Lookup called User__c
    Map<Id, Integer> userAppCountMap = new Map<Id, Integer>();
    for (AggregateResult aggRes : [
          SELECT COUNT(ID) numApps, User__c userId
          FROM Application__c
          GROUP BY User__c
    ]) {
        Id userId = (Id) aggRes.get('userId');
        Integer numApps = (Integer) aggRes.get('numApps');
        userAppCountMap.put(userId, numApps);
    }
    

    Your first example doesn't capture the fact that some users might have 0 cases assigned. But the second one works great.

    Do not use 1st one unless you're sure the numbers will be low. For bigger cases the inner query starts to lie and limit itself to say 200 rows when the real amount can be bigger. It's bit similar to how related list by default show first 5 records and you need to explicitly click to get more.

    Hmm. There probably will be more than 200 applications. Is there any documentation for what you're describing?

    From http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_gov_limits.htm "The row counts from these relationship queries contribute to the row counts of the overall code execution."

    What if I want the query to return count of Applications against each "user name" in an Ascending/Descending order?

    @PeterKnolle in the aggregate query, can you return the aggregate count that is greater than say 1?

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM

Tags used