Query parent-to-child with parent-specific WHERE clause

Question

I have a Lead field named LastOpenedDate__c, and in a bulk context I'm trying to get a count of all tasks completed for each lead in a list, but only tasks completed on or after each lead's LastOpenedDate__c.

Conceptually, my code looks like this.

    for(Lead eachLead : [
        SELECT Id, (
            SELECT Id
            FROM Task
            WHERE Status = :TaskUtil.COMPLETED_STATUS
            AND Type IN :TaskUtil.getTouchpointTypeSet()
            AND ActivityDate >= :l.LastOpenedDate__c
        )
        FROM Lead l
        WHERE Id IN :this.recordIds
    ]) {
        this.currentTouchpointsById.put(
                eachLead.Id, Decimal.valueOf(eachLead.Tasks.size()));
    }

The error I get trying to save this code is:

expecting a colon, found 'l.LastOpenedDate__c'

I know what I'm trying to do generally works in an RMDBS with SQL. Is what I'm trying to do possible in SOQL? And if so, what's wrong with my syntax?

Answer 1

Usually, the common solution is to use a formula of type checkbox with, for example, the value:

ActivityDate >= Who.LastOpenedDate__c

Then, in the SOQL query, it would be possible to check if the result of this formula is true by using:

SELECT Id, (
   SELECT Id
   FROM Task
   WHERE Status = :TaskUtil.COMPLETED_STATUS
   AND Type IN :TaskUtil.getTouchpointTypeSet()
   AND Your_New_Formula__c = true
)

The problem here, is that the Task object doesn't know the type of the related human on its WhoId field. It can be a Lead or a Contact. So you won't be able to access the LastOpenedDate__c field.

The only solution I know is to add a trigger on the update and insert events of the task object. In this trigger, you have to query the related lead and then you set a new custom field of type checkbox to: ActivityDate >= :TheRelatedLead.LastOpenedDate__c.

Answer 2

The solution I used in the end was to count the tasks one by one explicitly in code, which I knew was possible but was hoping for more elegant solution.

    // Compile the aggregate query results
    for(Lead eachLead : [
        SELECT Id, LastOpenedDate__c, (
            SELECT Id, ActivityDate
            FROM Tasks
            WHERE Status = :TaskUtil.COMPLETED_STATUS
            AND Type IN :TaskUtil.getTouchpointTypeSet()
        )
        FROM Lead
        WHERE Id IN :this.recordIds
    ]) {

        // Convenient local counters
        Integer touchpoints = 0;
        Integer currentTouchpoints = 0;

        // Count the tasks
        for (Task eachTask : eachLead.Tasks) {

            // Always counts as a historical touchpoint
            touchpoints++;

            // Conditionally counts as a current touchpoint
            if (eachTask.ActivityDate >= eachLead.LastOpenedDate__c) {
                currentTouchpoints++;
            }
        }

        // Remember the counters
        this.touchpointsById.put(eachLead.Id, touchpoints);
        this.currentTouchpointsById.put(eachLead.Id, currentTouchpoints);
    }