I know how to do two levels down, for example the below works
SELECT Name, (SELECT Name FROM Opportunities ) FROM Account WHERE ID = '001j0000003YExp'
However, when I try to extrapolate that down further it doesn't work
What am I doing wrong.Thanks,SELECT Name, (SELECT Name, (SELECT Name, Total Price FROM OpportunityLineItem) FROM Opportunities ) FROM Account WHERE ID = '001j0000003YExp'
Hello,Keep in mind that Opportunity/OpportunityLineItems is a one to many relationship as well as Account/Opportunity which means that for each Opportunity results you have to iterate over its OpportunityLineItems. In this example I summed totalPrice values:
Depending on your need you can get one Opporunity as well as one OpportunityLineItem, all you need is to add a LIMIT clause to specify the maximum number of rows to return. I.e:public List<ChartData> getData() {
List<Opportunity> oppData = [SELECT Name, (SELECT Name, TotalPrice FROM OpportunityLineItems),
Account.Name FROM Opportunity WHERE AccountId = '001j0000003YExp'];
Double oppPrice = 0;
for(Opportunity oppResult : oppData) {
String oppName = oppResult.Name;
for (OpportunityLineItem lineItem : oppResult.OpportunityLineItems) {
oppPrice += lineItem.TotalPrice;
}
data.add(new ChartData(oppName, oppPrice));
}
}
public List<ChartData> getData() {
Opportunity oppData = [SELECT Name, (SELECT Name, TotalPrice FROM OpportunityLineItems),
Account.Name FROM Opportunity WHERE AccountId = '001j0000003YExp' LIMIT 1];
Double oppPrice = 0;
for(Opportunity oppResult : oppData) {
String oppName = oppResult.Name;
for (OpportunityLineItem lineItem : oppResult.OpportunityLineItems) {
oppPrice += lineItem.TotalPrice;
}
data.add(new ChartData(oppName, oppPrice));
}
}
