0

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
];
2
  • to be honest hard to understand the question. I would suggest use Parent__c, Child__c, Junction__c
    – ytiq
    Oct 6, 2022 at 14:20
  • another point if you have Junction then you can't have Parent and Child, because you have many-to-many)
    – ytiq
    Oct 6, 2022 at 14:22

1 Answer 1

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
3
  • Derek. Thank you for the thorough response. I added the 'Id' to group by because I was getting an error when trying to save the class. I need to group by a field if I am to use the 'order by'. What should I be 'grouping by'? Or do I not need the 'order by'?
    – Phuc
    Oct 6, 2022 at 15:06
  • @Phuc Like I said in my answer, you need to be querying on Object_B, and grouping by Object_A__c (or whatever the relationship field is).
    – Derek F
    Oct 6, 2022 at 15:11
  • I should have reread your comment. Thanks
    – Phuc
    Oct 6, 2022 at 15:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .