+ Start a Discussion
Alex LazarevAlex Lazarev 

Example of Select query to the DB of a list of IDs

Hello guys,

I'm doing a Trigger, but since I have short experience I need some help please. I have the following Trigger that shoots when there is an attemp of insert into the custom Object. This works fine for a new record. But the logic that I'm missing is for bulk of Inserts where my Company told me that we should take the SELECT out of the loop and first build a list of the IDs and then run a single query with
IN : varibleWithIds

Here is my piece of code, how can I take the queries out of the 'for' statement and do a single 'SELECT' for the List of ids??
 
trigger AddOnlyVehiclesInStock on Order_Item__c (before insert) {
    
    // String to storage vehicle type
    Set<String> orderVehicle = 	New Set<String>();
    
    // Going through the Order_Item__c object to assign the field data to the variable orderVehicle
    for(Order_Item__c o : Trigger.New){
        orderVehicle.add(o.Vehicle_Type__c);
        
        System.debug('1-- Order_Item__c.Vehicle_Type__c: ' + orderVehicle);	
        
        	// Getting Name and quantity of the searched vehicle -- This should get the List of IDs and not loop inside a FOR
            List<Vehicle__c> v = [SELECT Name,Quantity__c FROM Vehicle__c Where Name IN : orderVehicle];
            for(Vehicle__c ve : v){
                System.debug('2-- Vehicle query debug: ' + ve);
                
                // If the vehicle that we are trying to add to the order has less than 1 in stock
                // then we cannot add it.
                System.debug('3-- Vehicle quatity: ' + ve.Quantity__c);
                
                if(ve.Quantity__c < 1){
                    o.addError('Cannot add to order vehicles without stock');
                }
            }
    }
}

Thank you!
Best Answer chosen by Alex Lazarev
Ashish DevAshish Dev
Hi Alex,

Simply take query and inner for loop out of outer for loop. It will be like. 
trigger AddOnlyVehiclesInStock on Order_Item__c (before insert) {
    
    // String to storage vehicle type
    Set<String> orderVehicle = 	New Set<String>();
    
    // Going through the Order_Item__c object to assign the field data to the variable orderVehicle
    for(Order_Item__c o : Trigger.New){
        orderVehicle.add(o.Vehicle_Type__c);
        
        System.debug('1-- Order_Item__c.Vehicle_Type__c: ' + orderVehicle);	
        
        	// Getting Name and quantity of the searched vehicle -- This should get the List of IDs and not loop inside a FOR

    }
	List<Vehicle__c> v = [SELECT Name,Quantity__c FROM Vehicle__c Where Name IN : orderVehicle];
	for(Vehicle__c ve : v){
		System.debug('2-- Vehicle query debug: ' + ve);
		
		// If the vehicle that we are trying to add to the order has less than 1 in stock
		// then we cannot add it.
		System.debug('3-- Vehicle quatity: ' + ve.Quantity__c);
		
		if(ve.Quantity__c < 1){
			o.addError('Cannot add to order vehicles without stock');
		}
	}
}

Let me know if this solves your problem.

All Answers

Ashish DevAshish Dev
Hi Alex,

Simply take query and inner for loop out of outer for loop. It will be like. 
trigger AddOnlyVehiclesInStock on Order_Item__c (before insert) {
    
    // String to storage vehicle type
    Set<String> orderVehicle = 	New Set<String>();
    
    // Going through the Order_Item__c object to assign the field data to the variable orderVehicle
    for(Order_Item__c o : Trigger.New){
        orderVehicle.add(o.Vehicle_Type__c);
        
        System.debug('1-- Order_Item__c.Vehicle_Type__c: ' + orderVehicle);	
        
        	// Getting Name and quantity of the searched vehicle -- This should get the List of IDs and not loop inside a FOR

    }
	List<Vehicle__c> v = [SELECT Name,Quantity__c FROM Vehicle__c Where Name IN : orderVehicle];
	for(Vehicle__c ve : v){
		System.debug('2-- Vehicle query debug: ' + ve);
		
		// If the vehicle that we are trying to add to the order has less than 1 in stock
		// then we cannot add it.
		System.debug('3-- Vehicle quatity: ' + ve.Quantity__c);
		
		if(ve.Quantity__c < 1){
			o.addError('Cannot add to order vehicles without stock');
		}
	}
}

Let me know if this solves your problem.
This was selected as the best answer
Alex LazarevAlex Lazarev
Ashish Dev
First of all thank you for your help, I just began and it's not easy for me sometimes to see this, might be, obvious solutions.

Clear up this for me please: With this modification, in case I shoot a bulk data to be inserted at the object 'Order_Item__c ', the script will still check for the 'Vehicle_Type__c' but the query will be executed once it completes the loop of the 'Set<String> orderVehicle' , right?
Ashish DevAshish Dev
Yes thats right.
query should never be in loop, otherwise you will hit maximum number of queries limit imposed by salesforce. In salesforce terminology it is called bulkified trigger.
Alex LazarevAlex Lazarev
Now its saying that Variable does not exist: o

I assume that its because now those are 2 different loops. But how can I reffer to the Order_Item__c to add and error message then?