SOQL and Apex patterns for multiple related objects

Business logic for one object often requires fields in related objects to be accessed. Suppose you have a number of objects related like this:

(In reality each object would have a number of fields that need to be accessed; in the examples below only the “Name” field is being accessed. Also see the earlier posting A SOQL relationship query example for an introductory example and some important links.)

With a Java background, your first thought on how to write the data loading part of such business logic code might be something like this where each object is lazy loaded as required through a getter method:

public class ClaimLogic {
    private Id claimId;
    private Group__c group;
    private Policy__c policy;
    private Claim__c claim;
    private List<Benefit__c> benefits;
    private List<Relationship__c> relationships;
    private List<Contact> contacts;
    public ClaimLogic(Id claimId) {
        this.claimId = claimId;
    }
    private Claim__c getClaim() {
        if (claim == null) {
            claim = [select Name from Claim__c where Id = :claimId];
        }
        return claim;
    }
    private Policy__c getPolicy() {
    	if (policy == null) {
            policy = [select Name from Policy__c where Id = :getClaim().Policy__c];
    	}
    	return policy;
    }
    // 4 other lazy-loading methods
}

But the pattern I suggest considering instead loads all the data at once:

public class ClaimLogic {
    private Group__c group;
    private Policy__c policy;
    private List<Benefit__c> benefits;
    private List<Relationship__c> relationships;
    private List<Contact> contacts;
    public ClaimLogic(Id claimId) {
        claim = [
            select
                    Policy__r.Group__r.Name,                // Grand parent field
                    Policy__r.Name,                         // Parent field
                    Name,                                   // Field
                    (
                            select
                                    Name                    // Child field
                            from Benefits__r
                            where Name like 'A%'            // Child field filtering
                    ),
                    (
                            select
                                    Name,                   // Association object field
                                    Contact__r.Name         // Many to many associated object field
                            from Relationships__r
                            where Contact__r.Name like 'X%' // Many to many associated object field filtering
                    )
            from Claim__c
            where Id = :claimId
            ];
        group = claim.Policy__r.Group__r;
        policy = claim.Policy__r;
        benefits = claim.Benefits__r;
        relationships = claim.Relationships__r;
        contacts = new List<Relationship__c>();
        for (Relationship__c relationship : claim.Relationships__r) {
            contacts.add(relationship.Contact__r);
        }
    }
}

The advantages of this approach are:

  • Instead of six SOQL queries being executed only one SOQL query is executed. Each query takes a few milliseconds and more importantly there is a governor limit on how many in total can be executed per request so six times fewer is a good thing for both performance and avoiding hitting the brick-wall of the governor limit.
  • While not taken advantage of in the above code, by querying all the objects in one go, the relationship fields (the fields ending in “__r”) in both directions are populated. This allows the whole object graph to be passed around and the various parts of the graph accessed as needed in various methods.

It is when writing this kind of SOQL that clear naming of the relationships (done when the relationships are created) becomes important. The parent relationship name is always taken from the parent object name. The child relationship name can be edited when the relationship is created and it is worth taking care with this to make it clear (and plural). The diagram at the beginning of this post shows the relationship names used in the SOQL in bold.

A few more comments on the SOQL:

  • As well as parent objects, grand-parent objects (and presumably great grand-parent objects – not sure if there is a limit) can be accessed.
  • On child objects, only the immediate children of the object in the root query can be accessed i.e. the grand-children cannot be accessed. So it is important to think about which object to use in the root query; sometimes moving up or down a level results in a solution. The relationship between the root query object and the child object is implicit which takes a bit of getting used to if you have a SQL background. The brackets around the child query are required and it can sometimes take a few goes to get those and the relationship name right.
  • The ability to query objects that are in a many to many relationship with the root object via an association object is a nice surprise.
  • Fields referenced via parent relationships can be used in “where” and “order by” clauses too.
Advertisements

4 thoughts on “SOQL and Apex patterns for multiple related objects

  1. Excellent article and blog as always! This is my preferred way of coding in queries as well. One problem I’ve found though while working with the Chatter objects is that getting the SObjectType then calling getLabel from the describe details always seems to return “Name” for a parent object that was queried in (Might be the same for child, I didn’t test that). Other than that this is definitely the way to go.

    ps. The solution to the problem with grabbing the describe and label for me was grabbing describe info for all objects in a map with the sobject prefix as the key. It worked out for me since I had to check a lot of different sobjects for labels.

    -Richard (@_drako)

  2. Pingback: Tweets that mention SOQL and Apex patterns for multiple related objects « Force 201 – Force.com Development -- Topsy.com

  3. While it is possible to go up multiple levels from child to parent, to grand-parent etc I believe you can only go down one level. I’ve sometimes changed the object used in the outer “where” to work-around this.

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