«

»

Apr 07

Print this Post

Relationship Query in Apex – Salesforce

Relationship Query in Apex – Salesforce

Most of the times, we have relationships between the objects(master-detail OR lookup) and often we need to fetch the list of all child records related to parent record in SOQL. This can be achieved using relationship query.

We can also say it as a Inner Query (query inside query).

For using relationship query, most important is, to know the Name of relationship between the two objects. You can get this relationship name by going to the Look-up OR Master-detail field on child object. Here is the screen that you will see after clicking on the look-up/Master-detail field name that is present on child object.

Here are two examples.

1.First one is for Account(Parent) – Contact (Child) relationship. Below screen, we have opened the Lookup field present on contact(child object).

Child Relationship name2. The second one is for some custom object. When we create a custom relationship field on any object, we get an option to choose the name of relationship, while creating the look-up or Master-Detail relationship field. Below you can see the same, and then the same name is used in relationship queries.

How to create relationship name

Then this relationship name reflects on the custom field as shown below:

Child Relationship name - 2

 

So, in case of relationship between standard objects, relationship name is fixed already, and you can not change, but in case you are creating the relationship on your own, then you get the option of choosing the relationship name.

Now you have got the relationship name. We’ll see, how to construct the query required for our problem i.e. to get all the child records related to one parent record in a single query.

Here is a significant difference while making relationship queries for standard relationships(already defined by salesforce) and custom relationships(defined by user – b/w two custom objects OR b/w custom & standard object).

For example, for account and contacts relationship(already defined by salesforce), the query will be like:

List<Account> accList = [select id,name,(select name, id, email from contacts) from account];

Here you can see in the phrase (select name, id, email from contacts ), we have used the relationship name as it is mentioned on the field detail page as shown below:

Child Relationship name

But in case of custom relationship the query will be like:

List<Account> accList = [select id, (select id, name from tests__r) from account];

Here we have used __r with the relationship name. We have to use __r suffix after the relationship name shown on the field detail page as shown below:

Child Relationship name - 2

This relationship query returns all the accounts with all the child test records associated to each account record. You can use the query result as shown below:


//Perform the query on Account.

List<Account> accList = [select id, (select id, name from tests__r) from account];

//Iterate over the account with.
for(Account a : accList){

//For each account object, get the child records using tests__r and iterate over each child records.
for(test__c test : a.tests__r){

System.debug('The test name is:::::' + test.name);

}

}

Limitation of Relationship Query:

  • In each specified relationship, only one level of parent-to-child relationship can be specified in a query. For example, if the FROM clause specified Account, the SELECT clause could only specify the Contact or other objects at that level. It could not specify a child object of Contact.
  • No more than 20 relationships can be specified in a single query.
  • In each specified relationship, no more than five levels can be specified in a child-to-parent relationship. For example, Contact.Account.Owner.FirstName (three levels).

Good Luck !! :)



Permanent link to this article: http://www.sfdcpoint.com/salesforce/relationship-query-apex-salesforce/