SOQL Inner Join Query

Question

I am just wondering if I could write a SOQL query for this:

• Find list of Lead

  • That have an open Task assigned to specific User and
  • Where the Lead.CreatedDate equals the Task.CreatedDate?

Answer 1

If you are willing to add some architecture you can enable this with a simple checkbox on Lead called something along the lines of Has_Task_Created_Same_Day__c. Then you would write a Task trigger or maybe use Process Builder to build a flow. That trigger or flow would just have to look at records created by your special User with a WhatId corresponds to a Lead, and see if the Lead.CreatedDate was today.

If you want it now, you can't accomplish what you want with pure SOQL; you will need to do some post-processing. You can find all relevant Task records and generate a Map<Id, Set<Date>> corresponding to theCreatedDate values (Date part only). Once you have that you can compare with your Lead records.

Here is some untested code to give you the gist:

static final User SPECIAL_USER; // lazy load someone via query
public static List<Lead> getSomeLeadSubset()
{
    List<Lead> subset = new List<Lead>();
    Map<Id, Set<Date>> taskIdToCreatedDates = getTaskCreatedDates(getLeadIdsWithTask());
    for (Lead lead : [
        SELECT CreatedDate FROM Lead WHERE Id IN :taskIdToCreatedDates.keySet()
    ]){
        Set<Date> taskCreatedDates = new Set<Date>();
        if (taskCreatedDates != null && taskCreatedDates.contains(lead.CreatedDate.date()))
            subset.add(lead);
    }
    return subset;
}
public static Set<Id> getLeadIdsWithSpecialUserTask()
{
    Set<Id> leadIds = new Set<Id>();
    for (AggregateResult aggregate : [
        SELECT WhatId FROM Task WHERE What.Type = 'Lead'
        AND OwnerId = :SPECIAL_USER.Id GROUP BY WhatId
    ]){
        leadIds.add(aggregate.get('WhatId'));
    }
    return leadIds;
}
public static Map<Id, Set<Date>> getTaskCreatedDates(Set<Id> leadIds)
{
    Map<Id, Set<Date>> taskIdToCreatedDates = new Map<Id, Set<Date>>();
    for (Task task : [SELECT WhatId, CreatedDate FROM Task WHERE WhatId IN :leadIds])
    {
        if (!taskIdToCreatedDates.containsKey(task.WhatId))
            taskIdToCreatedDates.put(task.WhatId, new Set<Date>());
        taskIdToCreatedDates.get(task.WhatId).add(task.CreatedDate.date());
    }
    return taskIdToCreatedDates;
}