How to remove a SOQL query from a loop

Question

I have a query for Opportunity records which are retrieved based on Account Id. I am not sure how I can remove them out of the for loop

I had to use the Opportunity for each account in the list, because for each Account I need to make the dml operation for Opportunity for each Opportunity record.

I am storing the Account Ids based on the other operation in AcList.

if(AcLst.size()>0){
    for(Id a: AcLst){
        List<Opportunity> ol = [SELECT Id, StageName, AccountId, (SELECT Id,Product2.Name FROM OpportunityLineItems WHERE Product2.Name like :prodname) FROM Opportunity WHERE AccountId IN :AcLst];
        for (Opportunity o : ol) {
            oppList = mapAccountIdOpportunityList.get(o.AccountId);        

            if (oppList == null) { 
                for(String a: AcCon.keyset()){
                    for(Id c : AcCon.values()){ 
                        Opportunity opp = new Opportunity();
                        opp.Name = 'Test One';
                        opp.AccountId = a;
                        opp.Contact__c = c;
                        opp.CloseDate= Date.newInstance(2022, 03, 01);
                        opp.StageName='Proposal';
                        mapAccountIdOpportunityList.put(opp.AccountId, oppList);
                        oppList.add(opp);
                    }
                }
            }
        }
    }
}

// this list says null even after adding opp

If I create a new list and then try adding opp, it adds the opp twice

Answer 1

Since you already have a list of Ids (AcLst) you could query every Opportunity related to those Accounts, then populate a Map using the accountId as key and the list of opportunity as value.

Map<Id, List<Opportunity>> mapAccountIdOpportunityList = new Map<Id, List<Opportunity>>();
for (Opportunity o : [SELECT Id, StageName, AccountId, (SELECT Id,Product2.Name FROM OpportunityLineItems WHERE Product2.Name like :prodname) FROM Opportunity WHERE AccountId IN :AcLst]) {
    List<Opportunity> oppList = mapAccountIdOpportunityList.get(o.AccountId);
    // If it's the first opportunity retrieved for the related account
    if (oppList == null) { 
        oppList = new List<Opportunity>();
        mapAccountIdOpportunityList.put(o.AccountId, oppList);
    }
    oppList.add(o);
}

for (Id accId : AcLst) {
    List<Opportunity> oppList = mapAccountIdOpportunityList.get(accId);
    if (oppList != null) {
        // the desired behavior
    }
}

Example: you have three accounts: A1, A2 and A3.
A1 has two opportunities: O10 and O11.
A2 has no opportunity at all.
A3 has only one opportunity: O30.

After the for-loop the map will contain two entries:

1. key: A1 - value: [O10, O11]
2. key: A3 - value: [O30]

If you're going to loop on AcLst to retrieve the opportunities, you'll have to check whether there is such key into the map.
To do so, you could either wrap your code within an if (map.containsKey(accountId)) or simply get the list from the map then check if it's != null.

Added my code on the top

Could you please explain in which cases you have to create a new opportunity?
From your code I understand that you have to create it for every account in AcCon map. If not, if you want to create a new opportunity only for the accounts which have no one, such code (and therefore the one I wrote below) must be changed.

Anyway here's how I would refactor it:

if(AcLst.size()>0){
    // Create a map
    Map<Id, List<Opportunity>> mapAccountIdOpportunityList = new Map<Id, List<Opportunity>>();
    // Populate the map getting every opportunity related to the account in the list.
    for (Opportunity o : [SELECT Id, StageName, AccountId, (SELECT Id,Product2.Name FROM OpportunityLineItems WHERE Product2.Name like :prodname) FROM Opportunity WHERE AccountId IN :AcLst]) {
        List<Opportunity> oppList = mapAccountIdOpportunityList.get(o.AccountId);
        // If it's the first opportunity retrieved for the related account
        if (oppList == null) {
            oppList = new List<Opportunity>();
            mapAccountIdOpportunityList.put(o.AccountId, oppList);
        }
        oppList.add(o);
    }

    // If you want to create a new opportunity for every account in AcCon
    List<Opportunity> oppToInsert = new List<Opportunity>();
    // since the date is always 2022-03-01, there is no need to create a new instance for every iteration
    Date expectedCloseDate = Date.newInstance(2022, 03, 01);
    for(Id a: AcCon.keyset()){
        Id c = AcCon.get(a);
        List<Opportunity> oppList = mapAccountIdOpportunityList.put(a, oppList);
        // If no opportunity for this account has been retrieved by the previous query
        if (oppList == null) {
            oppList = new List<Opportunity>();
            mapAccountIdOpportunityList.put(a, oppList);
        }
        Opportunity opp = new Opportunity();
        opp.Name = 'Test One';
        opp.AccountId = a;
        opp.Contact__c = c;
        opp.CloseDate = expectedCloseDate;
        opp.StageName ='Proposal';
        oppList.add(opp);
        oppToInsert.add(opp);
    }

    // If it ends here, you should uncomment the next line
    // insert oppToInsert;
}