Child Relationship Name not working in Query

Question

What I'm trying to do is in this query on the custom object Contract Documents (Contract_Document__c), only pulling in contract documents that are associated with their opp. A contract document is associated to an opp through a lookup field. This field is called Opportunity__c and it rests on the Contract_Document__c object. The child relationship name is Contract_Documents.

Here is my query.

    @remoteAction
public static void syncIt(String quoteId, String oppId){
    Try{
        Quote quoteObj = [SELECT OpportunityId, Service_Template_Codes__c, IsSyncing FROM Quote WHERE Id = :quoteId];

        Opportunity oppObj = [SELECT Id, SyncedQuoteId FROM Opportunity WHERE Id = :oppId];

        Contract_Document__c contractDocObj = [SELECT Implementation__c, Contract_Documents__r.Id FROM Contract_Document__c WHERE Contract_Documents__r.Id = :oppId];

        oppObj.SyncedQuoteId = quoteObj.Id;
        oppObj.Service_Edited__c = true;


        contractDocObj.Implementation__c = quoteObj.Service_Template_Codes__c;
        update oppObj;
        update contractDocObj;


    }
    Catch(exception e){
        system.debug(logginglevel.error, '*****'+e);
    }
}

The line in question is

Contract_Document__c contractDocObj = [SELECT Implementation__c, Contract_Documents__r.Id FROM Contract_Document__c WHERE Contract_Documents__r.Id = :oppId];

I've looked all over online and that's how I've seen how to reference cross objects using relationship fields but I keep getting this error in the developer console:

SELECT Implementation__c, Contract_Documents__r.Id FROM Contract_Document__c ^ ERROR at Row:1:Column:27 Didn't understand relationship 'Contract_Documents__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

But when I use Opportunity__c instead, it pulls a null value. Any suggestions on this?

Answer 1

The Child Relationship Name is the API name of a list of children records under the parent of the lookup.

Your query for the opportunity can include a subquery for these child records, using this API name and you can iterate these records in the list.

Opportunity oppObj = [SELECT Id
                            , SyncedQuoteId
                            , (SELECT Id
                                , Implementation__c
                                FROM Contract_Documents__r) 
                        FROM Opportunity WHERE Id = :oppId];

// output the opportunity information
system.debug('Opportunity: ' + oppObj);

// iterate over the list of child records in the Contract_Documents__r list
for (Contract_Document__c cDocument : oppObj.Contract_Documents__r) {
    system.debug('Contract Document: ' + cDocument);
}

// serialize as JSON
system.debug(JSON.serializePretty(oppObj));

Answer 2

First of all, verify the relationship name correctly. You can describe this object in the workbench to find correct relationship name.

Here, is an example

Secondly, instead of writing this:

Contract_Document__c contractDocObj = [SELECT Implementation__c, Contract_Documents__r.Id FROM Contract_Document__c WHERE Contract_Documents__r.Id = :oppId];

Use directly Lookup field if Contract_Document__c is lookup as follows :

Contract_Document__c contractDocObj = 
[SELECT Implementation__c, Contract_Document__c 
    FROM Contract_Document__c 
    WHERE Contract_Document__c = :oppId];

Answer 3

I voted up Mark's answer because that was able to pull in the query I needed but I ended up actually going with this code below for what I did.

    @remoteAction
public static void syncIt(String quoteId){
    Try{
        Quote quoteObj = [SELECT OpportunityId, IsSyncing, Service_Template_Codes__c FROM Quote WHERE Id = :quoteId];
        Opportunity oppObj = [SELECT Id, SyncedQuoteId FROM Opportunity WHERE Id = :quoteObj.OpportunityId];
        Contract_Document__c cd = [SELECT Implementation__c FROM Contract_Document__c WHERE Opportunity__c = :oppObj.Id];
        cd.Implementation__c = quoteObj.Service_Template_Codes__c;
        oppObj.SyncedQuoteId = quoteObj.Id;
        update cd;
        update oppObj;
    }
    Catch(exception e){
        system.debug(logginglevel.error, '*****'+e);
    }
}

Answer 4

Contract_Document__c contractDocObj = [SELECT Implementation__c, Contract_Documents__r.opportunityId FROM Contract_Document__c WHERE Contract_Documents__r.opportunityId = :oppId];

check for relationship name in field first open lookup field their it will be and change accordingly