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.

Day 17: Advanced SOQL Test