Relationship Query in Apex – Salesforce

Relationship Query in Apex – Salesforce

Relationship Query in Apex – Salesforce

soql relationship query

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 in Salesforce.

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.

Parent to child query in salesforce using inner query

For parent-to-child relationships, the parent object has a name for the child relationship that is unique to the parent, the plural of the child object name. For example, Account has child relationships to Assets, Cases, and Contacts among other objects, and has a relationshipName for each, Assets, Cases, and Contacts.These relationships can be traversed only in the SELECT clause, using a nested SOQL query.

Here are two examples of parent to child query in salesforce

For Standard relation

First one is for Account(Parent) – Contact (Child) relationship. Below screen, we have opened the Lookup field present on contact(child object). We also call it soql relationship query. Here is the screen that you will see after clicking on the look-up/Master-detail field name that is present on child object.

For custom relation using __r in salesforce

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.

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

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:

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:

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);
 
}
 
}

Child to parent query in salesforce

For child-to-parent relationships, the relationship name to the parent is the name of the foreign key, and there is a relationshipName property that holds the reference to the parent object. For example, the Contact child object has a child-to-parent relationship to the Account object, so the value of relationshipName in Contact is Account. These relationships are traversed by specifying the parent using dot notation in the query, for example:

Contact c = [Select FirstName, LastName, Account.Name, Account.Industry from contact where LastName = ‘sfdcpoint’];

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).

For more details please refer official link

Good Luck !! 🙂