function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Chris Walters 9Chris Walters 9 

Downward SOQL with bulkification

v47

Running into a bulkification problem - given a number of Tasks from TaskHandler.afterBulk I'm trying to access the fields inside the subquery and use them in a WHERE clause. 
for( Account a : [SELECT Id, Name, (SELECT Id, Email_Direction__c  FROM Tasks ) FROM Account WHERE foo.id IN :Trigger.new] ) {
    doSomething( a.Id, foo.Email_Direction__c);
}
What would the right syntax be to achieve this? 

TIA,

Chris
Best Answer chosen by Chris Walters 9
Chris Walters 9Chris Walters 9
Hello Abhishek, Thank You So Much for your time and effort on my behalf. I'll give this a try shortly. Bahut shukriyaa, Chris

All Answers

Abhishek BansalAbhishek Bansal
Hi Chris,

In order to use the field of child records, you need another for loop as mentioned below: 
for( Account a : [SELECT Id, Name, (SELECT Id, Email_Direction__c  FROM Tasks ) FROM Account WHERE foo.id IN :Trigger.new] ) {
	for(Task tsk : a.tasks) {
		doSomething( a.Id, tsk.Email_Direction__c);
	}
}

Please let me know if you any other help on this.

Thanks,
Abhishek Bansal.
Chris Walters 9Chris Walters 9
Thank you for your prompt reply! This is very close. What would I replace foo.id with in the previous code? Or maybe move the WHERE inside the inner-query?

TIA,

Chris
 
Chris Walters 9Chris Walters 9
Though upon further research it seems there's a better way - invert the SOQL to be based on Task and make use of polymorphic fields as referenced elsewhere. Something like 
Set<Id>taskIds = new Set<Id>{'00T1b00000xxxxxxx'};
List<Task> list_t = new List<Task>([SELECT Id,
                                    TYPEOF Who
                                      WHEN Contact THEN Last_Contact_Attempt__c, Last_Contact_Method__c
                                    END,
                                    TYPEOF What WHEN Account
                                      THEN Name
                                    END,
                                    TYPEOF Owner WHEN User
                                      THEN UserRoleId
                                    END
                                    FROM Task WHERE Id IN :taskIds]);
for( Task t : list_t) {
    System.debug( t.Id);      
}
This executes and does show only one row. But t.Who.Last_Contact_Attempt__c  is invalid syntax.
Chris Walters 9Chris Walters 9
Greetings Abhishek,

Our org uses Slack instead of Skype. Can you Slack?
Abhishek BansalAbhishek Bansal
Hi Chris,

Please find the code below: 
List<Task> list_t = new List<Task>();
list_t = [Select WhoId, Who.Type, OwnerId, Owner.Type from Task where Who.Type = 'Contact' OR Owner.Type = 'User'];
Set<Id> relatedContactIds = new Set<Id>();
Set<Id> relatedOwnerIds = new Set<Id>();
for(Task tsk : list_t) {
	if(tsk.Who.Type == 'Contact') {
		relatedContactIds.add(tsk.WhoId);
	}
	if(tsk.Owner.Type == 'User') {
		relatedOwnerIds.add(tsk.OwnerId);
	}
}
if(relatedContactIds.size() > 0 || relatedOwnerIds.size() > 0) {
	Map<Id, Contact> mapOfRelatedContacts = new Map<Id, Contact>([Select Id, Last_Contact_Attempt__c, Last_Contact_Method__c from Contact where Id IN: relatedContactIds]);
	Map<Id, User> mapOfRelatedUsers = new Map<Id, User>([Select Id, UserRoleId, UserRole.Name from User where Id IN: relatedOwnerIds]);
	
	for(Task tsk : list_t) {
		if(tsk.Who.Type == 'Contact' && mapOfRelatedContacts.containsKey(tsk.WhoId)) {
			Contact relatedContact = mapOfRelatedContacts.get(tsk.WhoId);
			//here you can use related contcat fields like relatedContact.Last_Contact_Attempt__c
		}
		if(tsk.Owner.Type == 'User' && mapOfRelatedUsers.containsKey(tsk.OwnerId)) {
			User relatedUser = mapOfRelatedUsers.get(tsk.OwnerId);
			//here you can use related user fields like relatedUser.UserRoleId
		}
	}
}

Please take care of the syntax errors and let me know if you need any further help on this.

Thanks,
Abhishek Bansal.​​​​​​​
Chris Walters 9Chris Walters 9
Hello Abhishek, Thank You So Much for your time and effort on my behalf. I'll give this a try shortly. Bahut shukriyaa, Chris
This was selected as the best answer