Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Ben Allington 7Ben Allington 7 

Remove SOQL from For Loop (Apex controller - Lightning component)


I am looking for some pointers on how to correctly structure this short apex function. I am using this to populate a lightning component list like so:
User-added image

The issue is I am trying to remove the soql query on the campaigns from the campaignMember.campaignId but need some direction. Any ideas? 
@AuraEnabled
public static List<Campaign> getCampaigns(String conId, String RT){
    List<CampaignMember> cmL = new List<CampaignMember>();
    List<Campaign> cl = new List<Campaign>();
    Custom_Related_Lists__c cusRL =[SELECT Record_Type_API_Name__c, Fields__c FROM Custom_Related_Lists__c WHERE Record_Type_API_Name__c=:RT LIMIT 1];
    sObject r = [SELECT id, name, DeveloperName from RecordType Where sObjectType='Campaign' AND DeveloperName=:RT LIMIT 1];
    cmL = [SELECT campaignId, ContactId FROM CampaignMember Where ContactId=:conId];
    for(CampaignMember c: cmL){
        try{
        Campaign cam = new Campaign();
        String rId = r.id;
        String cId = c.campaignId;
        String query = 'SELECT id, Name, RecordTypeId,' + cusRL.Fields__c + ' from campaign where RecordTypeId=:rId AND id=:cId order by CreatedDate desc';
        cam = Database.query(query);
        System.debug('cam' +cam);
        if(cam!=null){
            cl.add(cam);
        }
        }
        catch(exception e){
            System.debug('error' + e);
        }
    }
    return cl;
}

 
Best Answer chosen by Ben Allington 7
Waqar Hussain SFWaqar Hussain SF
Hi Ben,

Try below code
  
@AuraEnabled
public static List<Campaign> getCampaigns(String conId, String RT){
    List<CampaignMember> cmL = new List<CampaignMember>();
    List<Campaign> cl = new List<Campaign>();
    Custom_Related_Lists__c cusRL =[SELECT Record_Type_API_Name__c, Fields__c FROM Custom_Related_Lists__c WHERE Record_Type_API_Name__c=:RT LIMIT 1];
    sObject r = [SELECT id, name, DeveloperName from RecordType Where sObjectType='Campaign' AND DeveloperName=:RT LIMIT 1];
    cmL = [SELECT campaignId, ContactId FROM CampaignMember Where ContactId=:conId];
	set<Id> CampaignIds = new set<Id>();
	for(CampaignMember c: cmL){
		CampaignIds.add(c.campaignId);
	}
    
        try{
        list<Campaign> cam = new list<Campaign>();
        String rId = r.id;
        String query = 'SELECT id, Name, RecordTypeId,' + cusRL.Fields__c + ' from campaign where RecordTypeId=:rId AND id IN :CampaignIds order by CreatedDate desc';
        cam = Database.query(query);
        System.debug('cam' +cam);
        cl.addAll(cam);
        }
        catch(exception e){
            System.debug('error' + e);
        }
    }
    return cl;
}

 

All Answers

Waqar Hussain SFWaqar Hussain SF
Hi Ben,

Try below code
  
@AuraEnabled
public static List<Campaign> getCampaigns(String conId, String RT){
    List<CampaignMember> cmL = new List<CampaignMember>();
    List<Campaign> cl = new List<Campaign>();
    Custom_Related_Lists__c cusRL =[SELECT Record_Type_API_Name__c, Fields__c FROM Custom_Related_Lists__c WHERE Record_Type_API_Name__c=:RT LIMIT 1];
    sObject r = [SELECT id, name, DeveloperName from RecordType Where sObjectType='Campaign' AND DeveloperName=:RT LIMIT 1];
    cmL = [SELECT campaignId, ContactId FROM CampaignMember Where ContactId=:conId];
	set<Id> CampaignIds = new set<Id>();
	for(CampaignMember c: cmL){
		CampaignIds.add(c.campaignId);
	}
    
        try{
        list<Campaign> cam = new list<Campaign>();
        String rId = r.id;
        String query = 'SELECT id, Name, RecordTypeId,' + cusRL.Fields__c + ' from campaign where RecordTypeId=:rId AND id IN :CampaignIds order by CreatedDate desc';
        cam = Database.query(query);
        System.debug('cam' +cam);
        cl.addAll(cam);
        }
        catch(exception e){
            System.debug('error' + e);
        }
    }
    return cl;
}

 
This was selected as the best answer
Ben Allington 7Ben Allington 7
Worked fantastic, I see the approach and makes perfect sense, Thanks very much