How to use SOQL Query with Nested SOQL, Aggregate functions, ORDER BY, GROUP BY, HAVING, OFFSET and LIMIT?

We can use SOQL query in different ways. If you want to fetch any 10 Accounts, you can use the following SOQL query:

Select Id, Name From Account LIMIT 10

We can also use joins in SOQL to fetch data among multiple related objects.

For e.g. We want to get Contact Name from Account. we can use the following SOQL query:

Right Outer join ( Maximum 5 Levels up )
Select Contact.Account.Name From Contact (1 level up )

For e.g. We want to get all Accounts with related Contacts. we can use the following SOQL query:

Left Outer join ( Maximum, 1 level down )
Select Id, ( Select Id from Contacts ) From Account
Nested SOQL : 

In Nested SOQL, we can retrieve the related objects data using the SOQL query. We can query on the parent object and get details about parent and child records.

For example, following SOQL query retrieves Accounts, related Contacts, , and related Cases  associated with each Account:

Select Id, Name, (Select Id From Contacts), (Select Id From Cases) from Account LIMIT 10

Contacts is a child relationship name for Account.  To access child records, we append  ‘s’ for standard objects and ‘__r’ for custom object. This works for both master-detail and lookup relationship.

Please use Workbench to get the child relationship name. You can get to Workbench from URL https://workbench.developerforce.com

In Workbench, Go To:   Info > Standard & Custom Objects > Select ‘Account’ > Click Child Relationships > Click Contact.AccountId > relationshipName

Aggregate Functions with Group BY and HAVING clause:

We use aggregate functions SUM, COUNT, AVG, MAX, MIN etc. to perform mathematical calculation on data retrieved by SOQL query. We can further filter the data using WHERE clause, group the data using GROUP BY clause and filter grouped data using HAVING clause.

For Ex:  We can get sum of amount and count of open Opportunities grouped by StageName where count of opportunities are more than 1.

SELECT StageName, SUM(Amount) OppAmount, COUNT(id) Total 
FROM Opportunity 
WHERE isClosed=False 
GROUP BY StageName 
HAVING count(ID) >1

NOTE: In Apex Code, we can only get the data of Aggregate queries using AggregateResult class. In order to access specific field, we need to use alias name.

For eg: We execute the following APEX code to access sum of amount for specific Opportunity using “OppAmount”.

AggregateResult [] opps = [ SELECT StageName, SUM(Amount) OppAmount, COUNT(id) Total FROM Opportunity where isClosed=False GROUP BY StageName HAVING count(ID) >1 ];
System.debug(opps[0].OppAmount);

NOTE: We can’t use aggregate functions inside sub query

Sorting Data using ORDER BY:

We can sort the data in SOQL using ORDER BY clause with ASC (Ascending) or DESC (Descending) keywords.

For Ex: We can get Opportunities in ascending order based on the name of Opportunity.

Select Id, Name From Opportunity ORDER BY Name ASC
LIMIT the Rows using OFFSET and LIMIT:

We can limit the data using OFFSET and LIMIT by putting these keywords in the end of SOQL query. Use OFFSET to specify the starting row offset into the result set returned by your query.

For example, the following SOQL query returns a result set that skips the first 100 rows of the full query results:

SELECT Name FROM Opportunity LIMIT 50 OFFSET 100

The resulting set of records would still be limited by 50, but would begin with the 101st record in the result set.

Dynamic SOQL

If you want to build your SOQL query based on a text filed or a parameter at run time, you can construct the SOQL query in a string and use Databse.query to execute the SOSL query.

String myString = 'John Smith';
List sobjList = Database.query('SELECT Id, Name FROM Account WHERE Name = :myString');

Author:

Ajomon Joseph AJOMON JOSEPH
Senior Salesforce Architect
In Connect me on LinkedIn
twitter Follow me on Twitter @apexcoder
blogger Subscribe to my blog https://apexcoder.com
email Email me ajomon@apexcoder.com

 

2 thoughts on “How to use SOQL Query with Nested SOQL, Aggregate functions, ORDER BY, GROUP BY, HAVING, OFFSET and LIMIT?

  1. Hi Ajomon,

    Thanks for such great article.
    My question is, Is it possible in SOQL to have nested aggregate functions ?
    For example: [SELECT SUM( COUNT( Id ) ) FROM Case WHERE Accountid = ‘001a000001aAaaa’ GROUP BY My_Custome_Field__C]

    Any help will be appreciated.

    Thanks,
    Tejas.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s