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..
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:
Controller (Apex Class):
Your final output will look like this.
Learn More - Click here
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