2
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ The query has nothing specific to the loop record... \$\endgroup\$ Commented Jul 26, 2016 at 16:46

1 Answer 1

2
\$\begingroup\$

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
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.