+ Start a Discussion
Joseph BaroneJoseph Barone 

Need help bulkifing my SOQL code

Hello All,

I need help cleaning up my code as I continually hit Too many SOQL queries: 101.  Upon reading the forums, I understand the reasoning for this issue, but cannot figure out a way around it.  

What I'm trying to do is create a list of all Activities per an Account.  The VF page and code is activated by a detailed link button on the page.  The account id is passed into the Controller and is used to query the EventRelations for all Events where AccountID = specific account id.  From the EventRelations query, I want to take the Event ID and query all the pertanent Event info.  The code I have below works fine, except when I have accounts with over X amount of activities.  

I've been able to write a query in the form of [Select ..., (Select ... From EventRelations) From Events ... ], but we have over 25000 events.  Querying the EventRelations first, helps limit the amount of Events that I need to look at. 

Any help would be greatly appreciated.  

Thank you
 
public class ActivitiesbyAccountController {

    public string idCurrentEvent {get;set;}
    Public Event evententry;
    Public List <EventRelation> eventconnect;
    Public List <Event> selected {get;set;}
    
    
    public ActivitiesbyAccountController(ApexPages.StandardController Account)
    {
        
        idCurrentEvent = ApexPages.currentPage().getParameters().get('id');
        
        
        selected=new list<Event>();
        
        eventconnect = [Select ID, relationid, eventid FROM EventRelation Where accountid = :idCurrentEvent AND IsDeleted = False];
        
        for(integer i=0;i<eventconnect.size();i++)
        {
         	
            if(eventconnect.get(i).relationid != idCurrentEvent)
       			  {
             		
                      Event newevent = new Event();
          			string tempeventid = eventconnect.get(i).eventid;
					 
          			newevent = [Select ID, OwnerID, subject, purpose_of_meeting__c, ActivityDate,WhatID From Event WHERE id = :tempeventid LIMIT 1000 ALL ROWS];
          			newevent.whoid = eventconnect.get(i).relationid;  
      				
        			selected.add(newevent);  
        		 }
            
         }
     }
}

 
Best Answer chosen by Joseph Barone
Alexander TsitsuraAlexander Tsitsura
Try this code
 
public class ActivitiesbyAccountController {

    public string idCurrentEvent {get;set;}
    Public Event evententry;
    Public List <EventRelation> eventconnect;
    Public List <Event> selected {get;set;}
    
    
    public ActivitiesbyAccountController(ApexPages.StandardController Account)
    {
        
        idCurrentEvent = ApexPages.currentPage().getParameters().get('id');
        
        
        selected=new list<Event>();
        
        eventconnect = [Select ID, relationid, eventid FROM EventRelation Where accountid = :idCurrentEvent AND IsDeleted = False];
        
      
       Set<String> eventIds = new Set<String>();       
        for(integer i=0;i<eventconnect.size();i++)
        {
           if(eventconnect.get(i).relationid != idCurrentEvent)
           {
               eventIds.add(eventconnect.get(i).eventid);
           }
		}


		Map<Id, Event> eventsMap = new Map<Id, Event>([Select ID, OwnerID, subject, purpose_of_meeting__c, 
			ActivityDate,WhatID From Event WHERE id IN :eventIds LIMIT 1000 ALL ROWS]);

        for(integer i=0;i<eventconnect.size();i++)
        {
         	
            if(eventconnect.get(i).relationid != idCurrentEvent)
       		 {
             		string tempeventid = eventconnect.get(i).eventid;

                    Event newevent = eventsMap.get(tempeventid);
					newevent.whoid = eventconnect.get(i).relationid;  
      				
        			selected.add(newevent);  
        		 }
            
         }
     }
}

As a common practice, if your question is answered, please choose 1 best answer. 
But you can give every answer a thumb up if that answer is helpful to you.

Thanks,
Alex

All Answers

Alexander TsitsuraAlexander Tsitsura
Hi Joseph Barone

At the first, you need create a set of "tempeventid" and out in this set all needed eventid. After that you can query all needed Event and put record to map where key is Event Id.

And after that write second for for perform specific logic

Thanks,
Alex
Alexander TsitsuraAlexander Tsitsura
Try this code
 
public class ActivitiesbyAccountController {

    public string idCurrentEvent {get;set;}
    Public Event evententry;
    Public List <EventRelation> eventconnect;
    Public List <Event> selected {get;set;}
    
    
    public ActivitiesbyAccountController(ApexPages.StandardController Account)
    {
        
        idCurrentEvent = ApexPages.currentPage().getParameters().get('id');
        
        
        selected=new list<Event>();
        
        eventconnect = [Select ID, relationid, eventid FROM EventRelation Where accountid = :idCurrentEvent AND IsDeleted = False];
        
      
       Set<String> eventIds = new Set<String>();       
        for(integer i=0;i<eventconnect.size();i++)
        {
           if(eventconnect.get(i).relationid != idCurrentEvent)
           {
               eventIds.add(eventconnect.get(i).eventid);
           }
		}


		Map<Id, Event> eventsMap = new Map<Id, Event>([Select ID, OwnerID, subject, purpose_of_meeting__c, 
			ActivityDate,WhatID From Event WHERE id IN :eventIds LIMIT 1000 ALL ROWS]);

        for(integer i=0;i<eventconnect.size();i++)
        {
         	
            if(eventconnect.get(i).relationid != idCurrentEvent)
       		 {
             		string tempeventid = eventconnect.get(i).eventid;

                    Event newevent = eventsMap.get(tempeventid);
					newevent.whoid = eventconnect.get(i).relationid;  
      				
        			selected.add(newevent);  
        		 }
            
         }
     }
}

As a common practice, if your question is answered, please choose 1 best answer. 
But you can give every answer a thumb up if that answer is helpful to you.

Thanks,
Alex
This was selected as the best answer
Alexander TsitsuraAlexander Tsitsura
Sorry, i forgot about 'LIMIT 1000', you need increase this limit