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
Lam CorporationLam Corporation 

Are there any SOQL whizzes out there? Bulk Date Query

Hi all,

I am working on building a Trigger on a child object that has a lookup field on it to the parent Contact object. 
I have a date field on both the child object and on the Contact. 

I would like to design a bulk trigger that performs a query of all the children of the child object and returns the latest date from all the children that are related to the Contact parent. Then take the latest date and update the Contact parent with the latest date. Creating this for one record would be quite simple. 

Single Query
Child_Object__c a = [SELECT Id, End_Date__c FROM Child_Object__c WHERE Contact__c = contactParentId ORDER BY End_date__c ASC LIMIT 1];

I can't picture how to build this query for all records in Trigger.new. If I return a list of all records and then optimise is and LIMIT 1 I am only going to get one result. I would like to have a query that performs the single query but for all affected records in Trigger.new. 

Can anyone help please? Many thanks for your time.  
Best Answer chosen by Lam Corporation
BalajiRanganathanBalajiRanganathan
you have to use aggregate SOQL to achive this.  try below SOQL


SELECT Contact__c contactid, MAX(End_Date__c) maxdate FROM Child_Object__c WHERE Contact__c in (select Contact__c from Child_Object__c where id in : Trigger.new) group by Contact__c

or 


Set<Id> contactIds = new Set<Id>(); 
for(​Child_Object__c co : Trigger.new) 
contactIds .add(co.Contact__c);

SELECT Contact__c contactid, MAX(End_Date__c) maxdate FROM Child_Object__c WHERE Contact__c in:contactIds group by Contact__c

the return type of the above SOQL will be AggreateResult[]. please refere below.

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm

All Answers

ForceMantis (Amit Jain)ForceMantis (Amit Jain)
Try this code, you will need to replace some parts of it as I am not sure about exact api names of schema that you have. let me know if you still have query.
  
//Build a set of all parent contact ids
Set<Id> contactIds = new Set<Id>();
for(​Child_Object__c co : Trigger.new)
    contactIds .add(co.Contact__c);

//Query all parent contact along with their child record with latest date
Map<Id, Contact> contactMap = new Map<Id, Contact>([Select Id, (Select End_Date__c from ChildRelationShipName__r Order by End_Date__c Desc Limit 1) From Contact]);

//Now Process above map and update contact records

 
BalajiRanganathanBalajiRanganathan
you have to use aggregate SOQL to achive this.  try below SOQL


SELECT Contact__c contactid, MAX(End_Date__c) maxdate FROM Child_Object__c WHERE Contact__c in (select Contact__c from Child_Object__c where id in : Trigger.new) group by Contact__c

or 


Set<Id> contactIds = new Set<Id>(); 
for(​Child_Object__c co : Trigger.new) 
contactIds .add(co.Contact__c);

SELECT Contact__c contactid, MAX(End_Date__c) maxdate FROM Child_Object__c WHERE Contact__c in:contactIds group by Contact__c

the return type of the above SOQL will be AggreateResult[]. please refere below.

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm
This was selected as the best answer
Lam CorporationLam Corporation
Thanks guys for both suggestions. Sorry for the delay in answering.

@BalajiRanganathan yours worked for me.