Query a Min Value of a Related field to a Child Object from Parent and Assign it to a new record

Question

I am trying to find a minimum value of the field Maintenance_Cycle__c (which is a field on the Product standard object which is looked up by the Equipment_Maintenance_Item__c custom object, which is in turn a child of the Case standard object through master-detail relationship) and then assign it to another field in a new Case record.

My code is as follows:

public with sharing class MaintenanceRequestHelper {
    
    public static void updateWorkOrders(List<Case> cases) {
        List<Case> updatedCases = [SELECT Id,ProductId, Vehicle__c, (SELECT MIN(Equipment__r.Maintenance_Cycle__c) FROM Equipment_Maintenance_Items__r) FROM Case WHERE Id 
                                  IN :cases AND (Type = 'Repair' OR Type = 'Routine Maintenance') AND 
                                  Status = 'Closed'];
        List<Case> newCases = new List<Case>();
        for(Case a:updatedCases){
            Case b = new Case();
            b.Type = 'Routine Maintenance';
            b.Vehicle__c = a.Vehicle__c;
            b.Subject = 'Routine Maintenance Request';
            b.Date_Reported__c = Date.today();
            //to do: Date_Due__c field
            newCases.add(b);
        }
        insert newCases;
        }

I already have an error with this code "Grouped query cannot use child relationships in the SELECT list", which is caused by the MIN function (there is no error without it). I am also doing a MIN function because there will be several values and I need to find the smallest one. I will then need to assign that value somehow to the Date_Due__c field, but I do not even know how to access that value from the query, since it is few relationships away.

Answer 1

Although not optimal, you could retrieve the minimum value separately via an additional SOQL query for you to use in your updates. The below query should work (this is assuming that your lookup field on Equipment_Maintenance_Items__c has an API Name of Case__c. You'll need to modify this if that isn't true):

Equipment_Maintenance_Items__c minCycleItem = [SELECT Equipment__r.Maintenance_Cycle__c FROM Equipment_Maintenance_Items__c WHERE Case__c IN cases ORDER BY Equipement__r.Maintenance_Cycle__c ASC NULLS LAST LIMIT 1];

The additional Order By and Limit clauses in this ensure that you'll only get the single record that you need. You'll then be able to use the value from this single record to update the cases later in your code.