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\$
  • \$\begingroup\$ The query has nothing specific to the loop record... \$\endgroup\$ – Adrian Larson Jul 26 '16 at 16:46
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, privacy policy and cookie policy

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