Apex Trigger with SOQL Query and Lookup Relationship

Question

I have 2 objects called WorkRequest__c and WorkOrders__c. Each time our client receives a new request, a record is created on the WorkRequest__c object. Depending on information in each request, a certain number of "Workorder" records are created.

The WorkRequest__c and WorkOrder__c objects are related by a lookup field that I've added to the WorkOrder__c object (WorkOrder__c.WorkRequest__c). You can have multiple "Workorders" for a single "Workrequest", but only one "Workrequest" for a given "Workorder".

I'm writing a trigger that for each time a WorkOrder is created or updated, I want to:

1) Query all other WorkOrders with the same WorkRequest Id (ie the value in the WorkOrder__c.WorkRequest__c lookup field)

2) Check each WorkOrder record to see if the WorkOrder__c.Status__c = 'Complete'.

3) If WorkOrder__c.Status__c = 'Complete' for all WorkOrder records associated with a given WorkRequest Id, then update the WorkRequest__c.Stage__c = 'Finalized'

Below is the trigger I am trying to create. But I'm getting the following error in my nested SOQL query - "Compile Error: Didn't understand relationship 'WorkOrders__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name".

Can someone help me correct?

**trigger DDStepFields on WorkOrder__c(after insert,after update){
List<WorkRequest__c> wReqobj = new List<WorkRequest__c>();
List<Id> listIds = new List<Id>();

for(WorkOrder__c orderObj : Trigger.new){
    listIds.add(orderObj.WorkRequest__c);
}


wReqobj = [SELECT Id, Name, Stage__c (SELECT Id, Status__c FROM WorkOrder__r)
                    FROM WorkRequest__c
                    WHERE ID IN :listIds];

for (WorkRequest__c req : wReqobj){
    for(WorkOrder__c wo : req.WorkOrder__r) {
         if(wo.Status__c == 'Closed') {
            req.Stage__c == 'Finalized';  
            }
        }
    }       

update wReqobj }

**

Answer 1

Common mistake.

You are using the object API name WorkOrder__c, and replacing the __c with __r, right?

It's not completely intuitve, but what you are actually supposed to use there is the Child Relationship name. You can find that by clicking Create > Objects and finding the Work Order object, going to the Work Request Lookup field, and look for the "Child Relationship Name". Use that name with __r appended to the end in both the SOQL query and the for loop, or anywhere else you want to reference the Work Order list on your individual Work Request query results.

Answer 2

Use inner query as (SELECT Id, Status__c FROM Work_Orders__r) this will work.Replace "WorkOrder__r" with "Work_Orders__r"