2.2 Use relationship to retrieve data from related objects

Before we start

Besides our current merchandise object, we need to quickly create two custom objects – Customer and Transaction. Customer object has one custom field called Address which is a text field and Transaction object has two lookup relationship fields pointing to Customer and Transaction. Don’t forget to create tabs for both objects as well. Create a few records for both objects.

The problem and resolution

Suppose we want to find out all the customers who has bought a merchandise whose original price is no less than 100. We need to get the name and address of those customers so we can send mails to them to let them know they are valued customers. How can we achieve it?

The information we need to achieve lies in three different objects. Since Transaction is child object of both Customer and Merchandise, we want to query on transaction and retrieve the related information in its parent object using relationship fields. Below is the sample code:


List<Transaction__c> transactionList = [Select Id, Name, 
                                       		   Customer__r.Name,
                                       	       Customer__r.Address__c,
                                               Merchandise__r.Name,
                                               Merchandise__r.Original_Price__c
                                        From   Transaction__c
                                        Where  Merchandise__r.Original_Price__c >= 100];

for(Transaction__c trans: transactionList)
{
    System.debug('Customer ' + trans.Customer__r.Name + 
                 ' from Address ' + trans.Customer__r.Address__c + 
                 ' has bought ' + trans.Merchandise__r.Name + 
                 ' with the original price ' + trans.Merchandise__r.Original_Price__c);
}

A bit explanation

In SObject, if we need to reference a field from a parent object, the grammar is [RecordName].[RelationshipFieldName].[FieldName]. If the related object is a custom object, don’t forget to change __c into __r. It is the same way as we used in validation rules or workflow rules. Other than that, the usage is not too new.

Also, if you need to retrieve data from multiple objects, it is a good practice to try retrieving it from the child objects.

If you are coming from a SQL background, there isn’t such a thing called join and query from multiple objects in SOQL. There is sub query. But we don’t use it as much as we do in SQL. For most times, using relationship fields will be the best and most convenient way to retrieve data from multiple objects

Exercise

Write code to retrieve the list of Merchandise bought by Customer James. Find out the merchandise name and price list.

Next Post

2.3 Conditions inside where clause

Subscribe to Sfdcinpractice

Subscribe to get the latest blogs and tutorials of sfdcinpractice. No spam, no trash, only the awesome posts from sfdcinpractice. 

Leave a Reply

Your email address will not be published / Required fields are marked *