[SalesForce] Ordering approval process history items by CreatedDate DESC doesn’t give the correct order of execution

It seems to be a quirk of ordering SOQL queries by CreatedDate that, if the records were created in the same second, the order returned by the SOQL query doesn't reflect the order of execution, as demonstrated here.

I'm running into this problem when trying to query the approval process history for a custom object, in that the submission step is appearing before the next pending steps.

List<ProcessInstance> allApprovalProcesses = new List<ProcessInstance>(
            [SELECT Id, TargetObjectId, Status, CompletedDate, CreatedDate, ProcessDefinitionId, 
                (SELECT Id, StepStatus, Originalactor.Name, Actor.Name, CreatedDate 
                    FROM StepsAndWorkitems 
                    ORDER BY Id, CreatedDate DESC), 
                (SELECT Id, Actor.Name 
                    FROM Workitems), 
                (SELECT Id, StepStatus, Actor.Name, Originalactor.Name, CreatedDate 
                    FROM Steps) 
                FROM ProcessInstance 
                WHERE TargetObjectId IN :proposalIDs 
                ORDER BY TargetObjectId, CreatedDate DESC]);

Even if I order by both CreatedDate and Id descending, as suggested in the above link, the 'Started' (i.e. 'Submitted') step appears before the 'Pending' steps.

 05:45:54.0 (38218906)|USER_DEBUG|[359]|DEBUG|*** 
(ProcessInstanceHistory:{ProcessInstanceId=04g11000001N0k9AAC, Id=04h11000001OSmAAAW, StepStatus=Started, OriginalActorId=00511000004iKuJAAU, ActorId=00511000004iKuJAAU, CreatedDate=2016-11-02 10:58:11},
ProcessInstanceHistory:{ProcessInstanceId=04g11000001N0k9AAC, Id=04i110000012Up4AAE, StepStatus=Pending, OriginalActorId=00GG0000005hz2FMAQ, ActorId=00GG0000005hz2FMAQ, CreatedDate=2016-11-02 10:58:11},
ProcessInstanceHistory:{ProcessInstanceId=04g11000001N0k9AAC, Id=04i110000012Up5AAE, StepStatus=Pending, OriginalActorId=00GG0000005hz2EMAQ, ActorId=00GG0000005hz2EMAQ, CreatedDate=2016-11-02 10:58:11},
ProcessInstanceHistory:{ProcessInstanceId=04g11000001N0k9AAC, Id=04i110000012Up6AAE, StepStatus=Pending, OriginalActorId=00511000004iKuJAAU, ActorId=00511000004iKuJAAU, CreatedDate=2016-11-02 10:58:11})

Anyone have any ideas how I can force the SOQL ordering as per the actual approval history below?

Example of approval process history

Thanks!

Best Answer

I've been able to make it work with the following query:

private Map<Id,ProcessInstance> getProcessHistory(Id objectId){
       return new Map<Id,ProcessInstance>([SELECT
                                                Id
                                                , (SELECT 
                                                        ID
                                                        , ActorId
                                                        , Actor.Name
                                                        , Comments
                                                        , CreatedById
                                                        , CreatedDate
                                                        , IsDeleted
                                                        , IsPending
                                                        , OriginalActorId
                                                        , OriginalActor.Name
                                                        , ProcessInstanceId
                                                        , ProcessNodeId
                                                        , ProcessNode.Name 
                                                        , RemindersSent
                                                        , StepStatus
                                                        , TargetObjectId
                                                    FROM StepsAndWorkitems
                                                    ORDER BY CreatedDate DESC, Id DESC)
                                            FROM ProcessInstance 
                                            WHERE TargetObjectId =:objectId
                                            ORDER BY CreatedDate DESC, Id DESC]);
    }

The catch is to order first by the Created Date and THEN by the Id.

Related Topic