1

I have seen this questions which basically advise to run the following:

List<Opportunity> opps = new list<Opportunity>([Select Id, (Select Id, name From OpportunityLineItems) From Opportunity])

Now I want to access each OpportunityLineItem.

Seems like the opps list has no access to the related OpportunityLineItem(s) - Is there any way to loop each OpportunityLineItem without additional query?

0

3 Answers 3

2

You can iterate over Opportunity followed by iterating over inner query as:

for(Opportunity objOpportunity: [Select Id, 
    (Select Id, name From OpportunityLineItems) From Opportunity]) {

    // access opportunity with objOpportunity
    for(OpportunityLineItem objOli: objOpportunity.OpportunityLineItems) {
        // access child items with objOli
    }
}
1
  • This is a great answer. The map contains a key-value pair of ID to Opportunity, and each Opportunity has a list of OLIs. So you can iterate over the map values (or over the keys and get each value) and then in that loop, iterate over the list of OLIs. Mar 12, 2018 at 19:32
1

This seems to be (to me) mostly an issue of understanding how SObjects behave.

It's like ORM

SObjects in Salesforce feel a lot like ORM (object-relational mapping) like Java's Hibernate, or PHP's Doctrine or Propel. An SObject is an object (brilliant deduction, I know), and when we perform a SOQL query Salesforce takes care of turning the result of the query into an object that we can use.

Fields on an SObject become variables, and we can directly access them (provided you have the appropriate permissions).

The following query

[SELECT Id, Name, CloseDate FROM Opportunity]

behaves a lot like an object like this

class Opportunity{
    Id id;
    String name;
    Date closeDate;
}

Thought of like that, if you have a base understanding of objects and/or object-oriented programming, then expressions and statements like opp.id == targetId and opp.name = 'My Opp'; make intuitive sense.

What about related data?

That works for fields on the same object that in your base query, what about for other things?

This is another time where SObjects feel a lot like ORM. Let's look at Opportunity as our base object, and also Account and OpportunityLineItem.

Working with a new query

[SELECT Id, Name, CloseDate, Account.Name, Account.BillingStreet, (SELECT Id, Quantity FROM OpportunityLineItems) FROM Opportunity]

If we were to represent this query as a class, it'd look like this

class Opportunity{
    Id id;
    String name;
    Date closeDate;
    Account account;
    List<OpportunityLineItem> opportunityLineItems;
}

If you are querying from child -> parent (i.e. from Opportunity to Account), we don't actually pull the fields we're querying from Account into the Opportunity class, instead, we store that information in an instance of Account, and then store that Account in the Opportunity.

Likewise, when we query from parent -> child (i.e. from Opportunity to OpportunityLineItem), we don't store field information from the OpportunityLineItem on the Opportunity object. They are stored on the OpportunityLineItem object, and because an Opportunity can have more than one line item, they are stored in a List.

So the class representation I gave above is incomplete. The full version would be

class Opportunity{
    Id id;
    String name;
    Date closeDate;
    Account account;
    List<OpportunityLineItem> opportunityLineItems;
}

class Account{
    Id id;
    String name;
    String billingStreet;
}

class OpportunityLineItem{
    Id id;
    Decimal quantity;
}

Takeaway

The point here is that when you query for "related data" (like fields from Account in a query on Opportunity), you treat it exactly like you would treat any other instance of that SObject (or list of SObjects).

// Everything in this example is 100% valid
// You'll need at least 1 Opportunity for this query to work (else get a "query 
//   has no rows for assignment to SObject" error)
Opportunity opp = [SELECT Id, Name, Account.Name, (SELECT Id, Quantity FROM OpportunityLineItems) FROM Opportunity LIMIT 1];

Account relAcct = opp.Account;
system.debug(relAcct.name);
system.debug(opp.Account.Name);

List<OpportunityLineItem> oliList = opp.OpportunityLineItems;

// If your Opportunity has 0 line items, you'll get an "index out of bounds: 0" error here
system.debug(oliList[0].Quantity);
system.debug(opp.OpportunityLineItems[0].Quantity);

for(OpportunityLineItem oli :opp.OpportunityLineItems){
    system.debug(oli.Quantity);
}
0

I found this example:

for (Opportunity opp : [Select Id, Name,(Select id, name, quantity, From OpportunityLineItems)
              From Opportunity Where Account.name = 'account_name'])
            {
                system.debug('>>OLIS:' + opp.OpportunityLineItems );
            }

Not the answer you're looking for? Browse other questions tagged or ask your own question.