Salesforce Relationship Queries
The Good, The Bad And The Ugly
Client applications need to be able to query for more than a single type of object at a time. SOQL provides syntax to support these types of queries, called relationship queries, against standard objects and custom objects.
Relationship queries traverse parent-to-child and child-to-parent relationships between objects to filter and return results.
The Good
Relationship queries are similar to SQL joins. You can use relationship queries to return objects of one type based on criteria that applies to objects of another type, for example, “return all accounts created by Dave Norris and also get the contacts associated with those accounts.”
SELECT Id, Name, (SELECT Id, FirstName, LastName FROM Contacts) FROM Account WHERE OwnerId = '<Insert Id>'Of course there must be a parent-child relationship connecting the objects. You can’t just write arbitrary queries such as “return all accounts and users created by Dave Norris” — since there is no relationship between the Account and User table that you can traverse.
The best thing about relationship queries is that they are the most efficient way to query for multiple types of child objects based on a parent, for example, “return all cases, tasks, events, opportunities and work orders for the Contact Dave Norris”.
Some boiler plate code to start this processing is included below — it creates separate queries for each child object type and contains some code to view some of the limits used.
Integer startCPUTime = Limits.getCpuTime();
Integer startHeapSize = Limits.getHeapSize();
Integer startQueries= Limits.getQueries();
Integer startLimitQueries = Limits.getLimitQueries();
Integer startQueryRows = Limits.getQueryRows();
Integer startLimitQueryRows = Limits.getLimitQueryRows();
Integer startAggregateQueries = Limits.getAggregateQueries();
Integer startLimitAggregateQueries = Limits.getLimitAggregateQueries();
String ContactId = '0035f00000OEgVdAAL';
String queryContact = 'SELECT Id FROM Contact WHERE Id =:ContactId';
List<SObject> listOfRecords;
listOfRecords = Database.query( queryContact );for (SObject each : listOfRecords) {
String Id = each.Id;
}for (SObject each : [SELECT Id FROM Case WHERE ContactId =:ContactId]) {
String Id = each.Id;
}for (SObject each : [SELECT Id FROM Task WHERE WhoId =:ContactId]) {
String Id = each.Id;
}for (SObject each : [SELECT Id FROM Event WHERE WhoId =:ContactId]) {
String Id = each.Id;
}for (SObject each : [SELECT Id FROM Opportunity WHERE ContactId =:ContactId]) {
String Id = each.Id;
}for (SObject each : [SELECT Id FROM WorkOrder WHERE ContactId =:ContactId]) {
String Id = each.Id;
}System.Debug('@@ElapsedCPUTime:: ' + String.valueOf(Limits.getCpuTime() - startCPUTime) + ' of ' + String.valueOf(Limits.getLimitCpuTime()));
System.Debug('@@UsedHeap:: ' + String.valueOf(Limits.getHeapSize() - startHeapSize) + ' of ' + String.valueOf(Limits.getLimitHeapSize()));
System.Debug('@@UsedQueries:: ' + String.valueOf(Limits.getQueries() - startQueries) + ' of ' + String.valueOf(Limits.getLimitQueries()));
System.Debug('@@UsedQueryRows:: ' + String.valueOf(Limits.getQueryRows() - startQueryRows) + ' of ' + String.valueOf(Limits.getLimitQueryRows()));
System.Debug('@@UsedAggregateQueries:: ' + String.valueOf(Limits.getAggregateQueries() - startAggregateQueries) + ' of ' + String.valueOf(Limits.getLimitAggregateQueries()));
This code used 0.087 of a second of processing time and uses 6 SOQL queries out of the limit of 100.
Using relationship queries
Integer startCPUTime = Limits.getCpuTime();
Integer startHeapSize = Limits.getHeapSize();
Integer startQueries= Limits.getQueries();
Integer startLimitQueries = Limits.getLimitQueries();
Integer startQueryRows = Limits.getQueryRows();
Integer startLimitQueryRows = Limits.getLimitQueryRows();
Integer startAggregateQueries = Limits.getAggregateQueries();
Integer startLimitAggregateQueries = Limits.getLimitAggregateQueries();
String ContactId = '0035f00000OEgVdAAL';
String queryContact = 'SELECT Id, (SELECT Id FROM Cases), (SELECT Id FROM Tasks), (SELECT Id FROM Events), (SELECT Id FROM Opportunities), (SELECT Id FROM WorkOrders) FROM Contact WHERE Id =:ContactId';
List<SObject> listOfRecords = Database.query( queryContact );for (SObject each : listOfRecords) {
Map<String, Object> fieldValues = each.getPopulatedFieldsAsMap();
for (String fieldName : fieldValues.keySet()) {
if (fieldValues.get(fieldName) instanceof List<sObject>) {
List<sObject> myCollection = (List<sObject>) fieldValues.get(fieldName);
for (SObject childObject : myCollection) {
//Now simply get the field you need
childObject.get('Id');
}
}
}
}System.Debug('@@ElapsedCPUTime:: '+ String.valueOf(Limits.getCpuTime() — startCPUTime) + 'of ' + String.valueOf(Limits.getLimitCpuTime()));
System.Debug('@@UsedHeap:: ‘ + String.valueOf(Limits.getHeapSize() — startHeapSize) + 'of '+ String.valueOf(Limits.getLimitHeapSize()));
System.Debug('@@UsedQueries:: '+ String.valueOf(Limits.getQueries() — startQueries) + 'of '+ String.valueOf(Limits.getLimitQueries()));
System.Debug(‘@@UsedQueryRows:: ‘ + String.valueOf(Limits.getQueryRows() — startQueryRows) + 'of '+ String.valueOf(Limits.getLimitQueryRows()));
System.Debug('@@UsedAggregateQueries:: '+ String.valueOf(Limits.getAggregateQueries() — startAggregateQueries) + 'of '+ String.valueOf(Limits.getLimitAggregateQueries()));
Across multiple tests we saw a 50% reduction in CPU time and the added bonus that the number of SOQL queries and number of Query Rows returned were always 1 regardless of the number of sub queries I added. The only trade-off is the extra memory required to process the lists of records returned. This led to an average x10 increase in heap size.
Relationship queries use fewer governor limits across the board with the exception of heap size and use approximately 50% less CPU time.
The Bad
Getting hold of the relationship name to use for you SOQL queries can be difficult. Especially for custom objects and those relationships on standard objects that you have configured.
Here are my tips for making this easier.
Use Setup > Object Manager
Find the field that defines the relationship to the parent object. In the screen below we are on a custom Memo__c custom object and have a relationship back to a parent Contact via the Contact__c custom field
Drill into the field to view the details.
Here you will see the Child Relationship Name field. In the above screenshot you can see the value is set to Memos <- this is the value we can substitute into our SOQL query. We just need to remember that for custom objects the relationship name is suffixed with __r. So the relationship name in full is Memos__r.
So to query the related Memo__c custom records related to a parent Contact the SOQL relationship would look like this:
SELECT Id, (SELECT Id, <Field> FROM Memos__r) FROM Contact WHERE Id = ‘<Insert Id>’Use the Enterprise WSDL
Generating and strongly-typed Enterprise WSDL is also a great way to get a snapshot of all current fields AND relationships across all standard and custom objects.
The generated xml file allows you to scroll to any object and view the relationships. Any xml element of type tns:QueryResult is a candidate for you to query.
Below you can see that the Memo__c custom object relationship is present under the Contact objects xml element.
Use Describe Calls
The last method is to use a describe call on the SObject type to get the list of child relationships. This is like looking at the Enterprise WSDL without having to scroll past fields.
List<Schema.ChildRelationship> objectRelationships = Contact.SObjectType.getDescribe().getChildRelationships();The Ugly
There are 2 Salesforce Objects that necessitate the use of a relationship query; OpenActivities and ActivityHistory. You cannot directly use SOQL to query them directly.
SELECT Id, (SELECT Id, Subject FROM OpenActivities), (SELECT Id, Subject FROM ActivityHistories) FROM Contact WHERE Id = ‘<ContactId>’These objects drive the associated related lists on each standard and custom objects they are placed on and have one quality hard to replicate querying Tasks and Events — they contain Tasks rolled up from child objects. For example, on an Account, they will show tasks indirectly related to the Contact records on the Account.
They do however come with some ugly caveats.
- You are limited to only referencing one record. So you have to say “get all activity history records for the Contact Dave Norris”. You can’t say “get all activity history records between date A and B”.
- You can’t use a WHERE clause. At all.
- You MUST specify a limit of 500 records.
- You have to ORDER BY a specific format.
The caveats only apply if you do not have View All Permissions. Which is hopefully most of your user base. So don’t get fooled by testing this logged in as an Administrator.
So, try and avoid these 2 objects if possible. If you must use them then this is the correct and supported best practice.
SELECT Id, (SELECT Id, Subject FROM OpenActivities ORDER BY ActivityDate ASC NULLS LAST, LastModifiedDate DESC LIMIT 500), (SELECT Id, Subject FROM ActivityHistories ORDER BY ActivityDate DESC NULLS LAST, LastModifiedDate DESC LIMIT 500) FROM Contact WHERE Id = ‘<ContactId>’
