Insights of SOQL and Relationship Queries on Force.com

Introduction

Salesforce Object Query Language commonly known as SOQL is the language to query data from the salesforce.com platform. SOQL is very similar to SQL, it is just that SOQL utilizes the joins differently. The data model of Force.com is based on relationships of each object and to traverse the relationship path which is a fundamental building block while constructing a query. With the help of these queries, we can fetch data from different objects in one single query thus utilizing the joins implicitly. We construct the relationship in SOQL by using the path traversal which helps in joining the data.

Relationships in Force.com

In order effectively make SOQL queries, we would first need to understand the type of relationships available on the force.com platform. There are two types of relationships:-

1. Lookup Relationship
2. Master-Detail Relationship

Both of the relationships are 1-to-many but force.com platform treats them differently, both of these relationships are defined from child to parent.

Lookup Relationship

They are a flexible relationship between two objects and are used to create complex relationships. One of the main features about this relationship is that it is not enforced, i.e. an Orphan record is allowed in this relationship. We can create a record without entering a value in the relationship field. This data model allows a relationship to exist between a child and parent but it is not mandatory.

Permissions to usage and visibility are not governed by the permissions and visibility of the parent object, therefore it provides the flexibility to children to dictate their own sharing rules.

The lifecycle of the child record is not dependent on the lifecycle of the parent record, i.e. if parent record gets deleted then the child record is not deleted.

Roll up functionality are provided to implement declaratively and if required then it has to be implemented explicitly.

Master-Detail Relationship

This relationship is a special case of lookup Relationship, this relationship comes with some prebuilt functionality provided by force.com platform. Firstly Orphan record is not allowed in a Master-detail relationship, i.e. we cannot insert a child record without specifying a parent record. The relationship is enforced and no child record can exist without a parent record.

Permission to usage and visibility of child record is not flexible and is governed by the permission and visibility of the parent record. Sharing rules of parent records are imposed over the child record.

The master-detail relationship is provided with built-in cascade delete functionality, i.e. if a parent record is deleted then the child records also get deleted, this functionality is useful in many scenarios.

In Master-Detail Relationship there are many roll-up features are available to declare using point and click functionality and reduces the implementation time.

SOQL results and Data Binding

The result set of SOQL can be accessed programmatically and it utilizes the navigation with relationship underlying the data model of force.com. The result set of SOQL is anchored to a base object i.e. the object written after FROM clause. The result returned by the SOQL is a list of the object, this set also contains the list of fields which were included in the select clause of the SOQL statement. This set of field can have another nested select statement, therefore, there is a possibility that the result may have a nested object in the result set.

Consider the below schema, in this, we have established an m-n relationship between Position and Candidate with the help of another object i.e. Job Application. Following is more elaborated relationship these objects have.

1. Position can have n number of Job Application from various Candidates
2. A Candidate can apply for more than one Position
3. Also, there is a possibility that a Candidate applies to a company and not to a specific Position.

Kindly refer the Schema as shown in the picture below.

Now we are going to answer questions related to reporting and to answer them we will write SOQL and explain the type of joins and relationship queries.

Right Outer Join

To begin with, the question no 1 is: Find all the job applications related to Departments.

Now the object Job Application does not have the field Department and therefore we would have to reach out to the object which has this value for each job application i.e. Position__c. The resulting query would something like below.

SELECT Name, Position__r.Department__c FROM Job_Application__c

The result set it returned is below:

In this query, you would have observed that we have utilized a path expression Position__r.Department__c which reaches out to the related object and fetches out the value of the desired field, this type of join is known as Right Outer Join between the two objects. It is an implicit join statement where the path expression results in the desired value from the related object.

Left Outer Join

Next question is Find all the position and their related Job Application records.

In this scenario, we want to fetch all the related Job applications and there is a possibility that one Position may have more than one Job Application from different Candidates. To achieve this w would have to create a nest SOQL query, where the parent object is Position and the related child records are Job Applications. Note that here we are traversing from ‘m’ side of the m-1 relationship. The Query would look something like below

SELECT Name, (SELECT Name FROM Job_Applications__r) FROM Position__c

The result of this query would be something like below:

This query is very useful in fetching the related records and now we are traversing from the ‘1’ side of a 1-m relationship. The result set her contains the list of Position records and each of these position records is having a list of child records i.e. Job Applications. This type of query is known as Left Outer Join.

Left Inner Join

Next Question to answer is: find all those positions which have an associated Job Applications.

SELECT Name FROM Position_c WHERE Id IN (SELECT Position__c FROM Job_Application__c)

It will fetch result something like below:

In the earlier versions of Force.com’s SOQL this type of query was not possible but after Spring 10 this feature was added. Earlier we would have to fetch the Id’s of all the Positions associated with  Job Applications in a separate query and then use them in the above-shown example. Now, force.com allows select statement in conjunction with the select clause and the Ids of the position can be fetched directly in the IN Clause of the select statement. This type of join represents the Left Inner Join.

Right Inner Join

Next Question is: Find all the Job Applications for Positions that are in the Sales Department?

Here we are going to utilize path traversal to fetch the value of the position name in the select clause and here we need to filter the result set of Job application based on the Department and this field is again not on the Job Application object. Therefore we would use the path traversal method and use this field in the Where Clause of the SOQL statement and the query would look something like below.

SELECT Name,Position__r.Name, FROM Job_Application__c WHERE Position__r.Department__c = ‘Sales’

The result set of this query would look something like below:

Left Anti-Join

Now we have a scenario where we have to fetch all the Positions which do not have a Job Application associated with it.

In this scenario, we have to check whether there is any child record is associated or not with the Position object. Here the Select clause would be similar to the Left Inner Join but instead of the In Clause we would use the NOT In Clause and it will bring the result set opposite of the Left Inner Join.

The query would look something like below:

SELECT Name FROM Position_c WHERE Id IN (SELECT Position__c FROM Job_Application__c)

It will fetch result something like below:

Right Anti-Join

In this scenario, we are going to find all the Job Applications which are not associated with a Position.

To achieve this although we do not implement any join the resulting set is Right Anti-Join. The Query would look something like below:

SELECT Name FROM Job_Application__c WHERE Position__c = null

The result set would be something like below:

The only field we use here to filter the result is the relationship field therefore thus simulating as a join but without traversing any field on the related object.

Cheat Sheet

Force.com has provided a cheat sheet to easily utilize the Join Patterns and this sheet is made keeping in mind the 1-m relationship pattern. The parent object is on left and child is on right for the below cheat sheet.

To summarize all the joins we can say that to use Outer joins to display the records where there is no restriction of the relationship, i.e. to fetch all the records with or without a value in the related field. When you want to find the records which do not have a value in the related fields i.e. childless parent or orphaned Child, then use the Anti-Join patterns.

Date Functions

Force.com’s SOQL has provided with various functions which are exclusive to only date fields only and we can fetch data filtered appropriately with the help of these functions.

Functions are broadly divided into 5 categories:

1. Calendar functions: the syntax of the functions is as follows:

CALENDAR_MONTH, CALENDAR_QUARTER, CALENDAR_YEAR

2. DAY functions: the syntax of the functions is as following:

DAY_IN_MONTH, DAY_IN_WEEK, DAY_IN_YEAR, DAY_ONLY

3. FISCAL functions: the syntax of the functions is as follows:

FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR

4. HOUR function: the syntax of the functions is as following:

HOUR_IN_DAY

5. WEEK function: the syntax of the functions is as following:

WEEK_IN_MONTH, WEEK_IN_YEAR

Example of the Date Function in the query is as below:

SELECT Title, Name FROM Position__c WHERE CALENDAR_ YEAR (Date_Closed__c) = 2015

Aggregate Result with GROUP BY

Force.com platform in the Spring 10 release has introduced more new features in SOQL and these were the inclusion of Group By and Having Clause. These clauses help in generating reports and analysis on the go from the data in hand. These queries are useful while making Roll-up Summaries on the objects with the lookup relationship.

Let’s work on a scenario where we can implement the roll up like feature, suppose we want to find the total number Job Application which has been applied in various departments. To achieve this we would write a SOQL similar to the one given below:

SELECT Position__r.Department__c deptname, COUNT(id) total FROM Job_Application__c GROUP BY Position__r.Department__c

This query will have a result set similar to the one below:

In the query above we see a new of dealing with the grouped fields, here we are first of all referring the aggregated field with the alias name i.e. deptName. It can also be seen that the field which is aggregated is from the related object and job is obtained using the path traversal expression. In the second field, we are fetching the total numbers of Job Applications which are applied in a given department as the result set is grouped according to the department, for this field we used the alias name as the total. The aliasing the aggregated fields provides us with the means to refer them in the aggregated results.

Now the main difference between a normal SOQL and a SOQL with the aggregated field is that the result of the query is not a list of sObject but it is a list of AggregateResult. Since the new result set is containing the fields which were specified in the query and they are referred in the aggregate result using the alias name and are fetched using getter method as shown below.

List <AggregateResult> aggrs = [SELECT Position__r.Department__c deptname, COUNT(id) total FROM Job_Application__c GROUP BY Position__r.Department__c];

for (AggregateResult ja : aggrs)

{

System.debug(ja.get(‘deptname’) + ‘ | ‘ + ja.get(‘total’));

}

How to Use Aggregate result in Visualforce Page

In this section, we are going to see how we can use the Aggregate result in a Visualforce page. For this, we are going to create an extension and use Account standard controller. In this extension, I am going to fetch all the opportunities of an account and segregate them according to their present stage name and display the total amount of each stage.

To display the value of each stage I have created a Page Block Table, where I am passing the List of the Aggregate result as the value and created a variable to traverse the list. I am going to place this Visualforce page as a section on the account standard detail page and it will display the information related to the specific account.

Controller Extension:

VisualForce Page:

Output UI:

Summary

This includes the common patterns found in SQL and can now be implemented in SOQL as well like Joins and aggregate functions and other functions. These methods help improve the overall code efficiency and performance.

Scenario-based Question

1. Find the Maximum, minimum and Sum of all the opportunities of each account whose stagename is Closed Won?

ANS:

Select account.id, min(Amount), max(Amount), sum(Amount) TotalOppValue from Opportunity where stagename = ‘Closed Won’ group by account.id

ResultSet:

Also Have a look at the below resources:

1. Best Salesforce Interview Questions book with Apex and Visualforce concept explained

Also Have a look at the below learning resources:

1. SOQL (Salesforce Object Query Language)

2. Apex Trigger Best Practices and the Trigger Framework

3. Salesforce Interview Question and Answers Part 2

4. Salesforce Interview Questions on Test Class