function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SF94108SF94108 

Need to reduce SOQL limit in code

Hi, Racing against a deadline!

 

The code below looks to see if an Order exists. update order rec if it does, else create new order.

 

Need to figure a way to get the sql query out of the loop to reduce the SOQL hits. Kind of feeling limited because of count(*) function in the query. 

 

Any quick fix ideas or feel free to fix the code :),

 

Thanks!

 

trigger CreateOrderRec4  on Opportunity (after update,after insert) {


List<order__C> newOrders = new List<order__c>();
List<order__C> Updatelist = new List<order__c>();

 
for(Opportunity NewOpp: trigger.new) {         
     
oppid = newOpp.ID;
integer orderCount = [select count() from order__C where opportunity__c = NewOpp.id and autocreate__c =True]; 


if (orderCount == 0 )
{
order__c a1 = new order__c();
a1.opportunity__c = newOpp.id;
a1.order_status__c = newopp.stagename;
a1.account__c = newopp.accountid;

a1.autocreate__c = True;
neworders.add(a1);
}

if (orderCount == 1)
{order__c updOrder = [select id,order_status__c
from order__C  where opportunity__c = newOpp.id and autocreate__c=True ];

updOrder.order_status__c = newopp.stagename;
updOrder.account__c = newopp.accountid;
Updatelist.add(updOrder);
}

}

insert neworders;
update updatelist;
}


ashish raiashish rai

Hello,

  Use this code :

 

trigger SalesPersonOnRecord on Opportunity (before insert, before update)

{ 

List<order__C> newOrders = new List<order__c>();

List<order__C> Updatelist = new List<order__c>();

Set<ID> oppIds = new Set<ID>();

for(Opportunity NewOpp : Trigger.new)    {        oppIds.add(NewOpp.id);    }     

list<Order__c> mpOrder=[select id,opportunity__c,order_status__c,autocreate__c from Order__c where id in:oppIds ];  

 for( Opportunity op : Trigger.new )

 {         

 integer count=0;                

    for(order__c c : mpOrder)        

    {            

   if(c.opportunity__c==op.id && c.autocreate__c==true)             count++;      

    }              system.debug('@@@@@@@@@@@' + count);

   if (orderCount == 0 ){order__c a1 = new order__c();

   a1.opportunity__c = newOpp.id;

   a1.order_status__c = newopp.stagename;a1.account__c = newopp.accountid;
   a1.autocreate__c = True;neworders.add(a1);}
   if (orderCount == 1)

   {

  mpOrder.order_status__c = op.stagename;

   mpOrder.account__c = op.accountid;

  Updatelist.add(updOrder);

   }

 }

insert neworders;

update updatelist;      

  }

 } 

 

Think this will help you.

List<order__C> newOrders = new List<order__c>();
jaxdmasterjaxdmaster
trigger CreateOrderRec4  on Opportunity (after update,after insert) {


List<order__C> newOrders = new List<order__c>();
List<order__C> Updatelist = new List<order__c>();


Set<Id> oppIds = new Set<Id>();
for(Opportunity NewOpp: trigger.new) {         
oppIds.add(NewOpp.id);
}

Map<Id,Integer> ordCountMap = new Map<Id,Integer>(); 

for(order__C  ord : [select opportunity__c from order__C where opportunity__c = NewOpp.id and autocreate__c =True]) {
if(ordCountMap.containsKey(ord.id)) {
ordCountMap.put(id, ordCountMap.get(id)+1);
}else {
ordCountMap.put(id, 1);
}
}
    
oppid = newOpp.ID;
integer orderCount = [select count() from order__C where opportunity__c = NewOpp.id and autocreate__c =True]; 

for(Opportunity NewOpp: trigger.new) {  

Integer orderCount = oppCountMap.get(newOpp.id);
if (orderCount == 0 )
{
order__c a1 = new order__c();
a1.opportunity__c = newOpp.id;
a1.order_status__c = newopp.stagename;
a1.account__c = newopp.accountid;

a1.autocreate__c = True;
neworders.add(a1);
}

if (orderCount == 1)
{order__c updOrder = [select id,order_status__c
from order__C  where opportunity__c = newOpp.id and autocreate__c=True ];

updOrder.order_status__c = newopp.stagename;
updOrder.account__c = newopp.accountid;
Updatelist.add(updOrder);
}
}
insert neworders;
update updatelist;
}

 
spraetzspraetz

I wouldn't take the first proposal because it uses a query with no where clause which will cause you to hit another governor limit eventually.

 

list<Order__c> mpOrder=[select id,opportunity__c,order_status__c,autocreate__c from Order__c];  

 

I wouldn't take the second proposal because it still uses a query inside a for-loop if ordercount == 1 (and I think it won't compile due to a strange reference to NewOpp in the middle there)

 

for(Opportunity NewOpp: trigger.new) {  

Integer orderCount = oppCountMap.get(newOpp.id);
if (orderCount == 0 )
{
order__c a1 = new order__c();
a1.opportunity__c = newOpp.id;
a1.order_status__c = newopp.stagename;
a1.account__c = newopp.accountid;

a1.autocreate__c = True;
neworders.add(a1);
}

if (orderCount == 1)
{order__c updOrder = [select id,order_status__c
from order__C  where opportunity__c = newOpp.id and autocreate__c=True ];

updOrder.order_status__c = newopp.stagename;
updOrder.account__c = newopp.accountid;
Updatelist.add(updOrder);
}
}

 
spraetzspraetz

trigger CreateOrderRec4 on Opportunity (after update, after insert) {

    List<order__C> newOrders = new List<order__c>();
    List<order__C> Updatelist = new List<order__c>();
    Set<ID> oppIds = new Set<ID>();
   
    for(Opportunity NewOpp : Trigger.new){
        oppIds.add(NewOpp.id);
    }
   
    AggregateResult[] groupedResults = [SELECT COUNT(id) c, Opportunity__c FROM Order__c WHERE Opportunity__c in :oppIds GROUP BY Opportunity__c];
   
    Map<ID, Integer> NumberOfOrdersMap = new Map<ID, Integer>();
   
    for(AggregateResult ar : groupedResults){
        NumberOfOrderMap.put(ar.get('Opportunity__c'), ar.get('c'));
    }
   
    Map<ID, Order__c> OrderMap = new Map<ID, Order__c>([select id,order_status__c from order__C  where opportunity__c in :OppIds and autocreate__c=True]); 
   
    for(Opportunity NewOpp : Trigger.new){
        if(NumberOfOrderMap.get(NewOpp.id) != null && NumberOfOrderMap.get(NewOpp.id) == 0){
            //Your logic is here
            order__c a1 = new order__c();
            a1.opportunity__c = newOpp.id;
            a1.order_status__c = newopp.stagename;
            a1.account__c = newopp.accountid;
           
            a1.autocreate__c = True;
            neworders.add(a1);
            //End your logic
        }
        else if(NumberOfOrderMap.get(NewOpp.id) != null && NumberOfOrderMap.get(NewOpp.id) == 1){
            order__c updOrder = OrderMap.get(NewOpp.id);     
            updOrder.order_status__c = newopp.stagename;
            updOrder.account__c = newopp.accountid;
            Updatelist.add(updOrder);
        }
       
        insert neworders;
        update updatelist;
    }

 

Using the aggregate query might not be the best idea, instead I'd use the same query that I used to create the OrderMap and iterate through that and create my own Map of OpportunityId to # of orders to be used later on . I started down the aggregate route before I realized you actually needed to select the individual fields off of an individual order.