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.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.
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');
Senior Salesforce Architect
Connect me on LinkedIn
Follow me on Twitter @apexcoder
Subscribe to my blog https://apexcoder.com
Email me email@example.com