Left outer join safety in SOQL

iloveseven 07/11/2018. 2 answers, 180 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)

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.

HighResolutionMusic.com - Download Hi-Res Songs

1 The Chainsmokers

Beach House flac

The Chainsmokers. 2018. Writer: Andrew Taggart.
2 (G)I-DLE


(G)I-DLE. 2018. Writer: Riot Music Team;Harloe.
3 Anne-Marie

Rewrite The Stars flac

Anne-Marie. 2018. Writer: Benj Pasek;Justin Paul.
4 Ariana Grande

​Thank U, Next flac

Ariana Grande. 2018. Writer: Crazy Mike;Scootie;Victoria Monét;Tayla Parx;TBHits;Ariana Grande.
5 Clean Bandit

Baby flac

Clean Bandit. 2018. Writer: Jack Patterson;Kamille;Jason Evigan;Matthew Knott;Marina;Luis Fonsi.

Waste It On Me flac

BTS. 2018. Writer: Steve Aoki;Jeff Halavacs;Ryan Ogren;Michael Gazzo;Nate Cyphert;Sean Foreman;RM.
7 Imagine Dragons

Bad Liar flac

Imagine Dragons. 2018. Writer: Jorgen Odegard;Daniel Platzman;Ben McKee;Wayne Sermon;Aja Volkman;Dan Reynolds.
8 Nicki Minaj

No Candle No Light flac

Nicki Minaj. 2018. Writer: Denisia “Blu June” Andrews;Kathryn Ostenberg;Brittany "Chi" Coney;Brian Lee;TJ Routon;Tushar Apte;ZAYN;Nicki Minaj.
9 Brooks

Limbo flac

Brooks. 2018.
10 BlackPink

Kiss And Make Up flac

BlackPink. 2018. Writer: Soke;Kny Factory;Billboard;Chelcee Grimes;Teddy Park;Marc Vincent;Dua Lipa.
11 Diplo

Close To Me flac

Diplo. 2018. Writer: Ellie Goulding;Savan Kotecha;Peter Svensson;Ilya;Swae Lee;Diplo.
12 Rita Ora

Velvet Rope flac

Rita Ora. 2018.
13 Fitz And The Tantrums

HandClap flac

Fitz And The Tantrums. 2017. Writer: Fitz And The Tantrums;Eric Frederic;Sam Hollander.
14 Little Mix

Woman Like Me flac

Little Mix. 2018. Writer: Nicki Minaj;Steve Mac;Ed Sheeran;Jess Glynne.
15 Billie Eilish

When The Party's Over flac

Billie Eilish. 2018. Writer: Billie Eilish;FINNEAS.
16 K-391

Mystery flac

K-391. 2018.
17 Cher Lloyd

None Of My Business flac

Cher Lloyd. 2018. Writer: ​iamBADDLUCK;Alexsej Vlasenko;Kate Morgan;Henrik Meinke;Jonas Kalisch;Jeremy Chacon.
18 Backstreet Boys

Chances flac

Backstreet Boys. 2018.
19 Lil Pump

Arms Around You flac

Lil Pump. 2018. Writer: Rio Santana;Lil Pump;Edgar Barrera;Mally Mall;Jon Fx;Skrillex;Maluma;Swae Lee;XXXTENTACION.
20 Kelly Clarkson

Never Enough flac

Kelly Clarkson. 2018. Writer: Benj Pasek;Justin Paul.

Related questions

Hot questions


Popular Tags