Advanced SOQL
Now that we know how to write basic SOQL queries, we need to understand how to navigate master-detail relationships using SOQL. This is particularly important because in real world scenarios when you're passing data, you may need information from multiple objects to make sense of data. For example, a random contact won't make much sense to the end user unless its related account is also displayed.
Standard Objects
Child to Parent:
[SELECT ID, LastName, Account.Name FROM Contact WHERE LastName = 'Test'];
This will return one account
Parent to Child:
[SELECT ID, Name, (SELECT ID, LastName FROM Contacts) FROM Account WHERE Name = 'GenePoint'];
This will return all contacts related to that account. Also notice how instead of Contact we used Contacts, this is because every subquery must be called by its plural name.
Relationship queries can get a little tricky to store and access. Let's take our parent to child example where we are returning all contacts related to the GenePoint account and store it in a List of Account and output it in logs.
List<Account> finalList = [SELECT ID, Name, (SELECT ID, LastName FROM Contacts) FROM Account WHERE Name = 'GenePoint'];
System.debug(finalList);
You will notice your log not returning the Contacts, but only information on the Account:
22:54:11:021 USER_DEBUG [5]|DEBUG|(Account:{Id=0016F00002x0YX7QAM, Name=GenePoint})
This is because we need to manually call the subquery results, the syntax for which is:
The syntax for calling a subquery is:
listName[0].subQueryObject;
listName
is the name of the list.[0]
denotes we are accessing the first object in the list.subQueryObject
is the plural name of the Object we used in the nested query.
Now let's add this to our code
List<Account> finalList = [SELECT ID, Name, (SELECT ID, LastName FROM Contacts) FROM Account WHERE Name = 'GenePoint'];
System.debug(finalList);
System.debug(finalList[0].Contacts);
Now our log will show the results (formatted for easier reading):
22:54:11:021 USER_DEBUG [5]|DEBUG|(Account:{Id=0016F00002x0YX7QAM, Name=GenePoint})
22:54:11:021 USER_DEBUG [6]|DEBUG|
(Contact:{AccountId=0016F00002x0YX7QAM, Id=0036F00002nVJwYQAW, LastName=Frank},
Contact:{AccountId=0016F00002x0YX7QAM, Id=0036F00003XE2FLQA1, LastName=final})
Custom Objects
Navigation through relationships in Custom objects gets a little different from standard objects. This is done so that in future if a new Object is released it doesn't break existing code. When navigation through custom objects, we
need to add __r
at the end of our object name instead of __c
.
For easier understanding, let's assume Account and Contact are custom objects.
Child to Parent:
[SELECT ID, LastName__c, Account__r.Name__c FROM Contact WHERE LastName = 'Test'];
We call LastName
with __c
because it's a custom field, Account
with __r
because it's a custom object and it's Name
field using __c
because it's a custom
field.
Parent to Child:
[SELECT Name__c, (SELECT ID, LastName FROM Contact__r) FROM Account__c WHERE Name__c = 'GenePoint'];
To output this data in log, we follow the same syntax as we did with sObjects.
List<Account__c> finalList = [SELECT Name__c, (SELECT ID, LastName FROM Contact__r) FROM Account__c WHERE Name__c = 'GenePoint'];
System.debug(finalList);
System.debug(finalList[0].Contact__r);
In a parent to child relationship, we call our root (or outer query) using the __c
as we would normally, but our subquery uses __r
because it's a custom object and we use __r
instead of its plural
name to future-proof our code. Note that you cannot call a custom object using it's plural name so there are no options here.
Also notice how in Line 3 we use Contact__r
instead of Contact__c
. For easier
understanding, this is because our data type is of Contact__r
and not Contact__c
.
Summary
- SOQL can be used to navigate through relationships.
- Custom objects use
__r
while standard objects use their plural name when navigating Parent to Child relationships.