Refactoring APEX to keep all database calls outside of for loops

Question

I have the following APEX code. I have been trying for hours to try and refactor it so that the SOQL query is outside of the for loop without any luck. Any ideas?

public static void ErrorIfWorklogsOverlap(List<Worklog__c> worklogs) {
        for (Worklog__c worklog : worklogs) {
            List<Worklog__c> timesheetWorklogs = [
                    SELECT
                            Id
                    FROM Worklog__c
                    WHERE Timesheet__c IN (
                            SELECT
                                    Id
                            FROM Timesheet__c
                    )
            ];
            DateTime worklogStartTime = worklog.Start_Time__c;
            DateTime worklogEndTime = worklog.End_Time__c;
            for (Worklog__c checkLog : timesheetWorklogs) {
                DateTime checkLogStart = checkLog.Start_Time__c;
                DateTime checkLogEnd = checkLog.End_Time__c;
                if (worklogStartTime > checkLogStart && worklogStartTime < checkLogEnd) {
                    worklog.Start_Time__c.addError('Worklog\'s start time is within the bounds of another worklog');
                } else if (worklogEndTime < checkLogEnd && worklogEndTime > checkLogStart) {
                    worklog.End_Time__c.addError('Worklog\'s end time is within the bounds of another worklog');
                } else if (worklogStartTime < checkLogStart && worklogEndTime > checkLogEnd) {
                    worklog.addError('Worklog overlaps another worklog');
                }
            }
        }
    }

A Worklog belongs to a Timesheet. This code is supposed to be checking if any of the Worklogs in the list worklogs overlaps another Worklog in the same Timesheet.

Answer 1

The first step would be to create a set of Timelogs that you are interested in and then query for the related Worklogs. Querying Timelogs and getting child Worklogs is probably the simplest solution.

Sample code:

Set<Id> timelogIds = new Set<Id>();
for (Worklog__c worklog : worklogs) {
  timelogIds.add(worklog.Timelog__c);
}

// Get timelogs and related child worklogs
Map<Id, Timelog__c> timeLogs = new Map<Id, Timelog__c>(
                             [SELECT Id,
                             (SELECT ... FROM Worklogs__r) //query child relationship
                             FROM Timelog__c 
                             WHERE Id IN :timelogIds]);

for (Worklog__c worklog : worklogs) {
  List<Worklog__c> tempList = timeLogs.get(worklog.Timelog__c).Worklogs__r;
  // iterate through tempList to check for overlapping records
}