Hands on with SOQL in Salesforce
Updated: May 1, 2020
SOQL stands for Salesforce Object Query Language. It is syntactically similar to SQL (Structured Query Language).In this article we will cover some of important soql queries which are commonly used in daily programming. For those who have little bit of knowledge of SQL can relate to it pretty well and those whose who don't need not to worry. Lets get started :
Execute a SOQL Query
To use SOQL first you need to know the editors where you can execute queries. Here are some of the platforms :
2. Editor in Developer Console
There are other third party tools are available but lets just focus on these two in this article.
Its is a Salesforce web-based tool. To use workbench follow the below steps:
Navigate to https://workbench.developerforce.com/login.php.
For Environment, select Production.
Select the latest API version from the API Version drop-down menu.
Accept the terms of service, and click Login with Salesforce.
Enter your login credentials and then click Log in.
To allow Workbench to access your information, click Allow.
After logging in, select queries > SOQL Query.
Select the object and execute the Query.
Using Query Editor in Developer Console
1. Open developer console in salesforce org.
2. Click on the Query editor option in Developer console.
3. In Query editor, execute the Query.
Select an record :
In the below query we are using Account object. Specifies a list of one or more fields, separated by commas, that you want to retrieve from the specified object.'Limit' keyword is used to retrieve number of records.
SELECT Id,name FROM Account LIMIT 1
SELECT count() FROM Contact
SELECT Contact.Firstname, Contact.Account.Name FROM Contact
Ordering the records
You can apply ordering on numeric as well as alphabetic data. The query will look like this:-
SELECT Id FROM Account WHERE Name ='test' order by asc nulls first
Filtering Your Results
The query includes the required single quotes, as well as leading and trailing percentage signs to indicate that it’s a wildcard search.
% and _ wildcards are supported for the LIKE operator. The query will look like this:
SELECT FirstName, lastname FROM Contact WHERE lastname LIKE 'test%' // get all the records that starts with test
NOTE:- in like statement you can pass only single word or matching char not multiple string with ','.
Accessing Parent Child Relationship
Just like in SQL, SOQL uses a foreign key to relate these two objects. SOQL has two basic relationship query types that you need to know:
Child to Parent
Parent to Child
Child to Parent
It is used in none to one relationship.Suppose you have two object Account(Parent) and Contact (Child).I want all the contact with their Account name like right join in SQL.This relationship query uses "dot notation" to access data from the parent. The query will look like :-
Accessing standard object data
SELECT FirstName, LastName,Account.Name,Account.Id FROM Contact
Accessing custom object data
I have two custom objects Employee(Parent) and Department(child).
SELECT id,name,Employees__r.name FROM Employee_Department__c
Parent to Child
Now, What if i want to access all accounts and their respective contacts. To access many to one relationship (where a single Account linked to multiple Contact records), that is why it uses nested query which returns a list corresponding to single record . The query will look like:
SELECT Name, (Select firstName, lastName FROM Contacts) FROM Account
The relationship name inside the nested query uses the plural name Contacts, as opposed to Contact. When working with relationship queries, the parent-to-child relationship name must be a plural name.
When working with custom objects, the relationship name is not only plural, but it is appended with two underscores and an r. For example, the relationship name for the custom object My_Object__c is My_Objects__r. The query will look like :
SELECT id ,(select name from Employee_Departments__r) FROM Employee__c