We believe that you are practicing more and advancing your skills in Salesforce SOQL and this is really meant. So far, you have only been writing your SOQL queries to fetch the data from one single object.
However SOQL can support multiple objects as well and these objects have some kind of relationship so that you can use them in one single query, and that’s why this is called Relationship Query.
You cannot pick any random objects and start querying records from those objects in one single query. These objects that you are picking must have a relationship either parent-to-child or child-to-parent.
For example, let’s say you have an object called Department and there’s another object called Employee, and the Department object is the parent object of the Employee object, which makes total sense because one single Department can have multiple Employees.
So, that’s why our Department object is a parent object, where the employee object is a child object., if you want to query the data from both of these objects in one single SOQL, then this is supported. Very similar to this, Let’s say you have an object called company or you can also call it account, and then there is another object called contact, and from one company, you can have multiple contacts, maybe like you can have the contact of CEO, CFO or maybe a manager from that company. So, these two objects also share a relationship, where company is the parent object and contact is the child object. So, if you want to query the records from both of these objects in one single SOQL query, then this is also supported because they both share a relationship.
Now, let’s say you want to query the records from a Department and company object and these two objects are not related to each other anyhow, then you cannot do it.
Now, this relationship that we are talking about can be of two types from SOQL’s perspective.
Let’s take a look at our Department and Employee object. Here the Department is our parent object and Employee is the child object, and if you are writing a query on the parent object and reaching out to the child object, then this relationship is called a parent-to-child relationship. Another case can be when you have a query on the Employee object and you also want to fetch some details from the Department object.
In that case, you need to write your query on the Employee object and you’ll be reaching out to your parent object. So, this will be called a child to parent relationship.
So, this was just to give an overview of the relationship queries in SOQL. Now we are going to do a deep dive into both types of relationship queries. First, we are going to learn about parent-to-child relationship queries, and then we will learn about the child-to-parent relationship queries.
SOQL Parent to Child Relationship Queries
Let’s learn about parent-to-child relationship queries, and in these types of queries, your main object is the parent object. So in this example, the Department is our parent object.
So, our main query looks like this, SELECT, and then you will specify all the fields that you want to fetch from the Department object, then FROM Department, because Department is our parent object, which will act as the main object in this SOQL query.
Now let’s understand how we can reach out to the child records from a parent object.
So, here you have your department object and the Employee object, and we already know that Department is the parent object of the Employee child object. So, they both share a relationship and in this relationship, we are reaching out from Department to Employee. So, there must be a connection between Department and Employee object, and that’s what we call a relationship name.
For example, if you are reaching out from Department to the Employee object, then the relationship name can be Employee and we will use this relationship name in our query to fetch all the related Employee records. And keep in mind, this relationship name is only valid when you are reaching out to Employee object from your Department object. That means from parent to child object. If you do it vice versa, then this relationship name can be different.
So for now, let’s see how you can use this relationship name to fetch all the Employee records from a Department object record. So, here we have our SOQL query.
At the first glance, it may look scary, but we will break it down and you will understand how w have written this SOQL query.
The only difference in this SOQL query is that it has two select keywords and two from keywords.
So far we’ve only seen one single select keyword and from keyword in every SOQL query.
Now let’s understand this query. First, our main focus is our parent object. So, that’s why our first select statement is about the parent object. So, we are fetching department name and department numbers from the school object. So, that’s why if you focus on these white words, it simply translates to select deptname and deptnum from the department. But we also want to get all the related child records as well, or all the related Employee records as well.
So in that case, alongside your field name in your parent query, you can also specify a child subquery and here, if you notice, we are using our relationship name, which is called Employee. So this time we are fetching this Employee Name, Employee Number, and Salary from the Employee object. So, this query is going to return us the department name and their multiple employee records as well.
So, let’s say you have 5 different department and each department has a hundred Employees in them.
Then you will get 5 records from the parent query and these 5 records are related to hundred records in each of those.
Now, let’s go back to our Salesforce Org, and first, we’ll see how we can get the relationship name from a parent-to-child relationship. So, here I am in my Salesforce Org and each and every Salesforce Org has these standard objects like account, contacts, opportunities, and leads. An account is a parent object of the contact object and the opportunity object as well.
So, we will write a SOQL query on our parent account object, and from there, we will fetch all the related contact records.
So, first thing first, how we can get the relationship name?
To get the relationship name, you always need to go to the child object, and here, the contact is our child object. So, let’s scroll down and find a contact from this list, click on this contact object and let’s go to fields and relationships. And here, we need to focus on the datatype, which can be Lookup or master-detail for the relationship fields. So, let’s sort it by the datatype field. And here, let’s scroll down and we see that this contact has a look-up to account object.
So, this must be a relationship field between the contact object and the account object.
Let’s click on this field, and here you will see some details about this particular field, but the information that we want from this page is this one, the child relationship name, which is contacts. So, whenever we want to fetch the contacts from the account object, then we need to use contacts in our SOQL query as the relationship name.
So, let’s go to the developer console and let’s write a simple query on the account object first.
// Get all related contacts of an account
SELECT Name, Phone, Website, (SELECT Name, Department, Email FROM Contacts) FROM Account
So, it’s a pretty basic query on account object where we are retrieving all the records and we are retrieving the name of the account. It has given us 13 records and we have got the names of each and every account as well.
If you want, you can fetch more fields like maybe, phone number and website as well. And we have got more data here in the results. Now from this account object, we want to reach the related contact records. So first, let’s use a comma after our field names and then we need to use parentheses, and within these parentheses, we are going to write our child subquery. So again, we will have our Select and From the statement, Select and From, and this time, after this From keyword, we need to specify the relationship name instead of the object name. So you cannot write the object name here.
For example, if we go to our object manager and find contacts here, the API name is contact, and if you will write this contact here, it’s not going to work because instead of this object name, what we want is the relationship name, which was contacts as we have just seen So, we need to copy this relationship name and paste it here. And let’s say we want to get the name from these contacts,
so let’s write the name. As I briefed, you can fetch more fields from your child’s object as well. But for now, let’s only select a name and let’s execute this query. So, we have got our contacts here.
For example, the third record has multiple contacts in it. It has two contacts.
The first one is Liz D’Cruz and the second one is Tom Ripley.
If you want to fetch more data from contact, then you can specify the fields here.
Let’s get some fields from the contact object, maybe like department or e-mail.
Let’s write them down here, Department and then e-mail, and click on execute and we will see the department and email as well in our search result. We can also use the Where condition in our child queries.
So, let’s say if we want to put some conditions on the department, we can totally do that like department should be equals to maybe like finance.
// Get all related contacts (with where clause) of an account (with where clause)
SELECT Name, Phone, Website, (SELECT Name, Department, Email FROM Contacts WHERE Department=’Finance’) FROM Account WHERE Name=’United Oil & Gas, UK’
Where condition on your subquery, you can also have Where condition on your parent query as well. So, maybe like, if you want to put a Where condition on the account object, then we can do that. Let’s say we only want to fetch the account United Oil and Gas UK, so we can do that. And this time we have only got one result and the related contacts. Now, if there are multiple child objects, then you can query all those child objects as well. For example, if you want to query the related opportunities as well, we can do that. Let’s add one more comma here and we can have one more subquery, and this time, we’ll be writing our query on the opportunity object, and let’s get some fields from the opportunity object.
First, we need to get the relationship name, so let’s go to fields and relationships, sort the fields by the datatype, we need to focus on a master-detail or lookup field with the contact and this is our field and the child relationship name is opportunities. So, we are going to write, instead of opportunity,
we need to write opportunities here and then let’s get some fields, maybe like, amount we can get and then the, maybe like the opportunity name.
// Get all related contacts (with where clause) and opportunities of an account
SELECT Name, Phone, Website, (SELECT Name, Department, Email FROM Contacts WHERE Department=’Finance’), (SELECT Name, Amount FROM Opportunities) FROM Account
Let’s get these two fields, name and amount. Let’s execute this query, and now we are getting the related opportunities as well. Let’s remove this Where condition actually, so that we don’t restrict our account results, and we are getting the related opportunity records as well, and this subquery can also accept the where clause, so if you want to filter out some of the opportunity records, you can do that. Now, what if there are child objects of these child objects as well? For maybe like, contact can be a parent object for another child object.
So, can we have one more query here?
No, we cannot have a subquery within a subquery.
This is only supported for one level deep and we are already one level deep from our parent account object. So, you can only get one level of deep child records from a parent object in a SOQL query.
Now let’s talk about the limitations of the parent-to-child subquery.
So, when you are writing parent-to-child relationship queries, then there are two limitations that you need to remember.
- You can only go one level deep from your parent object and we have already seen it. (From the account object, we cannot go to the child object of either contact or opportunity object because when we are fetching these contact and opportunity records, we are already one level deep.
- There are up to 20 related objects that you can use in a parent-to-child relationship query. As we have used contact and opportunity object in one single query, like that, you can have 18 more, but you cannot have more than 20 related objects in a parent-to-child relationship query.
Keep these limitations in mind when you are writing your SOQL queries.
Now Let’s move on to learning about child-to-parent relationship queries.
SOQL Child to Parent Relationship Queries
Now, let’s talk about the child-to-parent query, and in these queries, our main object is the child object.
So, if we talk about our same example where Department is our parent object and Employee is our child object, then our main query will focus on the Employee object and we’ll write it like SELECT fields FROM Employee. Now, very similar to the parent-to-child queries, in the child-to-parent queries as well, you need to find that connection between your child object and your parent object. And here you are reaching out from your child object to your parent object.
And generally, this connection name or the relationship name is the field name that you create on your child’s object.
You will see it when I’ll explain it in a Salesforce Org. The difference between a parent-to-child query and a child-to-parent query is, that when you write a parent-to-child query, then you get multiple child records.
But when you write child to parent query, then there can be one single parent for one single child record. So, you won’t get multiple parent records in this case.
Let’s see how we can get the Department fields values when you write a query on the Employee object. So, this looks a lot simpler. There is no subquery involved here, and the reason is, that there can be only one single parent for a child record. So, all the white that you see in this query is referring to the child object. So, you are fetching Employee Name, and Employee Number from the student object, and all the blue that you see in this query is referring to the parent object.
So, to reach the parent objects field, you need to get the relationship name, which is Department here, and after the relationship name, you need to use a dot operator and then the field API name.
So you are getting Department.deptname and Department.Deptnum from the parent school object record. So, this is much simpler when we compare it with the parent-to-child relationship query,
and let’s go back to our Salesforce Org, and first, let’s see how we can get this relationship name, and then we will see some example queries in our developer console.
So, let’s come to our object manager, and here let’s go to our contact object again, because this time, we will be retrieving the accounts from a contact object record. So, here let’s go to the fields and relationships, and again, we need to find that relationship with the account object. We need to focus on either the lookup fields or the master-detail fields. This is our field with the account lookup, and here previously we were focusing on the child relationship name, but this time we need to focus on the field name. So, if you want to refer to the account fields, then you simply need to use the account as the relationship name.
So, let’s go to our developer console, and let’s erase everything. So, first, we are going to write a simple query on the contact object. So, I’m going to start with my select statement and write the query on the contact object, let’s get the contact object API name, which is the contact itself and we can get the name and phone from the contact, and maybe the department as well. Let’s execute.
// Get account information from contact
SELECT Name, Phone, Department, Account.Name, Account.Website FROM Contact
So, we have got 20 contact records in our Salesforce Org. Now we want to fetch the parent account record details as well for each and every contact record. So for that, we need to add a comma and then the relationship name. So, if you remember our relationship name is account.
So, let’s copy it, account dot maybe like, let’s get the name from the account and then the account dot website.
// get grandparent (user) object details
SELECT Name, Phone, Department, Account.Name, Account.Website, Account.Owner.Name FROM Contact
And executing this query, we have got the account name and account Website corresponding to each and every child record and unlike parent to child relationship query, the child-to-parent relationship query supports up to five levels of parent records. So, if your account object also has a parent object, then you can reach out to that object as well.
And let’s try to see an example of this.
Let’s go to our account object.
We must be having some sort of relationship with the account object as well. Maybe let’s sort it by the datatype and we have a few lookup fields here. So, the Account has a parent object as a user, so we can use this object, click on the account owner here so that we can get the relationship name.
So, when you are trying to reach out to this parent user record, you need to use the owner as the relationship name. So, let’s go to our developer console and what we will do, we will reach out to our grandparent, which is a user object.
First, we will reach out to the account object. Then the parent of the account, which is owned, as we need to use this relationship name. And then from this owner record or this user record, we need to get the name. We can simply use our dot operator and the field name. Let’s execute it. So, this time we are even getting our grandparent object’s detail as well.
So, this is how you can reach out to your parent object from your child objects in a SOQL query.
Now let’s have a look at the limitations of child-to-parent relationship queries.
So, when you are writing parent-to-child relationship queries, then there are two limitations that you need to remember.
- Only Five level of child-to-Parent is supported, as you can get the information of your grand grand grand parent object in the same query.
- Child-to-parent queries support up to 55 related objects in one single query, So, if your child object has multiple types of parent objects, meaning you can have up to 55 different objects in one single query. And from my personal experience, you will never hit these limits. So, you will always have like five to six different related objects that you want to get in one single query.
I hope you are more comfortable working with SOQL SELECT Queries. If you have any query feel free to comment or contact us through email yousuf@infotechabout.com
