SOQL in Salesforce
What is SOQL?
SOQL (Salesforce Object Query Language) is used to fetch the data from the Salesforce database. It is conceptually very similar to the SQL (Structured Query Language), but it is only specific to Salesforce databases.
The basic syntax followed by SOQL looks something like this :
SELECT Id,Name FROM Account
NOTE: Unlike SQL, the SOQL doesn’t support the wildcard (*) operator. This operator could be used in SQL to fetch all fields from a table but that is not possible is SOQL.
Running SOQL Queries
There are multiple ways to run SOQL queries in an salesforce org :
Developer Console
Open the developer console in your org, and go to the Query Editor tab on the bottom.
Then we can write in the SOQL query statement inside of this section and click on Execute, which will run the query and return us the results.
Workbench
Workbench is a tool available for salesforce which can be used to do quite a lot of stuff, one of it includes running SOQL queries too.
Go to the workbench URL and login from your salesforce org :
https://workbench.developerforce.com/l
Once logged in, you need to go to Queries -> SOQL Query
Then you can use the Query builder to build out a query or just directly put the query in a text box below. Then click on the Query button and you should be able to see the results..
Workbench provides additional features like being able to query on deleted records, running the query via the Bulk API and many more.
In Apex Code
Running SOQL queries in Apex code is essential to access your salesforce data inside an apex class. We can run a SOQL query like this in an apex class :
public class Account_Ctrl { public list accs; public Account_Ctrl() { accs = [SELECT Id,Name FROM Account]; } }
In Apex, each SOQL query is enclosed within the square brackets, unless we use the database.query method which takes the query as a string.
listaacs = (list )database.query('SELECT Id,Name FROM Account');
List Example
The SOQL query returns a list of records which can be assigned to an list of Accounts in apex, like in the example like this :
list accs = [SELECT Id,Name FROM Account];
Map Example
Also, we can assign the returned records to a map in salesforce as well, like this,
map aacs = [SELECT Id,Name FROM Account];
This will automatically set the ID of the returned record into the map as a key, and the value will be the returned record for that id.
SOQL Where Clause
SOQL supports a way to apply filters to your queries by using a WHERE clause which behaves the same way like the SQL version. It allows you to create a logical statement with AND and OR operators to apply additional filters on the data returned in your SOQL query.
SELECT Id,Name FROM Account WHERE Name = 'Salesforce Drillers' AND Description != null
SOQL commonly used functions
Here are the few SOQL operators and functions that are used commonly:
LIMIT
SOQL Limit clause allows us to limit the maximum data which will be returned to us from an SOQL query.
SELECT Id,Name FROM Account limit 50
Group By and Order By
The group by clause can be used to group SOQL results based on certain fields, which can be used to apply aggregate functions in SOQL like COUNT, SUM, MIN and MAX.
SELECT COUNT(Id),Name FROM Account WHERE Group By Name
Note: The Group By keyword returns the AggregateResult list while you use that in apex. For example:
listaggr = [SELECT COUNT(Id),Name FROM Account WHERE Group By Name];
The order by clause is used to order the SOQL results based on a field in ASC (ascending, and default if not used explicitly) and DESC (descending) way.
SELECT Id,Name FROM Account Order By CreatedDate DESC
Relationship Fields in SOQL
Parent to Child
We can access relationship fields or parent or child records in SOQL too with the relationship name. For example if we want to query an Account record with all the Contact records under it, we can use the Contacts child relationship name to query it like this :
SELECT Id,Name,(SELECT Id,FirstName,LastName FROM Contacts) FROM Account
For any custom objects, the relationship name is appended with an __r suffix, signifying that it is a relationship field. This is what a parent to child query looks like on a custom object :
SELECT Id,Name,(SELECT Id,Name FROM Students__r) FROM Class__c
Child to Parent
For the other way around, if we want to fetch the parent account record’s information for a Contact record, we can use the Account lookup field to query it like this :
SELECT Id,FirstName,LastName,AccountId,Account.Name FROM Contact
And likewise if we do a query to fetch parent records on a custom object , we can again use the ___r appended relationship name of the field to query the data.
SELECT Id,Name,Class__r.Name FROM Student__c
Limits around SOQL
- We can only fetch a maximum of 50,000 data rows in one transaction with SOQL queries. So make sure to use the LIMIT keyword to make sure that your SOQL doesn’t fetch more than 50,000 rows.
- We can only do a total of 100 SOQL queries in one transaction.
- We can only fetch one level down child objects in the query, for example we can only go from the Account to Contact.
- While fetching Parent fields, the maximum level we can go upwards is 5 objects.