too many SOQL workaround for a loop cause a problem

Question

I have a loop that iterates over a list of a class created for managing Json and transform it into records. The problem is that I have a lookup field to a product and he way to find this product is by a SOQL.

I decided to move the SOQL outside of the function. by iterating over the list with slices of 100 record, creating a Set<String> of the field that the SOQL uses to search for a related Product. But then I can't find a way to know which record in this set is associated with the record of the list

The old code, where I would have a query limit problem is this one:

List<ProductRequest> allProductRequest = new List<ProductRequest>();
for (Map<String, Object> record : recordListMap){
    Json2Apex productRequestContainer = (Json2Apex)JSON.deserialize(JSON.serialize(record), Json2Apex.class);
    allProductRequest.add(productRequestContainer.transformToProduct());
}

public class Json2Apex{
    public String SOL_CODE;
    
    public ProductRequest transformToProduct() {
        List<ProductRequest> product_Request = [SELECT Id FROM ProductRequest WHERE QuickBase_Record_Id__c = :Record_ID AND Ref_Number_RD__c != '' LIMIT 1];
        if (product_Request.isEmpty()){
            product_Request.add(New ProductRequest(RecordTypeId = '012S0000003UpaDIAS', Status = 'Request_Received'));
        }
        
        product_Request[0].D365_Product_Code__c = decimal.ValueOf(SOL_CODE);
        
        
        List<Product2> product = [SELECT Id FROM Product2 WHERE ProductCode = :SOL_CODE LIMIT 1];
        if (!product.isEmpty()){
            product_Request[0].Associated_Product__c = product[0].Id;
        }
        
        return product_Request[0];
    } 
}

this code will quickly exceed the limit of 100 queries,I then thought of an alternative where I would do 1 query every 100 record. It is probably not optimized, but it would allow me to do the equivalent of 10000 queries if needed.

Integer queryLimit = 100;//iterate by chuck of 100 to not exceed SOQL limit
for (Integer i = 0; i < recordListMap.size(); i+=queryLimit) {
    Set<String> productCodes = new Set<String>();
    for (Integer j = 0; j < queryLimit && (queryLimit*i+j) < recordListMap.size(); j++) {
        productCodes.add((String)recordListMap[queryLimit*i+j].get('SOL_CODE'));
    }
            
    List<Product2> product = [SELECT Id FROM Product2 WHERE ProductCode IN :productCodes LIMIT 100];
            
    for (Integer j = 0; j < queryLimit && (queryLimit*i+j) < recordListMap.size(); j++) {
        Json2Apex productRequestContainer = (Json2Apex)JSON.deserialize(JSON.serialize(recordListMap[queryLimit*i+j]), Json2Apex.class);
            allProductRequest.add(productRequestContainer.transformToProduct());
    }
}

but I have soon relised that my list with 100 Products is unusable, since there is no way of knowing wich product is asociated with wich productRequest.

Actually, I know that it is possible to make a map from a query. it would look like:

Map<ID, Product2> m = new Map<ID, Product2>([SELECT Id FROM Product2 WHERE ProductCode IN :productCodes LIMIT 100]);

But I am not satisfied with this, the ID is the key, while my way of finding a Product is by the SOL_CODE. Is there a way to turn around the map. so the keys can be ProductCode field and the values be the id's? Or just a better way to workaround the problem to too many queries.

Thank you

Answer 1

You can make a map from a query, just like you said, but you can also create a map and populate it by iterating over a list.

First you need to declare a map:

Map<String,Id> productCodeProductMap = new Map<String,Id>();

Iterate over the "product" list (recommendation -> use plural variables for lists) and "put" key-value pairs in the map:

for(Product2 currentProduct : product){
   productCodeProductMap.put(currentProduct.ProductCode,currentProduct.Id);
}

Then you can get the Product Id by passing the Product Code to the Map, like this:

Id productId = productCodeProductMap.get('productCode');

Where 'productCode' is a string with the code of the product you are looking for (product_Request[i].D365_Product_Code__c).

BTW! Do not hard code 'Ids', like never. The reason is because Sandbox Ids and Production Ids are different. This:

New ProductRequest(RecordTypeId = '012S0000003UpaDIAS', Status = 'Request_Received')

Is a really bad practice. Try getting the RecordTypeId prior to this assignment:

Id productRecordTypeId = Schema.SObjectType.Product2.getRecordTypeInfosByName().get('Product_RecordType_Name').getRecordTypeId();

Where 'Product_RecordType_Name' is a string with the name of the Record Type.

Then you can use the value stored in productRecordTypeId to achieve what you want to do:

New ProductRequest(RecordTypeId = productRecordTypeId, Status = 'Request_Received')

Hoping this is helpful!