WHAT IS SOQL?
SOQL(Salesforce Object Query Language) is a language that is used to query data from your Salesforce Org for specific information.
SOQL is similar to the SELECT statement in the widely used SQL(Structured Query Language), but it doesn’t support all advanced features of the SQL SELECT command.
With SOQL you can construct simple yet powerful queries to pull data from your Org.
WHEN TO USE SOQL
Use SOQL query when you know which Object the data resides in and you want to
- Retrieve data from a single object or from multiple Objects that are related to each other.
- Count the number of records that meet a specific criteria.
- Sort results as part of the query.
HOW TO WRITE SOQL
A simple SOQL can be written using SELECT and FROM keywords.
The field names we use in SOQL must be the API names of the fields.
SELECT Id, Name FROM Opportunity
USING WHERE CLAUSE
We can use the WHERE keyword to specify a condition
SELECT Id, Name FROM Opportunity WHERE LastName = 'Lock'
The Logical operators that can be used are AND, OR and NOT
Select Id, Name from Opportunity where LastName = 'Lock' AND FirstName = 'John'
Select Id, Name from Opportunity where LastName = 'Lock' OR FirsttName = 'Will'
SELECT Id, Name from Opportunity WHERE Amount = 5000 AND ( LastName = 'Smith' OR FirstName = 'Tim' )
SELECT Id, Name FROM Opportunity WHERE NOT (stageNme = 'Closed')
SELECT opp.FirstName, opp.LastName from Opportunity opp
Here opp is the Alias of the Opportunity.
SELECT FirstName, LastName FROM Contact Con, Con.Account Acct WHERE Acct.Name = 'Genepoint'
The following SOQL keywords cannot be used as alias names:
AND, ASC, DESC, EXCLUDES, FIRST, FROM, GROUP, HAVING, IN, INCLUDES, LAST, LIKE, LIMIT, NOT, NULL, NULLS, OR, SELECT, USING, WHERE, WITH.
COMPARISON OPERATORS IN SOQL
- = (Equals)
- != (Not Equals)
- < (Less Than)
- <= (Less Than or Equal)
- > (Greater Than)
- >= (Greater Than or Equal)
- LIKE (Like , is supported for string fields only)
- IN (In)
- NOT IN (Not In)
- INCLUDES, EXCLUDES (Applicable for Multi Select Picklists)
QUERY ON CUSTOM FIELDS, RELATIONS AND CUSTOM OBJECTS
The API name for custom fields and objects always ends with ‘__c’ (underscore-underscore-c) and the empty spaces in between will be replaced by ‘_’(underscore).
You can query custom fields on Standard object like this
SELECT Total_Revenue__c, SubStage__c FROM Opportunity
You can query custom fields on Custom Object like this
SELECT Company_Name__c, Number_of_Employees__c from Company__c
When you use a relationship name in a query, you must use the relationship names without the __c. Instead, append an __r (underscore underscore r).
SELECT Id, FirstName__c, Mother_of_Child__r.FirstName__c FROM Daughter__c
Here Daughter is the child Object and Mother of Child is a lookup field on the Daughter object.
Using (__r) the data of the fields from parent objects can be pulled in a query.
RELATIONSHIP QUERIES ON STANDARD OBJECTS
Parent to child and child to parent relationships exist between the objects in Salesforce
There are relationship names for each relationship. The name of a relationship depends on the direction of the relationship.
The relationship names for Standard objects are provided by Salesforce and for the custom objects we can define the relationship name while creating the relationship.
For example Account is a parent of Contact. The relationship name on the contact object to the Account object is ‘Account’.
Below is the example of a query for this child to parent relationship
SELECT Id, Name, Account.Name FROM Contact WHERE Account.Industry='Media'
For parent to child relationships, the parent object will have a child relationship name that is unique to the parent, the plural of the child object name.
Account has child relationship to Contact, and the relationship name is ‘Contacts’
Below is an example of a query for Parent to child relationship
SELECT Name, ( SELECT LastName FROM Contacts) FROM Account
RELATIONSHIP QUERIES ON CUSTOM OBJECTS
Let us assume Child and Parent are two custom objects where we have a child to parent relationship and the field name on the Child Object is Parent of Child.
The child to parent relationship query looks like this
SELECT Id, FirstName__c, Parent_of_Child__r.FirstName__c FROM Child__c WHERE Parent_of_Child__r.LastName__c = 'Smith'
The parent to child relationship query looks like this
SELECT ParentName__c, Age__c, (SELECT Child_Name__c, Child_Age__c FROM Childs__r) FROM Parent__c
SELECT Id, Name, Father__r.Name, Mother__r.Name FROM Child__c
Where Father and Mother are the Parents to the Child object.
To know the limitations of SOQL query, check this link.
LIKE Operator can be used instead of ‘=’ as shown below
SELECT Id, Name FROM Account Where FirstName LIKE 'Will'
The % and _ wildcards are supported for the LIKEoperator.
The below query returns all the records that start with the name Will and contain the word Will in their names. Like Will, William, Williamson etc… but not Twill
SELECT Id, Name FROM Account where Name LIKE 'Will%'
The below query returns all the records that contain the 5 letter word with Will and an extra letter appended to it. It returns the record with the names Like William, Willford, Willamson etc… but not Will.
SELECT Id, Name FROM Account where Name LIKE 'Will_%'
Aggregate functions include AVG(), COUNT(), MIN(), MAX(), SUM() and more.
AVG() returns the average of Amount for all the Opportunities.
Select AVG(Amount) from Opportunity
COUNT() returns the number of rows that match the filtering conditions.
SELECT COUNT() FROM Account WHERE Name LIKE 'a%'
This query returns the number of rows or records where the Name starts with a
COUNT(fieldName) returns the number of rows that match the filtering conditions and have a non-null value for fieldName.
SELECT COUNT(FirstName) FROM Account WHERE Name LIKE 'a%'
COUNT_DISTINCT(fieldName) returns the number of unique rows that match the filtering criteria and have a non null value in the field.
SELECT COUNT_DISTINCT(LastName) FROM Opportunity
MIN() function returns the minimum value of a field for an Object
Select MIN(CreatedDate) from Account
MAX() function returns the maximum value of a field for an Object
Select MAX(LastModifiedDate) from Contact
If you use MIN() or MAX() functions on a picklist field, the function uses the sort order of the picklist values instead of alphabetical order.
SUM() function returns the total sum of a numeric field
Select SUM(Amount) from Opportunity
The ORDER BY keyword can be used to control the order of the query results
SELECT Name, Industry FROM Account ORDER BY CreatedDate
ASC or DESC specifies whether the results are ordered in ascending or descending. The default order is ascending.
NULLS FIRST or NULLS LAST orders null records at the beginning or end of the results. Null values are sorted first by default.
SELECT Amount from Opportunity ORDERBY Amount DESC NULLS LAST
LIMIT is an optional clause that can be used to specify the number of records or rows returned by a query.
SELECT Name FROM Account WHERE Industry = 'Media' LIMIT 125
When querying many records, we can display the results in multiple pages using the OFFSET clause.
SELECT Name FROM Merchandise__c WHERE Price__c > 5.0 ORDER BY Name LIMIT 100 OFFSET 10
The FIELDS() function lets you fetch the fields either Standard, Custom or All.
FIELDS(ALL)—to select all the fields of an object.
FIELDS(CUSTOM)—to select all the custom fields of an object.
FIELDS(STANDARD)—to select all the standard fields of an object.
SELECT Id, FIELDS(CUSTOM) FROM User LIMIT 200
You can use the GROUP BY option in a SOQL query to avoid iterating through individual query results. That is, you specify a group of records instead of processing many individual records.
SELECT LeadSource FROM Lead GROUP BY LeadSource
SOQL Injection involves taking user supplied input and using those variables in a dynamic SOQL query. If the input is not validated, it can include SOQL commands that effectively modify the SOQL statement and trick the application into performing unintended commands.
String name; String qryString = 'SELECT Id FROM Contact WHERE ' + '(IsDeleted = false and Name like \'%' + name + '%\')'; List<Contact> queryResult = Database.query(qryString);
Here the user provides an input value called name. If the user provides a legitimate value, the statement executes as expected. However if the user provides unexpected input the results show a different set of records. This is called SOQL injection and this flaw can be used to modify the intended logic of any vulnerable query.
To prevent SOQL injection attacks use binding variables.
USING BINDING VARIABLES IN QUERY
A bind variable is an Apex variable that you use in a SOQL query.
Use bind variables as placeholders for specific values to be provided later.
A bind variable in a query is like a parameter in a method. The bind variable is replaced by a specific value when the query runs.
Id oppId = '0084xbgmnjkq879fyhgt'; SELECT Id, Name FROM Opportunity WHERE Id = :oppId
A list of Ids can also be used in a query
SELECT Id, Name FROM Opportunity WHERE Id IN :oppIds
Where oppIds is a list of Ids.
WRAPPING IT UP
In this blog we have covered how to write SOQL in salesforce using different keywords and clauses.