+ Start a Discussion
tsalbtsalb 

Trigger - SOQL for Users outside of FOR loop

I need some help taking this SOQL outside of the for loop - but i'm having trouble constructing a query (I usually use __r notation within the SOQL) to traverse to related objects - I'm nto sure how to do this for Users since it's not really a related object - not unless they've been enabled on the contact.

 

Requestor__c is a lookup to a contact, which may or may not be an active customer portal user. This is an old piece of code I'm trying to update and make more efficient.

 

trigger changeOrderOwner on Order__c (before insert, before update) {

	try {
		for(Order__c o : Trigger.new) {
			o.OwnerId = [SELECT u.Id FROM User u WHERE u.ContactId = :o.Requester__c].Id;
		}
	}
	catch(Exception e) {
		
	}	

}

 

Best Answer chosen by Admin (Salesforce Developers) 
Jake GmerekJake Gmerek
trigger changeOrderOwner on Order__c (before insert, before update) {
        ID[] ids = new ID[]{};
      	try {
		for(Order__c o : Trigger.new) {
			ids.add(o.Requester__c);
		}

                User[] theusers = [SELECT u.Id FROM User u WHERE u.ContactId IN:ids];
                for(Order__c o : Trigger.new){
                     for(User u: theusers){
                           if (o.Requestor__c == u.contactId){
                               o.OwnerId = u.id;
                           }
                     }
                 }
	}
	catch(Exception e) {
		
	}
	

}

 I know that it is cumbersome, but that is what works.  Let me know if you have any questions.

All Answers

Jake GmerekJake Gmerek
trigger changeOrderOwner on Order__c (before insert, before update) {
        ID[] ids = new ID[]{};
      	try {
		for(Order__c o : Trigger.new) {
			ids.add(o.Requester__c);
		}

                User[] theusers = [SELECT u.Id FROM User u WHERE u.ContactId IN:ids];
                for(Order__c o : Trigger.new){
                     for(User u: theusers){
                           if (o.Requestor__c == u.contactId){
                               o.OwnerId = u.id;
                           }
                     }
                 }
	}
	catch(Exception e) {
		
	}
	

}

 I know that it is cumbersome, but that is what works.  Let me know if you have any questions.

This was selected as the best answer
tsalbtsalb

What's the advantage of using the array of IDs over say, a set? (this is from a different trigger wher ei use a set)

 

	set<id>must=new set<id>();
	for(Order__c o : Trigger.new) {
		must.add(o.Requester__c);
        }

 

craigmhcraigmh

You can also use trigger.newMap for a list of the IDs, no need to create a new one.

tsalbtsalb

Ah, also it's still hitting Too many SOQL Queries:101 during my test class insert.. Traces back to the code below - but I'm not seeing why not - you've already taken that out of the FOR loop. hmm.

 

 User[] theusers = [SELECT Id FROM User WHERE ContactId IN:must];

 

 

Ninja Edit * Ah, there's another trigger where the previous dev uses a query inside a for loop. I'll paste it for reference, but i'm updating it now with the suggestions posted here.

 

trigger setOrderVendor on Proposal__c (before insert, before update) {
    	
	//Update Orders with Proposal Vendor Id
	try {
		for(Proposal__c p : Trigger.new) {
			Order__c o = [SELECT o.Vendor_Name__c FROM Order__c o WHERE o.Id = :p.Order__c];
			if(p.Status__c == 'Accepted') {				
				o.Vendor_Name__c = p.Vendor__c;
				update o;			
			} 
		}
	}
	catch(Exception e) {		
	}	
	
}

 

craigmhcraigmh

Another thing: you can use a Set for the IDs, and then use the contains() method, so you don't have to loop through the IDs looking for a match.

tsalbtsalb

Can you give me a quick reference to both the comment above and this one? I'd appreciate it thanks!

craigmhcraigmh

I believe you need to use the keySet of the newMap Map object, but if I'm wrong, then I guess you'll have to use values()

 

Set<Id> IDs = new Set<Ids>();

(add IDs here)

for(Id newId: trigger.newMap.keySet()) {
   if(IDs.contains(newId)) {
      //do stuff
   }
}