SOQL to query parent to child

Question

I have been developing a scheduled class that changes the BMCServiceDesk__FKStatus__c to "Closed" on BMCServiceDesk__Incident__c records that have had a BMCServiceDesk__FKStatus__cof "Resolved" for 5 days or more.

A new requirement states that BMCServiceDesk__Incident__c records should only have the status changed in this way if the all associated child BMCServiceDesk__Task__c records have a BMCServiceDesk__FKStatus__c of "Resolved".

So I need to change my query to only pull through BMCServiceDesk__Incident__c records with a BMCServiceDesk__FKStatus__c of "Resolved" where all associated BMCServiceDesk__Task__c records have an BMCServiceDesk__FKStatus__c equal to "Resolved". I don't understand how I can do this? Below is my code showing how far I've got.

{

List<BMCServiceDesk__Status__c> ClosedStatus = [SELECT Id FROM BMCServiceDesk__Status__c WHERE Name = 'Closed'];


List<BMCServiceDesk__Incident__c> listIncident = [SELECT Id FROM BMCServiceDesk__Incident__c
                WHERE BMCServiceDesk__Status_ID__c = 'Resolved'
                AND (BMCServiceDesk__StatusChangeDate__c = N_DAYS_AGO:5
                     OR BMCServiceDesk__StatusChangeDate__c = N_DAYS_AGO:6
                     OR BMCServiceDesk__StatusChangeDate__c = N_DAYS_AGO:7) LIMIT 100];

   List<BMCServiceDesk__Task__c> listTask = [SELECT Id FROM BMCServiceDesk__Task__c
                                         WHERE BMCServiceDesk__FKIncident__c : listIncident]

for(BMCServiceDesk__Incident__c up: listIncident)
{

    up.BMCServiceDesk__FKStatus__c = ClosedStatus[0].ID;
}

update listIncident;
}
}

Answer 1

Adding this term to the end of your BMCServiceDesk__Incident__c query

AND Id not in (
    select Id
    from BMCServiceDesk__Task__c
    where BMCServiceDesk__FKStatus__c != 'Resolved'
)

would logically exclude the records. See e.g. A Deeper look at SOQL and Relationship Queries on Force.com for more explanation.

But I am not certain that query optimiser will do a good job on this (the concern being whether the BMCServiceDesk__Task__c is limited to a small set of records or not) so best to use the Query Plan Tool tool to check that.

If that was a problem, you could use this query pattern:

List<BMCServiceDesk__Incident__c> listIncident = [
        SELECT Id, (
                select Id
                from BMCServiceDesk__Tasks__r
                where BMCServiceDesk__FKStatus__c != 'Resolved' 
                )
        FROM BMCServiceDesk__Incident__c
        ...
        ];

and filter in the Apex code:

for(BMCServiceDesk__Incident__c up: listIncident) {
    // Only update of no none-resolved ones
    if (up.BMCServiceDesk__Tasks__r.size() == 0) {
    }
}

Answer 2

You will need to have a map of associated (child) records with the Parent being the map's key.

Map<id, List<BMCServiceDesk__Task__c > > mapTasks = new Map<id,List<BMCServiceDesk__Task__c > >();
for(BMCServiceDesk__Task__c childTask: [SELECT Id, BMCServiceDesk__FKIncident__c  
                                        FROM BMCServiceDesk__Task__c
                                        WHERE BMCServiceDesk__FKIncident__c : listIncident] ) 
{
    if (!mapTasks.containsKey(childTask.BMCServiceDesk__FKIncident__c) )
    {
        mapTasks.put(childTask.BMCServiceDesk__FKIncident__c, new List<BMCServiceDesk__Task__c> {childTask} );
    }
    else 
    {
        taskList = mapTasks.get(childTask.BMCServiceDesk__FKIncident__c).add(childTask);
    }
}

Now that you have the map ready, loop through parent records and check for each associated record id.

for(BMCServiceDesk__Incident__c up: listIncident)
{
    boolean allTasksResolved = true;
    if (mapTasks.containsKey(up.id))
    {
        for(BMCServiceDesk__Task__c childTask : mapTasks.get(up.id) )
        {
            if(childTask.BMCServiceDesk__FKStatus__c == false) allTasksResolved = false;
        }
    }
    if ( allTasksResolved ) up.BMCServiceDesk__FKStatus__c = 'Resolved';
}
update listIncident;