Aggregate child records with multiple values

Question

I need to find the max date of 2 field on Object__c Structure Object_A is parent Object__B is junction object where Object__A and Object__C are lookup fields Object__c is object that has the date fields where I need to find the max value of 2 fields.
There may be multiple Object__C records.

When an Object__C is update I query for the Object__B record where the Object__C record is housed and then query all of the Object__C records. I think I have this part ok. My question is how do I get all of the Object__C records into an Aggregate query and find the max date of the 2 fields? I will be pushing the dates to Object__A. Or is using an Aggregate query the incorrect approach?

List<AggregateResult> permitAggrDateList = [
        SELECT Id, MAX(Rec_Date_c), MAX(Submit_Date__c)
        FROM Object__C
        WHERE Id IN : ?????
        GROUP BY Id
        ORDER BY MAX(Rec_Date__c), MAX(Submit_Date__c) DESC
];

Answer 1

Yes, using aggregate functions to do this is a valid approach (not the only approach). A few things to note though:

• GROUP BY Id (by the Id field of the SObject your query is based on) makes the query worthless
  • no records would be grouped together
  • so MAX() would just return the value of each individual record
  • all of the downsides of AggregateResult with none of the benefits
• Using aggregate functions is useful on child records (where you can group by a relationship field), and much less so on parent records

If you want to do this using SOQL, your query should be based on Object_B instead.

It is possible to use aggregate functions on related data, which is very helpful for trying to aggregate data using a junction object (i.e. for a many-to-many relationship).

So with that knowledge in hand, the requirements for your query look like:

• Based on Object_B
• Using MAX(Object_C__r.Rec_Date_c) and MAX(Object_C__r.Submit_Date_c)
• With a filter to grab all junction object records Object_C__c IN :triggeredObjectCIds
• Grouping by Object_A__c