6

I am inserting the opportunity line item based on the values of custom object. I am able to achieve this if I add the soql query inside for loop.But that is not an optimized solution. Can anyone suggest me how to do it with out adding inside for loop ?

Apex Class:

List<PricebookEntry> PBList = new List<PricebookEntry>();
for(Pricing__c prices: priceList){
                PBList=[SELECT Id, IsActive, CurrencyIsoCode FROM PricebookEntry
                    WHERE  Product2.Name =:prices.Name__c];

            If (pbeRecord.size() == 1){ 

                for(Pricebookentry PB: PBList){

                    OpportunityLineItem OpItem=new OpportunityLineItem();
                    oli.PriceBookEntryId=PB.Id;
                    oli.Opportunityid=prices.Opportunity__r.id;
                    oplineList.add(oli);
                    }
            }
                    insert oplineList;
  • Is this a trigger? – tjcinnamon Jul 12 '16 at 20:21
7

Here is one approach.

  1. First get a set of the products you're going to need
  2. Get all the PBEntry records at once and put them on a map (key = the product name) so you can retrieve them easily later
  3. In your loop, check for the existence of records with that product name and, if they do, then retrieve the records and iterate to perform your logic

Sample Code

      //Create a Set with all the Products
      Set<String> productNames = new set<String>();
      for(Pricing__c prices: priceList) {
        productNames.add(prices.Name__c);
      }

     //Put the set in a map for easy access
     Map<String, List<PricebookEntry>> PBMap = new Map<String, PricebookEntry>();
     for (PricebookEntry  pb :  [SELECT Id, IsActive, CurrencyIsoCode FROM PricebookEntry
              WHERE  Product2.Name  in :productNames]) {

            if (! PBMap.containsKey(pb.Product2.Name)) {
               PBMap.put(pb.Product2.Name, new List<PricebookEntry>);
            }

            List<PricebookEntry> pbList =  PBMap.get(pb.Product2.Name);
            pbList.add(pb);
            PBMap.put(pb.Product2.Name, pbList);
     }

      for(Pricing__c prices: priceList) {

        If (PBMap.containsKey(prices.Name__c)){ 
           //Get all the PB Entries that matched  
           List<PricebookEntry> pbList =  PBMap.get(prices.Name__c);

            for(Pricebookentry PB: PBList){

                OpportunityLineItem OpItem=new OpportunityLineItem();
                oli.PriceBookEntryId=PB.Id;
                oli.Opportunityid=prices.Opportunity__r.id;
                oplineList.add(oli);
                }
        }

       insert oplineList;
  • Amar, your code seems to be missing declarations (for oplineList, for example) so I didn't include them but majorly is an example on how to retrofit your code to achieve the same with the SoQL outside the loop. – Sebastian Kessel Jul 12 '16 at 19:16
  • Thanks for your response I added the logic you provide but it is saying Variable does not exist: pb.Product2.Name at line List<PricebookEntry> pbList = PBMap.get(pb.Product2.Name); – Amar Jul 12 '16 at 19:51
  • D'uh. Change the offending line to List<PricebookEntry> pbList = PBMap.get(prices.Name__c); Code is fixed in the sample. – Sebastian Kessel Jul 12 '16 at 19:52
0

You can try something like this:

List<PricebookEntry> PBList = new List<PricebookEntry>();
List<String> somelIst = new List<String>();
for(Pricing__c prices: priceList){
someList.add(prices.name__C);
}

PBList=[SELECT Id, IsActive, CurrencyIsoCode FROM PricebookEntry
                    WHERE  Product2.Name IN someList];


for(Pricing__c prices: priceList){


            If (pbeRecord.size() == 1){ 

                for(Pricebookentry PB: PBList){

                        OpportunityLineItem OpItem=new OpportunityLineItem();
                        oli.PriceBookEntryId=PB.Id;
                        oli.Opportunityid=prices.Opportunity__r.id;
                            oplineList.add(oli);
                            }
                    }
}
     insert oplineList;
  • Can you add more explanation than just telling OP to try your code? – Adrian Larson Jul 12 '16 at 21:41

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.