Trying to avoid Governor limits in no of SOQL queries

Question

I have 2 custom objects appointment_c and TimeDrive_c.

Appointment has fields

1. startdate__c
2. contact__c

TimeDrive__c has

1. Target_date__c
2. contact__c
3. CreatedDate

Here is what I need to do

I need to get all records within a specific date range

select id, Target_date__c, Contact__c,  Name, CreatedDate

                    from TimeDrive__c  where Target_date__c >=:startdate  and 

                    Target_date__c <=:enddate

I need to loop through each record in this list and check if there are appointments for this contact which has startdate fall between targetdate and createddate

Here is the bit I have done till now

 timeDriverLst = [select id, Target_date__c, Contact__c,  Name, CreatedDate

                    from TimeDrive__c  where Target_date__c >=:startdate  and 

                    Target_date__c <=:enddate ];

    if(timeDriverLst.size() >0){

    for(integer i =0; i < timeDriverLst.size(); i++)

    {

        mapTime.put(timeDriverLst[i].id, timeDriverLst[i]);

       /* appLst = [Select Name, Contact__c from Appointment__c where (StartDate__c > = :timeDriverLst[i].CreatedDate 

                 and StartDateTime__c <=:timeDriverLst[i].Target_date__c) and Contact__c = :timeDriverLst[i].Contact__c ];   

           */      

    }

I know I shouldn't have a SOQL query within a for a loop. How can I avoid this and achieve the requirement?

Answer 1

You could get all the contact IDs from the time driver list and also get the earliest planned date. Then you could pull out all the appointments whose contact IDis in the contact id list and whose date is between the earliest created date and the target date. Then you would need to do a double loop, checking each appointment against each time driver. (Ordering the appts by contact or by date as you retrieve them might help here).

Go for this in-depth job-oriented salesforce training online now!