Remove SOQL query inside a loop

Question

I am coding a HttpPatch and I need help to remove the query inside the loop (line 13). Is it possible?

 @HttpPatch
global static void EstablishmentUpdateDelete(
    String actionType,
    String cuit,
    List<Establishment> establishments
){
    RestRequest req = RestContext.request;
    RestResponse res = RestContext.response;
    Account acc = [SELECT Id FROM Account WHERE CUITCUIL__c =:cuit];
    //String establishmentCode = req.requestURI.substring(req.requestURI.lastIndexOf('/')+1);

    for (Establishment est : establishments) {
        List<Establishment__c> ests = [SELECT Id, Cobro_Anticipado__c, EstablishmentCode__c 
                                      FROM Establishment__c 
                                      WHERE EstablishmentCode__c =:est.establishmentCode AND Account__c =:acc.Id];

        if(ests.size() > 0){
            if(actionType == 'A'){
                ests.Codigo_de_Tasa__c = '67';
                ests.Cobro_Anticipado__c = true;
                if(ests.Cobro_Anticipado__c != CA){
                    ests.Alta_Baja_CobroAnticipado__c = system.today();
                }
                res.statusCode = 200;
                update ests;
            } else if(actionType == 'B'){
                ests.Codigo_de_Tasa__c = null;
                ests.Cobro_Anticipado__c = false;
                    if(ests.Cobro_Anticipado__c != CA){
                        ests.Alta_Baja_CobroAnticipado__c = date.today();
                    }
                    res.statusCode = 200;
                    update ests;
            }
        }else{
            WrapCuitNotFound accNotFound = new WrapCuitNotFound();
            accNotFound.message = 'Establecimiento no econtrado';
            res.responseBody = Blob.valueOf(JSON.serialize(accNotFound));
            res.statusCode = 200;
        }
    }
}

Answer 1

You can remove the query on the account and just use SOQL for loop, use the set or map to filter records based on your requirements -

Set<String> establishmentCodes = new Set<String>();
for(Establishment estCode : establishments) {
    establishmentCodes.add(estCode);
}

OR if you need to maintain the mapping of Id to EstablishmentCode then you can create a map of Id -> EstablishmentCode__c like Map<Id,String> and filter your records by values of map and then check in your SOQL loop for existence of EstablishmentCode using the map for individual records.

for (Establishment est : [SELECT Id, Cobro_Anticipado__c, EstablishmentCode__c 
                           FROM Establishment__c 
                           WHERE EstablishmentCode__c IN: establishmentCodes 
                           AND Account__r.CUITCUIL__c =:cuit]) {
    //Your code inside for loop
}        
 

This will solve your problem.