+ Start a Discussion
davidjbbdavidjbb 

APEX SOQL LIMIT

Hello,

 

 

How can I rewrite the following so I don't run the soql in the for loop, however,

each Workorder has it's own material list of items.

 

		           	
                for(jbbfc2__Workorder__c wo : workorderList){  
		         materialUList = [select jbbfc2__Workorder__c, Name
                                from jbbfc2__MaterialU__c 
                                where jbbfc2__Workorder__r.jbbfc2__Status__c ='Closed' AND jbbfc2__Workorder__c =:wo.id];
 	             
                  InvoiceObj invObjItem = new InvoiceObj(wo, wrMap, materialUList);
                  this.invObjList.add(invObjItem);
                 }

 

Best Answer chosen by Admin (Salesforce Developers) 
RustanRustan

capture the ID from the workorderlist by putting it in a set and use that to limit the query.

All Answers

RustanRustan

something like this

 

 

 

 

 

map<ID, jbbfc2__MaterialU__c> muMap = new map<ID, jbbfc2__MaterialU__c>();

 

for(jbbfc2__MaterialU__c mu :[select jbbfc2__Workorder__r.id, jbbfc2__Workorder__c, Name
from jbbfc2__MaterialU__c 
where jbbfc2__Workorder__r.jbbfc2__Status__c ='Closed']
)

{

    muMap.put(m.jbbfc2__Workorder__r.id, mu);

}

 

 

for(jbbfc2__Workorder__c wo : workorderList){


InvoiceObj invObjItem = new InvoiceObj(wo, wrMap, muMap.get(wo.id));
this.invObjList.add(invObjItem);
}

davidjbbdavidjbb

Where "m.jbbfc2__Workorder__r.id"

 

Wouldn't it just be mu.jbbfc2__Workorder__c because that's a lookup. 

 

Also

 

for my InvoiceObj what's the parameter do I put to allow.. muMap.get(wo.id)?

RustanRustan

sorry... no use mu.jbbfc2__Workorder__r.id because you are using the id as the key for the map.

 

and I am not entirely sure about your second question. The muMap.get(wo.id) is using the ID as the key to get the right record.

davidjbbdavidjbb

Oh, but I pass in 

 

"wo" through the invoiceObj which I can get the Id from that, so shouldn't I pass in the map instead for the list of materials

RustanRustan

I am not entirely understanding your question but the reason for separating the two queries and using a map like this is so that you don't hit the SOQL limit.

davidjbbdavidjbb
                  for(jbbfc2__MaterialU__c mu :[select jbbfc2__Workorder__c,jbbfc2__Workorder__r.Id, Name,
                  	jbbfc2__Material__r.jbbfc2__ProductCode__c,jbbfc2__Material__r.jbbfc2__UnitPrice__c,jbbfc2__quantity__c
                  	from jbbfc2__MaterialU__c where jbbfc2__Workorder__r.jbbfc2__Status__c ='Closed'])
                  {
              		  
              		  System.debug('Workorder Id' + mu.jbbfc2__Workorder__r.Id);
              		  System.debug('Material' + mu.Name);
              		  
              		  matList.add(mu);
              		  if(muMap.get(mu.jbbfc2__Workorder__r.Id)==null){	
              		  	
                      	muMap.put(mu.jbbfc2__Workorder__r.Id, matList);
              		  }
                  }

 

For matList.add(mu);

It's adding all the materials to the matList regardless which jbbfc2__Workorder__r.Id it belongs too..How can i fix this? so the matList only has the the MaterialU belonging to the correct jbbfc2__Workorder__r.Id

 

RustanRustan

capture the ID from the workorderlist by putting it in a set and use that to limit the query.

This was selected as the best answer