A SOQL relationship query example

I have a few cases where a custom object can be related to either a Contact or an Account. My first thought of how to access the Contact or Account information in business logic Apex was to use two SOQL queries like this (where PolicyAssociation__c is just a junction table):

List<Contact> contacts = [
        select Name, MailingPostalCode
        from Contact
        where Id in (select Contact__c from PolicyAssociation__c where Policy__c = :policyId)
        ];
List<Account> accounts = [
        select Name, BillingPostalCode
        from Account
        where Id in (select Account__c from PolicyAssociation__c where Policy__c = :policyId)
        ];

But in the governor limited world of Force.com, using two queries everywhere to access one set of objects (either Contact or Account) seemed a little reckless.

Unlike SQL via JDBC, SOQL allows data from multiple objects to be conveniently obtained in one query via the relationship “fields”. It is well worth reading and re-reading A Deeper look at SOQL and Relationship Queries on Force.com to understand what is possible. When you first build a data model you will probably accept the default names for these relationships but it is worth reviewing the naming – particularly the master to detail name – to ensure that they are consistent and clear when used in SOQL.

So I am now using this pattern where a single query gets the Contact and Account objects albeit at the cost of a tedious to type loop over the PolicyAssociation__c objects:

List<PolicyAssociation__c> pas = [
        select Contact__r.Name, Contact__r.MailingPostalCode, Account__r.Name, Account__r.BillingPostalCode
        from PolicyAssociation__c
        where Policy__c = :policyId
        ];
List<Contact> contacts = new List<Contact>();
List<Account> accounts = new List<Account>();
for (PolicyAssociation__c pa : pas) {
    if (pa.Contact__r != null) {
        contacts.add(pr.Contact__r);
    }
    if (pa.Account__r != null) {
        accounts.add(pr.Account__r);
    }
}
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s