1

I have to display all accounts and their opportunity and their associated opportunity line item in vf page. I was able to achieve account and its opportunity but when adding code for opportunity line Item in vf page it throws the following error:

System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Opportunity.Name Class.Acc_Opp_Oppline.: line 27, column 1

VF page:

<apex:page controller="Acc_Opp_Oppline">
    <apex:pageBlock>
    <apex:pageBlockTable value = "{!accs}" var="a">
         <apex:column value="{!a.name}"/>
        <apex:column headervalue="Opportunities">
        <apex:pageBlockTable value="{!mpopp[a.name]}" var="c">
           <apex:column value="{!c.name}"/>   
            <apex:column headervalue="Opportunity LineItems">
            <apex:pageBlockTable value="{!mpoppline[c.name]}" var="o">
             <apex:column value = "{!o.name}"/>
            </apex:pageBlockTable>
            </apex:column>
         </apex:pageBlockTable> 
        </apex:column>
        </apex:pageBlockTable>    
    </apex:pageBlock>
</apex:page>

controller:

public class Acc_Opp_Oppline {    
    public List<Account> accs {get;set;}
    public Map<string,list<opportunity>> mpopp {get;set;}
    public Map<string,list<OpportunityLineItem>> mpoppline {get;set;}
    public Acc_Opp_Oppline()
    {
        List<Opportunity> opps = new List<Opportunity>();
        List<OpportunityLineItem> oppItems = new List<OpportunityLineItem>();
        accs = [select id,name,(select id,Name from Opportunities) from Account];
        mpopp=new map<string,list<Opportunity>>();
        mpoppline=new map<string,list<OpportunityLineItem>>();
        for(Account acc : accs)
        {
            for(Opportunity o : acc.opportunities)
         {
            opps.add(o);
         }
          mpopp.put(acc.Name, opps);
          for(Opportunity oppLineItem : [select id, (select id,name from OpportunityLineItems) from Opportunity
                                                  where ID IN :opps])
          {
              for(OpportunityLineItem ol : oppLineItem.OpportunityLineItems)
              {
                  oppItems.add(ol);
              }
              mpoppline.put(oppLineItem.Name, oppItems);
          }
        }
    } 
}
1
  • Seems the error is straight forward. You need to add Opportunity "Name" field into your query. Then only, It's can be used in line mpoppline.put(oppLineItem.Name, oppItems);
    – Raj
    Commented Sep 26, 2021 at 14:41

1 Answer 1

0

Your code has a number of problems, which we'll address here.

First, you have queries inside of loops. You need to refactor this to avoid governor limits. You can't just run unlimited queries like that.

Second, you forgot a field in your query on line 19, which is the original cause for your error.

Third, you're mapping by Name, which may not be unique, thus risking mixing up the elements. You should almost always prefer to map by Id instead.

Fourth, you're not creating a new list of opportunities and opportunity line items for each of the relevant parents, so even if everything else was fixed, you'd result in a jumble of every account appearing to have every opportunity, each of which would appear to have every opportunity line item. You can't initialize the list just once. We'll demonstrate how to do this properly in the code below. See this for a bit more information.

Fifth, you're using maps in a way that I generally consider unsafe in Visualforce. If there are any missing values in your maps, your page will crash with a "key not found" error. You really should use "wrappers" for this so you don't need to rely on maps. They add a bit of code, but you can then always guarantee that there will be a valid data structure.

Sixth, your code may run into heap, CPU, or row limit errors, as you're running unrestricted queries. For purposes of this answer, though, we will presume that you only have a limited amount of data as a Proof of Concept for how this should work. In reality, you'd have to restrict the queries so you don't exceed any limits.

Keeping all the above in mind, here's the code, with (hopefully) sufficient comments.


public class Acc_Opp_Oppline {
    // Wrapper classes for display purposes
    public class AccountWrapper {
        public Account record { get; set; }
        public List<OpportunityWrapper> opportunities { get; set; }
        AccountWrapper(Account record) {
            this.record = record;
            opportunities = new List<OpportunityWrapper>();
        }
    }
    public class OpportunityWrapper {
        public Opportunity record { get; set; }
        public List<OpportunityLineItemWrapper> lineItems { get; set; }
        OpportunityWrapper(Opportunity record) {
            this.record = record;
            lineItems = new List<OpportunityLineItemWrapper>();
        }
    }
    public class OpportunityLineItemWrapper {
        public OpportunityLineItem record { get; set; }
        public OpportunityLineItemWrapper(OpportunityLineItem record) {
            this.record = record;
        }
    }
    public List<AccountWrapper> accountList { get; set; }
    public Acc_Opp_Oppline() {
        accountList = new List<AccountWrapper>();
        Map<Id, List<Opportunity>> opportunitiesByAccount = new Map<Id, List<Opportunity>>();
        Map<Id, List<OpportunityLineItem>> lineItemsByOpportunity = new Map<Id, List<OpportunityLineItem>>();

        // Get the records we need
        Account[] accounts = [SELECT Name FROM Account];
        // Make sure we only get opportunities that have an account
        Opportunity[] opportunities = [SELECT AccountId, Name FROM Opportunity WHERE AccountId = :accounts];
        // And make sure we only get line items for the above opportunities
        OpportunityLineItem[] lineItems = [SELECT OpportunityId, Name FROM OpportunityLineItem WHERE OpportunityId = :opportunities];
        // We pre-initialize the opportunitiesByAccount map to avoid later if statements
        for(Account record: accounts) {
            opportunitiesByAccount.put(record.Id, new List<Opportunity>());
        }
        // Same for lineItemsByOpportunity
        for(Opportunity record: opportunities) {
            lineItemsByOpportunity.put(record.Id, new List<OpportunityLineItem>());
            opportunitiesByAccount.get(record.AccountId).add(record);
        }
        for(OpportunityLineItem record: lineItems) {
            lineItemsByOpportunity.get(record.OpportunityId).add(record);
        }
        // Now, we build our final data format
        // Note that this is an acceptable use of "nested for loops."
        // You may have heard they're bad for you, but that's only if you're not using Maps to find related data, as we do here.
        // See https://salesforce.stackexchange.com/a/282831/2984
        for(Account accountRecord: accounts) {
            // For each account, we create a wrapper
            AccountWrapper thisAccountWrapper = new AccountWrapper(accountRecord);
            for(Opportunity opportunityRecord: opportunitiesByAccount.get(accountRecord.Id)) {
                // As well as one for the opportunity
                OpportunityWrapper thisOpportunityWrapper = new OpportunityWrapper(opportunityRecord);
                for(OpportunityLineItem lineItemRecord: lineItemsByOpportunity.get(opportunityRecord.Id)) {
                    // This last wrapper really wasn't necessary, but it makes things more consistent.
                    // Note that we don't need a lineItemWrapper variable, since we're adding directly to the list.
                    thisOpportunityWrapper.lineItems.add(new OpportunityLineItemWrapper(lineItemRecord));
                }
                // Add the wrapped opportunity to the account
                thisAccountWrapper.opportunities.add(thisOpportunityWrapper);
            }
            // And add the wrapped account to our display list
            accountList.add(thisAccountWrapper);
        }
    } 
}

<apex:page controller="Acc_Opp_Oppline">
    <apex:pageBlock>
        <apex:pageBlockTable value = "{!accountList}" var="a">
            <!-- since we have wrappers, we change a.name to a.record.name, etc -->
            <apex:column value="{!a.record.name}"/>
            <apex:column headervalue="Opportunities">
                <apex:pageBlockTable value="{!a.opportunities}" var="c">
                    <apex:column value="{!c.record.name}"/>   
                    <apex:column headervalue="Opportunity LineItems">
                        <apex:pageBlockTable value="{!c.lineItems}" var="o">
                            <apex:column value = "{!o.record.name}"/>
                        </apex:pageBlockTable>
                    </apex:column>
                </apex:pageBlockTable> 
            </apex:column>
        </apex:pageBlockTable>    
    </apex:pageBlock>
</apex:page>

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .