Accessing fields from nested SOQL and exposing them in Visualforce

Question

I have a question in regards to Nested SOQL queries and exposing their fields, that are part of that Nested SOQL, into a Visualforce page.

This is a structure sample:

List<Object1> obLis=[SELECT Id, Name,
(SELECT Id, Name FROM Object 2), (SELECT ID, Name FROM Object3 FROM Object1 LIMIT 500]; 

The Object one has a Parent relationship over Object 2 and Object 3.

What I want is to be able to retrieve the fields on that query and expose them in a Visualforce keeping the relation between objects.

Lest's say we have this example from SFDC:

//Controller
 public with sharing class TestDisplayQueryList{ 
 public List<Account> Records {get; set;} 
  public TestDisplayQueryList(){ 
  Records = [select Name, AccountNumber, CleanStatus from
  Account where CleanStatus='Pending']; 
  } 
 }

This can be uses in Visualforce like this to expose the fields:

<apex:page controller="TestDisplayQueryList"> 
<apex:pageBlock title="My Content"> 
    <apex:pageBlockTable value="{!Records}" var="Record"> 
        <apex:column > 
            <apex:facet name="header">Account Name</apex:facet> 
            <apex:outputText value="{!Record.Name}"/> 
        </apex:column> 
        <apex:column > 
            <apex:facet name="header">Account Number</apex:facet> 
            <apex:outputText value="{!Record.AccountNumber}"/> 
        </apex:column> 
        <apex:column > 
            <apex:facet name="header">Clean Status</apex:facet> 
            <apex:outputText value="{!Record.CleanStatus}"/> 
        </apex:column> 
    </apex:pageBlockTable> 
</apex:pageBlock> 

This is very clear and straight forward. But, how can I get the fields from Object 2 and Object 3 in the nested SOQL query to show up in the Visualforce?:

 List<Object1> obLis=[SELECT Id, Name,
    (SELECT Id, Name FROM Object 2), (SELECT ID, Name FROM Object3 FROM Object1 LIMIT 500];

The all idea is that I want to be able to expose in a table all the records related in Objects 1, 2 and 3

Thanks.

This is the controller I'm using:

public class OrderQuery
{

  public List<Account> acclist {get; set;}

  public OrderQuery()
  {

  acclist= [SELECT Name, KvK_nummer__c,  Street_Name__c, House_Number__c, House_Number_Suffix__c, Zip_code__c,RecordTypeId,
                  (SELECT OrderNumber, Status,Type, BillToContactName__c, EffectiveDate FROM Orders WHERE Status='Confirmed'),
                  (SELECT FirstName, LastName, Title, MobilePhone, Email FROM Contacts) FROM Account LIMIT 20];

   }

}

The page:

<apex:page controller="OrderQuery" sidebar="false">
  <apex:pageBlock title="Orders Block">
    <apex:pageBlockTable value="{!accList}" var="acct"> 
    <apex:column value="{!acct.Name}"/>
  <apex:repeat value="{!acct.Orders}" var="obj2"> 
  <apex:column value="{!obj2.OrderNumber}"/>
</apex:repeat> </apex:pageBlockTable> </apex:pageBlock></apex:page>

Answer 1

Using the results of a parent-child subquery (aka a left outer join) in Visualforce (and Apex) is easy to understand once you know about how SOQL returns the data for subqueries.

A query will always return a List (provided that it compiles, and you haven't used up your 100 queries in a synchronous transaction or 200 in an asynchronous transaction).

When you have a subquery, it gets returned as a List nested inside of the List returned by the outer query.

You access the subquery results by using the relationship name of the child object. For standard relationship fields, this is usually just the plural of the child object name (Contacts, Opportunties, Users, etc...). There are some odd cases, like the self-relationship on Account via the ParentId field where the relationhip name is ChildAccounts, but those aren't too common.

For custom relationship fields (lookup or master-detail), the default relationship name is the plural + __r (My_Custom_Objects__r).

So, and assuming that the parent object here is Account, and one of the relationship names is Object2s__r

<apex:pageBlock title="My Content">
    <apex:repeat value="{!Accounts}" var="acct">
        <apex:outputText value="{!acct.Name}"/>
        <apex:repeat value="{!acct.Object2s__r}" var="obj2">
            <apex:outputText value="{!obj2.Name}"/>
        </apex:repeat>
    </apex:repeat>
</apex:pageBlock>

About the only other thing that you should keep in mind is that nesting visualforce components that iterate over collections is a good way to blow through the 135kb viewstate limit (though if you're getting anywhere close to that, it's an indication that you're trying to display too much data on your page).

If you're having trouble finding the relationship name, there are a few different ways to try to figure it out:

• Use the schema builder in Salesforce, find your child object, double click on the relationship field to the parent object. This should bring up a dialogue box that contains the relationship name (minus the __r for custom relationship fields) among other things
• Use the workbench, navigate to the object details for your parent object, and expand the 'child relationships'. Find your child object (My_Object__c.Relationship_Field__c), expand it, and its relationship name will appear
• Using Apex, get the describe info for the object, and use the getChildRelationships() method

Answer 2

There's a problem with the query you're asking us about.

List<Object1> obLis=[SELECT Id, Name,
(SELECT Id, Name FROM Object2), (SELECT ID, Name FROM Object3];

If the relationship exists as you describe, the query should look something more like this:

List<Object1>obLis =[SELECT Id, Name,
(SELECT Id, Name FROM Object2s), (SELECT ID, Name FROM Object3s) 
FROM Object1 WHERE 'Some criteria' ];

In other words, Object1 has to be included in the query. Once you do that, you can then create relationships between what's returned and the subqueries to output them on the page using one of a couple different techniques.

The technique that I prefer is to return the above as a map.

Map<Id,Object1>id2obLis = new map([SELECT Id, Name,
(SELECT Id, Name FROM Object2s), (SELECT ID, Name FROM Object3s) 
FROM Object1 WHERE 'Some criteria' ]);

From the above, you can then relate the results into separate lists for each object or iterate on the Id's of Object1 to pull the results from the map. It's not clear from your example, exactly how you want to display the results, whether all across for the same Object1 Id or in groups of tables for each Object. It's easy to do it using either method if your results are returned as a map.

If you need the three separate lists of Objects to iterate on, you then do something like the below:

To extract everything to where you can use it:

set<Id>obj1Ids = new set<Id>();
list<Object1>obj1Records = new list<Object1>();
list<Object2>obj2Records = new list<Object2>();
list<Object3>obj3Records = new list<Object3>();

obj1Ids = id2obLis.keyset();

for(Id o1:obj1Ids){

    Object1 Record = id2obLis.get(o1);

    obj1Records.add(Record);

    list<Object2s> = id2obLis.get(o1).Object2s;

    liar<Object3s> = id2obLis.get(o1).Object3s;

    for(Object2 o2:Object2s){

        obj2Records.add(o2);

    }

    for(Object3 o3:Object3s){

        obj3Records.add(o3);

    }

}

Note that you can also create maps of Object1 to each of the Object2 and Object3 records. Since there can be multiple Object2 and Object3 records, the internal for loops are necessary in most cases unless you're certain those records won't exist. Also note that the subqueries are performed on the plural of the object name, not the singular. I've assumed standard objects, so didn't add __c or __r to the sample code.