You need to sign in to do that
Don't have an account?
ilewi121
SOQL reference parent object in subquery
Working on a trigger to update the lead object. The field displays a count of activities completed within 30 days from the Lead record's CreatedDate.
I can't find a clean way to limit my SOQL subquery based on the parent object's CreatedDate field.
Any ideas?
public static String ldPrefix = Lead.sObjectType.getDescribe().getKeyPrefix(); public static void updateLeadCounts30days(Set<ID> leadIds) { List<Lead> Leads = [SELECT ID, CreatedDate, TP_First_30_Days__c, (SELECT ID, ActivityDate FROM Tasks), (SELECT ID, ActivityDate FROM Events) FROM Lead WHERE ID IN :leadIds]; List<Lead> updateLeads = new List<Lead>(); for (Lead l : Leads) { Date InitDate = l.CreatedDate; Date InitDate30 = l.CreatedDate + 30; Integer ThirtyTaskCount = [SELECT COUNT() FROM Tasks WHERE ActivityDate <= :InitDate30]; Integer ThirtyEventCount = [SELECT COUNT() FROM Events WHERE ActivityDate <= :InitDate30]; Integer FirstThirtyDays = ThirtyTaskCount + ThirtyEventCount; if (l.TP_First_30_Days__c != FirstThirtyDays) { l.TP_First_30_Days__c = FirstThirtyDays; } updateLeads.add(l); }
I would still prefer an SOQL solution, but solved this with Apex.
All Answers
You need not query the Tasks and Events again as you have them within the Lead query. you can iterate over them only. In the given code you are querying the Event records again which will query all the Events in the system and not related to that Lead (if that is your requirement, which i doubt).
Thank you Gulshan, I realize it is doubled right now. My problem is I don't know how to run a second query to access the records I pull from the first query. I cannot reference fields from the parent SOQL query to isolate records for my child SOQL query in the same line.
How would I write these queries so I can compare the Lead Created Date with the Activity Date?
I would still prefer an SOQL solution, but solved this with Apex.