SOQL for Approval History

Question

I'm trying to create this in an LWC and can't put together the right SOQL to grab all the data:

SELECT CreatedDate, StepStatus, OriginalActor.Name, Actor.Name, Comments from ProcessInstanceStep where ProcessInstance.TargetObjectId = :accountId

gets me just about everything except the first Step Name column.

I see there's a ProcessNodeName field in ProcessInstanceNode that seems to have what I'm after but I don't know how to join the two object together. I'm not sure if this is where I should be looking, however.

Answer 1

After some inspiration from this answer, I found that the ProcessInstanceHistory object can be accessed via the StepsAndWorkItems relationship on ProcessInstance.

I did need to assume the first step in the process is named "Approval Request Submitted" by default since that entry seems to have no ProcessNode.Name, however.

My Apex method ended up looking like this:

public List<ProcessInstanceHistory> getApprovalHistoryByAccountId(Id account) {
    ProcessInstance pi = [
        SELECT Id, (SELECT Id, ProcessNode.Name, CreatedDate,
                     StepStatus, OriginalActor.Name, Actor.Name, Comments
                     FROM StepsAndWorkitems
                     ORDER BY CreatedDate DESC
                    ) 
        FROM ProcessInstance
        WHERE TargetObjectId = :accountId
        ORDER BY CreatedDate DESC
        LIMIT 1
    ];
    return pi.StepsAndWorkitems;
}

Answer 2

Using a sub-query on ProcessInstance to find the related Steps should do the trick.

SELECT Status,
    (
    SELECT CreatedDate, StepStatus, OriginalActor.Name, Actor.Name, Comments
    FROM Steps
    ORDER BY CreatedDate DESC
    )
FROM ProcessInstance
WHERE TargetObjectId = :accountId

---

UPDATE

As per comment below a modification to the query in the original attempt to answer.

SELECT Id, LastActor.Name,
    (SELECT Id, Actor.Name, StepStatus, Comments FROM Steps),
    (SELECT Id, ProcessNodeName from Nodes)
FROM ProcessInstance
WHERE TargetObjectId = :accountId
ORDER BY CreatedDate DESC

• Step Name: Taken as ProcessNodeName from a child object of ProcessInstance called Nodes
• Date: Taken as CreatedDate of ProcessInstance
• Status: Taken as StepStatus from a child object of ProcessInstance called Steps
• Assigned To: Taken as Actor.Name from a child object of ProcessInstance called Steps
• Actual Approver: Taken as LastActor.Name of ProcessInstance
• Comments: Taken as Comments from a child object of ProcessInstance called Steps