How to get a SOQL query out of a for loop

Question

Code added.

I have searched endlessly for a solution here and cannot find one, please help!

I have three objects (A, B, and C). A has a lookup to B, and B is the master to C (detail). Both A and C have many records related to each B record.

I want to have a job run that gets a subset of records from object C (it will usually be around 5,000 records). Then go through each of those and get the records on Object A that lookup to the same Object B record, summarize an Object A number field, and put that on the C record.

I have successfully gotten this to work in small scale, <100 Object C records. But each Object C record requires a new SOQL query since I am iterating through them in a for loop after I get all the Object C records. Plus I know this it is not best practice to ever have a query in a loop.

How can I get this to work? Since the records share the relationship with Object B, is there another way to get the data from the Object A records that match? Or is there some way to pull two lists, one Object C and one Object A. Then summarize the Object A records and line the lists up some how?

Thanks in advance!

Code:

public class nightlyJob {
    public static void updateNumbers(){
        integer I = 29; 
        List<ObjectC__c> CUpdateList = new List<ObjectC__c>();
        List<ObjectC__c> CpullList = 
                  [SELECT ID, Index__c, ObjectB__r.id
                  FROM ObjectC__c
                  WHERE Index__c = :I];
                for(ObjectC__c s : CpullList){
                        List<ObjectA__c> AList =
                            [SELECT ObjectB__c, Number__c
                            FROM ObjectA__c
                            WHERE ObjectB__c = :s.ObjectB__r.Id];
                    decimal NumSum = 0;
                    for(ObjectA__c a : AList){
                        NumSum = a.Number__c + NumSum;
                    }
                    s.Num__c = NumSum;
                    CUpdateList.add(s);
                }
        update CUpdateList;
    }            
}

Answer 1

It looks like you are really missing several fundamental concepts at the moment.

The biggest problem you are up against in SFDC development is that "database" operations are very expensive and are strictly limited. It's not just a matter of "best practice": if in a single transaction you exceed these limits -- number of SOQL calls, number of records returned, number of records updated, number of DML statements, etc. -- your transaction will fail. For details, search online for "Salesforce Execution Governors and Limits".

You can write code that works within these limitations, but there is a bit of a learning curve.

First, learn to use collections with SOQL queries to get your SOQL queries out of loops. This is a.k.a. "bulkfication" and it fundamental to SFDC development:

    List<ObjectC__c> CpullList = 
              [SELECT ID, Index__c, ObjectB__r.id
              FROM ObjectC__c
              WHERE Index__c = :I];
    // Create a map with the results of this query.  
    // key=ObjectC__c.Id, value = Object__c record
    Map<Id, ObjectC__c> objCmap = Map<Id, ObjectC__c>(CpullList);

    // Build a set of all the Object_B id's from this result set
    Set<Id> objBids = new Set<Id>();
    for (ObjectC__c record : CpullList) {
        objBids.add(record.ObjectB__r.id);
    }

    // Now you can use only one SOQL query instead of a loop
    List<ObjectA> AList = [SELECT ObjectB__c, Number__c
                        FROM ObjectA__c
                        WHERE ObjectB__c in:objBids];

Next, use "SOQL aggregate functions" whenever you can. Example: in your code here, you could use "SUM()" and "group by" instead of performing these calculations with loops:

    // Get the sum of ObjectA__c.Number__c for each Object B in objBIds
    AggregateResult[] groupedResults = [select ObjectB__c, 
                                        sum(Number__c) sumA 
                                        from ObjectA__c 
                                        where ObjectB__c in: objBids 
                                        group by ObjectB__c];

    for (AggregateResult ar : groupedResults)  {
        System.debug('Object B Id' + ar.get('Objectb__c'));
        System.debug('Sum of ObjectA__c.Number__c' + ar.get('sumA'));

        // Here, you might want to build a Map<Id, Integer> sumAmap:
        // key=Object B ID, value=sumA
        // and then use it along with objCmap to build a collection of Object C's 
        // for your update statement...
    }

You can continue this process and apply these ideas to make the code more efficient.

But even after you have your methods working as efficiently as possible, you still may run into limits due to the number of records you're dealing with. At that point, you will need to learn about the Batchable interface, the Queuable interface and @future calls (how to process a larger number of records, split across transactions) That's really too much to information to cover in a single SO answer.