0

I need to remove soql query from the for loop in below code snippet , how to proceed ?

private static List<IoTQuoteJSON.OtherDealerCodes> getOtherDealerCodes(Id oppId) {
        List<IoTQuoteJSON.OtherDealerCodes> listOd= new List<IoTQuoteJSON.OtherDealerCodes>();
        IoTQuoteJSON.OtherDealerCodes od = new IoTQuoteJSON.OtherDealerCodes();
        for(Opportunity_Dealer_Code__c odc:[SELECT Sales_Code_Type__c, Sales_Dealer_Code__c
                                              FROM Opportunity_Dealer_Code__c 
                                             WHERE Opportunity__c = :oppId AND Sales_Code_Type__c = 'Secondary']){
            system.debug('odc====>'+odc);                                     
            User myUser = [select name, email from USER WHERE Rep_Dealer_code__c = :odc.Sales_Dealer_Code__c];
            system.debug('myUser====>'+myUser);
            od.dealerCode = odc.Sales_Code_Type__c;
            od.dealerType = odc.Sales_Dealer_Code__c;
            od.dealerName = myUser.name;
            od.dealerEmailAddress = myUser.email;
            listOd.add(od);        
        }
        return listOd;//
}

Will be waiting for your answers

Thanks in advance

  • 1
    Welcome to SFSE. Please also consider completing Bulk Apex Triggers on Trailhead, which covers bulk patterns well, and searching our question history for nearly 1,000 questions that cover bulkification. – David Reed Oct 6 at 15:05
1

You need to bulkify the query by using a WHERE IN clause in your SOQL so you only make one query.

See example below. Keep in mind that if Rep Dealer Codes aren't unique, you will end up with a problem in your Code-to-User map. But then, you'd also have the same problem in your current code.

private static List<IoTQuoteJSON.OtherDealerCodes> getOtherDealerCodes(Id oppId) {
    List<IoTQuoteJSON.OtherDealerCodes> listOd= new List<IoTQuoteJSON.OtherDealerCodes>();
    IoTQuoteJSON.OtherDealerCodes od = new IoTQuoteJSON.OtherDealerCodes();

    List<Opportunity_Dealer_Code__c> dealerCodes = getDealerCodes(oppId);
    Map<String, User> dealerCodeToUser = getDealerCodeToUser(getUsers(getSalesDealerCodeSet(dealerCodes)));
    User myUser;

    for(Opportunity_Dealer_Code__c odc : dealerCodes) {
        myUser = dealerCodeToUser.get(odc.Sales_Dealer_Code__c);
        
        od.dealerCode = odc.Sales_Code_Type__c;
        od.dealerType = odc.Sales_Dealer_Code__c;
        od.dealerName = myUser.name;
        od.dealerEmailAddress = myUser.email;

        listOd.add(od);        
    }

    return listOd;
}

private static List<Opportunity_Dealer_Code__c> getDealerCodes(Id oppId) {
    return [
        SELECT Sales_Code_Type__c
            ,Sales_Dealer_Code__c
        FROM Opportunity_Dealer_Code__c 
        WHERE Opportunity__c = :oppId 
            AND Sales_Code_Type__c = 'Secondary'
    ];
}

private static Set<String> getSalesDealerCodeSet(List<Opportunity_Dealer_Code__c> opportunityDealerCodes) {
    Set<String> dealerCodes = new Set<String>();

    for(Opportunity_Dealer_Code__c odc : opportunityDealerCodes) {
        dealerCodes.add(odc.Sales_Dealer_Code__c);
    }

    return dealerCodes;
}

private static List<User> getUsersByDealerCode(Set<String> dealerCodes) {
    return [
        SELECT name, email 
        FROM User
        WHERE Rep_Dealer_code__c IN :dealerCodes
    ];
}

private static Map<String, User> getDealerCodeToUser(List<User> users) {
    Map<String, User> dealerCodeToUser = new Map<String, User>();

    for(User u : users) {
        dealerCodeToUser.put(u.Rep_Dealer_code__c, u);
    }

    return dealerCodeToUser;
}

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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