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 

1. Retrieve data from a single object or from multiple Objects that are related to each other.
2. Count the number of records that meet a specific criteria.
3. 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'

LOGICAL OPERATORS

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')

ALIAS NOTATION

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

JUNCTION QUERY

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

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

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

ORDER BY

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

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

OFFSET

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

FIELDS()

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

GROUP BY

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

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.