SOQL to pull out Approval History records

Question

Can someone let me know of SOQL that can pull out all these fields from Approval History ?

Action, Date, Status, Assigned To, Actual Approver, Comments, Overall Status

So far I found out that we can get all the approvals (for one record) using the following query

SELECT Id FROM ProcessInstance WHERE TARGETOBJECTID = "record id here" ORDER BY CreatedDate DESC

This gives me all the processinstances as shown below.

Then I queried ProcessInstanceNode to get various information like

Action, Date, Status

But I am not sure how to get other information like "Assigned To, Actual Approver, Comments and Overall Status"

Can someone help ?

EDIT : I am getting an error while using @sfdcfox query. Please see the screenshot below.

Answer 1

You can Query ProcessInstance and get related details from child records. Here is sample code for you.

for (Account a : [SELECT Id,(SELECT ID FROM ProcessInstances  ORDER BY CreatedDate DESC) FROM Account WHERE ID  =:accId])
{
    for(ProcessInstance pi :a.ProcessInstances)
        processInstanceIds.add(pi.Id);
}

// Now that we have the most recent process instances, we can check
// the moRst recent process steps for comments.  
for (ProcessInstance pi : [SELECT TargetObjectId,LastActorId,LastActor.Name,(SELECT Id, ActorId,OriginalActor.Name,Comments, ProcessInstanceId FROM StepsAndWorkitems WHERE StepStatus != 'Started' AND StepStatus != 'Pending' Order BY ID DESC), (SELECT Id, StepStatus, Comments  FROM Steps ORDER BY CreatedDate DESC LIMIT 1 )
                       FROM ProcessInstance WHERE Id IN :processInstanceIds ORDER BY CreatedDate DESC])
{
    //approvalMap.put(pi.LastActor.Name,'');
    // If no comment exists, then prevent the object from saving.                 
    if (pi.StepsAndWorkitems.size() > 0)
        for(ProcessInstanceHistory pih :pi.StepsAndWorkitems)
            approvalList.add(new wrapperclass(pih.OriginalActor.Name,pih.Comments));
    else
        approvalList.add(new wrapperclass(pi.LastActor.Name,''));

}