Query user's Items to Approve?

Question

I need to create a new section on a custom landing page that gives the User a list of objects that they can approve, as they are a member of a queue being submitted to.

The closest I can get is this :

List<Id> objectsUserCanApprove{ 
  get{
    List<Id> ids = new List<Id> ();
    for (ProcessInstanceWorkItem item : [SELECT ProcessInstance.TargetObjectId 
                                    FROM ProcessInstanceWorkItem 
                                    WHERE ProcessInstance.Status = 'Pending'])
    {
        ids.add(item.ProcessInstance.TargetObjectId);
    }
    return ids;
  }
}

//Followed By :

[SELECT Id, Name FROM customObject__c Where Id IN :objectsUserCanApprove) ORDER BY Submitted_Date__c];

But the problem arises with multi-level approval queues, the query returns objects that have already been approved by the current queue but have not been approved by the second queue. Is there anyway query ProcessInstanceWorkItems belonging to a specific queue?

Answer 1

You need to filter on ActorId in addition to ProcessInstance.Status in your query on ProcessInstanceWorkItem.

SELECT ProcessInstance.TargetObjectId FROM ProcessInstanceWorkItem 
WHERE ProcessInstance.Status = 'Pending' AND ActorId = :queue2id

You can even do an inner-join sub-select and get it all in one query:
You cannot add parent relationship data in an inner-join sub-select query, or you could do:

List<CustomObject__c> myItems = [
    SELECT Name FROM CustomObject__c WHERE Id IN (
        SELECT ProcessInstance.TargetObjectId FROM ProcessInstanceWorkItem 
        WHERE ProcessInstance.Status = 'Pending' AND ActorId = :queue2id
    )
];

But since it's not allowed, your original approach is perfect:

Set<Id> targets = new Set<Id>();
for (ProcessInstanceWorkItem workItem : [
    SELECT ProcessInstance.TargetObjectId FROM ProcessInstanceWorkItem 
    WHERE ProcessInstance.Status = 'Pending' AND ActorId = :queue2id
) targets.add(workItem.ProcessInstance.TargetObjectId);
List<CustomObject__c> itemsToApprove = [
    SELECT Name FROM CustomObject__c WHERE Id IN :targets ORDER BY Submitted_Date__c
];