Nested Tables and Nested Queries in Visualforce - An Example
9:27 AMI 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 AccountThis 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
10 comments
What does the test code look like?
ReplyDelete-Thanks
If I need to go to third level child object, this class does not accept. Is there a better way to accomplish it?
ReplyDeleteHi Can you please give an example for a bill of material example also.
ReplyDeleteHere 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
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?
ReplyDeleteI have tried using 'rerender' with these tables, but it doesn't work... any ideas why?
ReplyDeleteAll I'm doing is adding ID's to the pageblocktables and creating a button that rerenders those ids.
This comment has been removed by the author.
ReplyDeleteOn 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{!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
ReplyDeleteI 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
ReplyDeleteI need to pass the Account ID to the query to pull data only for a particular account. Any ideas how this can be done.
ReplyDelete