Dynamic SOQL + Person Accounts = 😡
A story in overcoming challenges working with Person Accounts and dynamic SOQL
Update: A tweet from Charles T led me to update this article. See the end of this story for an explanation on how I now overcome the challenge of tackling Person Accounts for the timeline Lightning Web Component. Thanks Charles!
I‘ve been developing an open source lightning web component. If you’ve been on the journey with me you’ll know that performance and scale were key considerations. If you haven’t been following you can catch up here.
This blog tells the story of how Person Accounts forced a change in the design in how many database operations I had to do.
The Requirement and Original Solution
The original requirement seemed simple — show an interactive timeline for Accounts and Contacts and allow an administrator to decide which child records to plot by specifying their object name and relationship.
In order to reduce the number of database operations I thought it was good design to do one query vs many queries for each child record. Since I don’t know at runtime which relationships the Salesforce Administrator will choose to include I had to rely on a concept called Dynamic SOQL.
Dynamic SOQL refers to the creation of a SOQL string at run time with Apex code. It allows you to create more dynamic components that adapt at runtime and avoid hard-coding outcomes.
In the example below I can substitute fields and relationship names to dynamically build up a query including all child records related to the parent using the relationship name.
SELECT Id, (SELECT <FIELDS> FROM <RELATIONSHIP-1>),(SELECT <FIELDS> FROM <RELATIONSHIP-2>),(SELECT <FIELDS> FROM <RELATIONSHIP-3>) FROM <PARENT> WHERE Id :=recordIdSo for Contact where the Salesforce Administrator has asked to plot Tasks, Events, Cases and Work Orders the query would look like this:
SELECT Id, (SELECT Subject, ActivityDate FROM Tasks),(SELECT Subject, ActivityDate FROM Events),(SELECT Subject, CreatedDate FROM Cases),(SELECT Subject, CreatedDate FROM WorkOrders) FROM Contact WHERE Id :=recordIdThe Problem
I had successfully used the approach above to plot child records of Account, Contact, Lead, Opportunity and Case. The problem arose when I tried to plot child records to Person Accounts.
Note that Person Accounts are not active by default and require Salesforce Customer Support to help activate it in your Salesforce Org. See here.
The Digression
Before we talk about what caused the issue it’s important to get a baseline understanding of Person Accounts first.
Person accounts store information about individual people by combining certain account and contact fields into a single record.
It’s like a quantum problem. A Person Account is both an Account and a Contact at the same time. Much like Schrödinger’s cat it can take a while for this concept to sink in. A Salesforce Administrator can create create relationships to either the Contact or Account version of the Person Account. Yes, I know, it messes with my mind too 😅
In an attempt to make things easier for developers and administrators whenever a field is created on the Contact object it will become available directly on the Account object. This, in theory, makes it easier to access data without executing multiple expensive database operations.
The same applies to relationships between objects.
Standard Relationships
Tasks can be related to an Account — via WhatId and/or the Contact — via WhoId. Again, for Person Accounts, Salesforce tries to simplify things by creating a version of the Contact relationship on the Account object. In the example below the standard relationship between a Task and a Contact — called Tasks — becomes PersonTasks on the Account object.
This means I can query for all Tasks related to a Person Account from one object. If on the Account I can use the Tasks relationship to get records where the Person Account is in the WhatId field and PersonTasks to find records where the Person Account is in the WhoId field.
Custom Relationships
Custom object relationships are similar. The naming convention varies. Consider the example below for a custom object Contact_Addresses__c that has a master detail relationship to Contact. Just like standard objects a copy is available directly on the Account
By creating a mirror of fields and relationship names it simplifies SOQL queries where I need to return records for both Account and Contact versions of a Person Account.
As an example. The example below returns all Tasks, Cases and the custom object Contact_Address__c related to a Person Account in one query.
SELECT Id, (SELECT Subject, ActivityDate FROM Tasks), (SELECT Subject, ActivityDate FROM PersonTasks), (SELECT Subject, CreatedDate FROM Cases),(SELECT Subject, CreatedDate FROM PersonCases),(SELECT Id, Name FROM Contact_Addresses__pr) FROM Account WHERE Id :=accountIdThe Problem Restated
The above digression through Person Accounts seems to fully support one database query for related child records. I was excited until I tried it and failed.
The problem wasn’t the query but the need to use getSObjects() to return the individual records based on the child relationship name. I had been planning to use the getSObjects() method for this. Turns out there’s a long standing bug that causes an error.
I cannot strongly type the objects I need to retrieve as they will by nature be different depending on which metadata an Administrator has added or removed.
When trying to access SObjects from a standard relationship from the Account it errors.
//Execute a query on a Person Account using standard relationship from the Account object
String queryPersonAccount = SELECT Id, (SELECT Subject, ActivityDate FROM PersonTasks) FROM Account WHERE Id =:accountIdList<SObject> listOfRecords = Database.query( queryPersonAccount );
//This works. The Contact task related records are returnedfor (Sobject each : listOfRecords) {
for (SObject eachCh : (List<SObject>)each.getSObjects('PersonTasks')) { //This errors
System.Debug('Woo hoo it worked!');
}
}System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Account.Tasks
The same applies to custom relationships.
//Execute a query on a Person Account using custom relationship from the Account object
String queryPersonAccount = SELECT Id, (SELECT Id, Name FROM Contact_Addresses__pr) FROM Account WHERE Id =:accountIdList<SObject> listOfRecords = Database.query( queryPersonAccount );
//This works. The Contact Address relationship records are returnedfor (Sobject each : listOfRecords) {
for (SObject eachCh : (List<SObject>)each.getSObjects('Contact_Addresses__pr')) { //This errors
System.Debug('Woo hoo it worked!');
}
}System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Account.Contact_Addresses__r
The New Solution
Largely thanks to this tweet from Charles T I have updated my solution.
Originally my idea was to branch the logic for Person Accounts with a big, if/then/else statement. I estimated this would lead to 100’s of lines of extra, hard to maintain code.
As the tweet suggested getPopulatedFieldsAsMap() seems to be the key. By experimenting a little I was able to use this method to retrieve Person Account relationships successfully. I have included a sample code snippet below. See the summary for a link to the working GitHib repository that uses this technique.
//Execute a query on a Person Account using standard relationship from the Account object
String queryPersonAccount = SELECT Id, (SELECT Id, Name FROM Contact_Addresses__pr) FROM Account WHERE Id =:accountIdList<SObject> listOfRecords = Database.query( queryPersonAccount );
//This works. The Contact Address relationship records are returnedfor (Sobject each : listOfRecords) { //This is key. I no longer rely on getting each SObject
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(<field>); }
}
}
}
By using this technique I was able to reduce the lines of code. So a big thanks to Charles for sharing his knowledge and experience here to save me a lot of headaches.
Summary
Designing for Person Account usage can throw you a few curve balls. I’ve learnt to test more thoroughly.
If you want to help then feel free to raise an issue or send a pull request. I’ll be trying to add Person Account support to the timeline component soon.
