How to iterate over query results containing a sub-collection of results

Question

I've added a custom object named Organisation_Stat which is a child of Account. An Account may have zero or more related Organisation_Stat objects.

I want to enumerate Accounts which have related Org Stat objects which meet a criteria. Accounts with no Org Stats or Org Stats that do not meet the criteria will be omitted.

I have this query:

SELECT Account.Id, Account.Name, (SELECT Id, Name, Clients_Connected__c FROM Organisation_Stats__r S WHERE S.Clients_Connected__c > 3) Stats FROM Account

and in an action method in a controller, I'm trying to traverse the accounts and each accounts related set of Org Stats using this code, but it's not working:

public PageReference InvokeAsmxServiceV2() {    

  List<Account> queryResults = [SELECT Account.Id, Account.Name, (SELECT Id, Name, Clients_Connected__c FROM Organisation_Stats__r S WHERE S.Clients_Connected__c > 3) Stats FROM Account];

  for (Account o : queryResults) {

    /* Code for account omitted */

    for (Organisation_Stat__c s : o.Stats) {

      /* code for dealing with child org stat object omitted */
    }
  }

  return null;
}

I've run this query in Codingo Studio, and the three output columns are "Id", "Name", and "Organisation_Stat__r" where the latter is a collection.

I don't think I should be using List<Account> since its stopping me getting to the Organisation_Stat__r collection.

When I System.debug(System.JSON.serializePretty(queryResults)) there is an Organisation_Stat__r property on each Account populated with the data I want to process.

What should I change in the above to complete the traversal?

Answer 1

I tried a simplified version of the query using the Account to Contact parent-to-child relationship. With this I was able to access the Contacts for each Account.

List<Account> queryResults = [Select Id, (Select Id from Contacts) from Account limit 10];
for (Account a : queryResults) {
    for (Contact c : a.Contacts) {
        System.debug(c);
    }
}

You might find Relationship Queries.Understanding Relationship Names helpful in identifying the API names for the SOQL query.

Try reworking your query to something like:

List<Account> queryResults = [
     SELECT Id, Name, 
     (SELECT Id, Name, Clients_Connected__c 
      FROM Account.Organisation_Stats__r 
      WHERE Clients_Connected__c > 3) 
     FROM Account];

Then you should be able to access the child records with o.Organisation_Stats__r (where o is an instance of Account).

Another alternative would be to reverse the relationship query. E.g. (Your Account child-to-parent relationship name may vary)

List<Organisation_Stats__c> queryResults = [
    SELECT Id, Name, Clients_Connected__c, Account__r.Id, Account__r.Name
    FROM Organisation_Stats__r 
    WHERE Clients_Connected__c > 3];

Side Note

You will want to consider how many records are going to be returned by this query. If you are querying across all the Accounts in an org you will start to run into various limits. It might be necessary to pass this off to a batch job if you can't narrow the query down.

Answer 2

public PageReference InvokeAsmxServiceV2() {    

  List<Account> queryResults = [SELECT Account.Id, Account.Name, (SELECT Id, Name, Clients_Connected__c FROM Organisation_Stats__r WHERE Clients_Connected__c > 3) FROM Account];

  for (Account o : queryResults) {

    /* Code for account omitted */

    for (Organisation_Stat__c s : o.Organisation_Stats__r) {

      /* code for dealing with child org stat object omitted */
    }
  }

  return null;
}

Answer 3

Here's a solution using two custom objects. A 'booking' has a number of Attendees/Delegates.

String traineeName;
String lstTrainees;
List<Booking_Reference__c> lstBookings = [SELECT name,EventId__c, (SELECT Id, name, delegate__c, delegate__r.name, product__c, Asset_Used__c from Attendees__r) FROM Booking_Reference__c];
for (Booking_Reference__c br: lstBookings){
    for (Attendee__c att : br.Attendees__r){
        system.debug(att.delegate__r.name);
    }
}

However, the reverse the relationship query doesn't allow the reference to the parent objects fields. The results below are identical providing only the object Id for the booking and the delegate.

List<Attendee__c> queryResults = [SELECT delegate__c,  booking__c  FROM Attendee__c ];
system.debug(queryResults);
List<Attendee__c> queryResults2 = [SELECT delegate__r.name,  booking__r.name  FROM Attendee__c ];
system.debug(queryResults2);

(Attendee__c:{Delegate__c=0038E00000OE4JJQA1, Booking__c=a048E00000AL79uQAD, Id=a1i8E000001etm1QAA},etc

You need to iterate through the result:

    For(Attendee__c att:[select Name,booking__r.Name, delegate__r.name from Attendee__c])
{
  system.debug('Attendee Name:'+att.Name+'     Booking Ref:'+att.booking__r.Name+'     Delegate Name:'+att.delegate__r.Name);
}

Thanks to Banwari Kevat for the clue in his answer at https://developer.salesforce.com/forums?id=9060G000000XbH5QAK

Answer 4

This is an alternative I tried, worth a shout.

//CoBId is set of all CoB
 List<Customer_OnBoarding__c> CustOB = [SELECT Id, (SELECT Id FROM Attachments) FROM Customer_OnBoarding__c WHERE Id = :CoBId];

    if (!CustOB.isEmpty()) {
      Set<Id> attachmentIds = (new Map<Id, SObject>(CustOB[0])).keySet();
      List<Attachment> files = [SELECT Name, Body, ContentType FROM Attachment WHERE Id IN :attachmentIds];
    }