Task relationship to Opportunity in sub query

Question

I'm attempting to create a Batch class that will Stamp Opps with their Next Activity Date.

I haven't been able to figure out the relationship between Opps and Tasks? I’ve tried: Task_r, Tasks_r, Activities__r, Activity __r with no luck

Error: Variable does not exist: Activities__r

Thoughts? Code:

public class BatchNextOpportunityActivity implements Database.Batchable<sObject>, Database.Stateful, Schedulable{

//variable to hold any errors
private Set<String> apexErrors;

//List to hold Opportunities to be updated
List <Opportunity> oppsToUpdate = new List <Opportunity>();

//instantiate the class
public BatchNextOpportunityActivity(){
    
    Set<String> apexErrors = new Set<String>();
    this.apexErrors = apexErrors;

    List <Opportunity> oppsToUpdate = new List <Opportunity>();
    this.oppsToUpdate = oppsToUpdate;
}

public void execute(SchedulableContext SC) {
    Database.executeBatch( new BatchNextOpportunityActivity() );
}

public Database.QueryLocator start(Database.BatchableContext BC){
    
    String query =  'Select ID, Next_Activity__c,(ActivityDate FROM Activities__r  WHERE ActivityDate > Date,Today() ORDER BY ActivityDate desc) from Opportunity WHERE CreatedDate = LAST 120 DAYS';
    
    return Database.getQueryLocator(query);
}

    public void execute(Database.BatchableContext BC, List<Opportunity> scope){
    
    system.debug('scope.size = '+scope.size()); 
    
    for (Opportunity opp : scope){
        //If the ActivityDate of the 1st Task is greater than the Opps Next_Activity__c then let's update it with the Task's ActivityDate
        if (opp.Activities__r.size()>0 && opp.Activities__r[0].ActivityDate > opp.Next_Activity__c) {
            opp.Next_Activity__c = opp.Activities__r[0].ActivityDate;
            oppsToUpdate.add(opp);
        }
        
        //If there's no Tasks found and the Next_Activity__c field is populated lets clear it 
        Else if (opp.Next_Activity__c != null) {
            opp.Next_Activity__c = null;
            oppsToUpdate.add(opp);
        }
    }
}

Answer 1

You only add __r when it's a custom relationship.

Since the relationship between Task and Opportunity is a standard one, you don't add __r.

The child relationship name you're looking for here is Tasks, Events, or ActivityHistories

SELECT Id, (SELECT Id, Subject FROM Tasks) FROM Opportunity

Answer 2

If you're looking for the next open activity, your query should be:

SELECT (SELECT ActivityDate FROM OpenActivities ORDER BY ActivityDate ASC NULLS LAST LIMIT 1) FROM Opportunity

However, to avoid the "slow queue" for batch processing, the query should be performed in the execute method:

public void execute(Database.BatchableContext BC, List<Opportunity> scope){
    scope = [SELECT (SELECT ActivityDate FROM OpenActivities ORDER BY ActivityDate ASC NULLS LAST LIMIT 1) FROM Opportunity WHERE Id = :scope];
    // rest of logic here //

As a side note, don't use the String query method for getting the query locator, as it helps you avoid typos and accidentally renaming/deleting fields.

return Database.getQueryLocator([SELECT Id FROM Opportunity]);