Nested Tables and Nested Queries in Visualforce - An Example

9 comments
I recently had to work on a requirement which included nested queries and nested tables. Thanks to the forums and the developer guides, i got the information from a couple of posts and articles.

This article is just a consolidation of information spread over many places.

Scenario:

I will have to display a list of Accounts. Under each account i will have to display the associated Account Team Members.


SOQL Query:

The SOQL Query to retrieve both the Account Details as well as the Account Team Member details will be as follows..


Select Id,(Select TeamMemberRole, User.Name From Account.AccountTeamMembers), Name, BillingCountry from Account

This is what is called a nested query which is quite obvious from the fact that you can see two "Select" statements in a single query..

Now, let us create a simple visualforce page to display the query results.


Visualforce Page:


<apex:page tabstyle="Account" controller="nestedqueryexample">
    <apex:pageblock> 
        <apex:pageblocktable value="{!accsandtmember}"  var="accdet">
            <apex:column >
                   <apex:facet name="header">                                        
                         Team Members                                                                               
                    </apex:facet>
                    <apex:pageblocktable value="{!accdet.AccountTeamMembers}" var="tm">
                        <apex:column headerValue="Team Member">
                               <apex:outputfield value="{!tm.User.Name}"/>
                         </apex:column>
                         <apex:column headerValue="Role">
                               <apex:outputfield value="{!tm.TeamMemberRole}"/>
                         </apex:column>                          
                    </apex:pageblocktable>
            </apex:column>
            <apex:column headervalue="Account Name">
                    <apex:outputtext value="{!accdet.Name}"/>
            </apex:column>
            <apex:column headervalue="Billing Country">
                    <apex:outputtext value="{!accdet.BillingCountry}"/>
            </apex:column>
        </apex:pageblocktable>
    </apex:pageblock>
</apex:page>  

Controller (Apex Class):



public class nestedqueryexample
{
  public List<Account> getaccsandtmember()
  {
      List<Account> accounts = [Select Id,(Select TeamMemberRole, User.Name From Account.AccountTeamMembers), Name, BillingCountry from Account];
      return accounts;
  }
}

Your final output will look like this.

Looks messy rite?. We will add a expand/collapse button for the nested table to make it look clean.

Learn More - Click here

9 comments :

  1. What does the test code look like?

    -Thanks

    ReplyDelete
  2. If I need to go to third level child object, this class does not accept. Is there a better way to accomplish it?

    ReplyDelete
  3. Hi Can you please give an example for a bill of material example also.

    Here is a SQL Query for SQL server
    How do we do in SFDC usign APEX & VF PAges

    Viewing a multilevel bill-of-materials list for a parent product

    USE AdventureWorks;
    GO
    WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
    (
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
    b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
    AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
    bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom
    INNER JOIN Parts AS p
    ON bom.ProductAssemblyID = p.ComponentID
    AND bom.EndDate IS NULL
    )
    SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
    ComponentLevel
    FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
    ORDER BY ComponentLevel, AssemblyID, ComponentID;
    GO

    ReplyDelete
  4. can we use nested nested tag. Means i have a requirement like we need to query the Oppurtunity in which an inner query on Account child object.So now from Oppurtunity to Account and Account to its child PartnerLocation.How can i acheive this? This is very tricky one?Please help me how to acheive this?

    ReplyDelete
  5. I have tried using 'rerender' with these tables, but it doesn't work... any ideas why?

    All I'm doing is adding ID's to the pageblocktables and creating a button that rerenders those ids.

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. On your VF page, you have value="{!accdet.AccountTeamMembers}" for your team member listing. Is this a different query you are calling from the one you have listed?

    ReplyDelete
  8. {!accdet.AccountTeamMembers}, Here AccountTeamMembers is actually the name of the related list... You can find this when you explore the schema of the Account object... It actually refers to the subquery in the apex class Account.AccountTeamMembers

    ReplyDelete
  9. I have been searching trying to find a way to pull the Account Team Member Names into a custom field via a trigger, but found that it is possible to have a trigger on the AccountTeamMember obj. So how are you pulling that information in and it's not possible with a trigger

    ReplyDelete