How to remove SOQL query from my FOR loop

Question

I am creating some code that takes the accountId From a custom object and copies it to the Case object's accountId field based on if the URL from the custom object matched the URL from the case object. Below is the code I have created. It works but there is a SOQL query in the for loop which is bad practice. How would I go about removing the SOQL from the For loop?

Code:

 for(Integer i=0; i < triggerNew.size(); i++){
        Case newCase = triggerNew[i];
         if(newCase.AppURL__c != null){
          String cloud = newCase.AppURL__c;
             system.debug(cloud);
             List<MobileCloud_Website__c> acc = [select Account__r.Id from MobileCloud_Website__c WHERE URL__c =: cloud];
             system.debug(acc);
             newCase.AccountId = acc[0].Account__r.Id;

         }
     }

I am aware the best way to do this would be to use a Set and Map, but I am stuck as to how to use those collections properly. Below was as far as I got before my brain gave up.

Code:

        Set<MobileCloud_Website__c> accIds = new Set<MobileCloud_Website__c>([select Account__r.Id, URL__c from MobileCloud_Website__c WHERE URL__c != null]);
    Map <String, Id> caseURLs = new Map<String, Id>();
     for(Integer i=0; i < triggerNew.size(); i++){
        Case newCase = triggerNew[i];
         if(newCase.AppURL__c != null){
            caseURLs.put(newCase.AppURL__c, newCase.AccountId);

         }

Answer 1

1. Aggregate the filter values.

   Set<String> urls = new Set<String>();
   for (Case record : trigger.new)
       urls.add(record.AppUrl__c);
   

2. Iterate the query results and map them by the filter field.

   Map<String, MobileCloud_Website__c> websites = new Map<String, MobileCloud_Website__c>();
   for (MobileCloud_Website__c website : [
       SELECT Account__c
       FROM MobileCloud_Website__c
       WHERE Url__c IN :urls
   ]) websites.put(website.Url__c, website);
   

   Note that you should just pull the Id stored in the database via __c rather than __r.Id. Also note that if this field is not unique and you have duplicates, it is arbitrary which one will win. Given the same scenario, the code as you wrote it would have thrown an error.

3. Retrieve the records from your Map using the field value.

   for (Case record : trigger.new)
   {
       MobileCloud_Website__c website = websites.get(record.AppUrl__c);
   }