Left outer join safety in SOQL

iloveseven 07/11/2018. 2 answers, 179 views
soql subquery

I covered this post. There I met the following query:

SELECT Name, (SELECT Name FROM Job_Applications__r) FROM Position__c

Here is the explanation provided in the post:

Note that we have utilized a NESTED select to obtain the records of related children from the parent. This kind of expression is very powerful in obtaining related records traversing from the 1 side in a 1-m relationship. In complex data modeling, especially with containment relationships, it is a useful pattern to obtain related information on a parent and all its children via traversing path expressions with relationship fields. In this example, we are utilizing a path expression from Position__c using the relationship, Job_Applications__r to obtain the list of applicants to create this join. This pattern will also be very useful in obtaining data from feeds in Chatter which will be discussed elsewhere. Please note that the result set is a list of positions where each position contains a list of application, reflecting the nature of the hierarchical representation of the data that is selected by this query.And is very useful Information for the beginners.

In order to make my question here I first explain my understanding of the query.

We take all records in Position__c object. For each present record we are retrieving the Name of the record at first. Then we are using the taken record and filter out all the records in Job_Applications__r so that we a left with only those records from Job_Applications__r which have their field (the API name of which is) Position__c being equal to the taken record from Position__c; for every record (with we are left with after screening) from Job_Applications__r we are retrieving the Name of the record and assemble a list of the names. As a result we are getting a name of a Position__c record and a list on names of Job_Applications__r records associated with it.

Here comes my question: How does SOQL guarantee that there will be a Position__c field in Job_Applications__r? Or what happens if there is no such field?

I may lack the attention and miss something here (I am sorry for that), but after googling for 20 minutes I was not able to find out the answer to my question.

2 Answers


gNerb 07/11/2018.

Your question has two interpretations:

Field exists

The compiler checks to verify that the field exists. If it doesn't the code wont compile.

Field has a value

If the field has a null value, the record will simply not be returned as it cannot associate the record with a position.

There are a few ways to control this behavior. If the Position__c field is a master detail it is required and so it will never be null. If the position__c field is a lookup, making it a required field achieves the same result.

If the field is not required, null values are allowed and the application will not be associated with a specific position.


Adrian Larson 07/11/2018.

The only object describe/schema you care about here is that of the parent object. What you care about is the Relationship Name of the Child Relationship. When using static SOQL, the query will not even compile if you use the wrong relationship name.

The API Name of the Lookup relationship from your child object to your parent object is part of the child schema, and it is completely irrelevant to this query syntax.

As an explanatory script, if your child object were named Job_Application__c, you could run the following:

DescribeSObjectResult parentDescribe = SObjectType.Position__c;
for (ChildRelation relation : parentDescribe.getChildRelationships())
{
    if (relation.getChildSObject() == Job_Application__c.sObjectType)
    {
        system.debug(relation.getRelationshipName());
    }
}

The above script will get you the exact relationship name you need to use. Note that there could be multiple lookup relationships from your child object to your parent object. In such case, you would want to also investigate relation.getField() and make sure you are looking at the right one.

In the above example, I assume your child object name and child relationhship name resemble each other closely. That resemblance is common. However, your child object could be named FooBar__c and still have a child relationship name of Job_Applications__r.

Related questions

Hot questions

Language

Popular Tags