Hands on with SOQL in Salesforce

Updated: Apr 30

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 :

1. Workbench

2. Editor in Developer Console


There are other third party tools are available but lets just focus on these two in this article.


Using Workbench


Its is a Salesforce web-based tool. To use workbench follow the below steps:


  1. Navigate to https://workbench.developerforce.com/login.php.

  2. For Environment, select Production.

  3. Select the latest API version from the API Version drop-down menu.

  4. Accept the terms of service, and click Login with Salesforce.

  5. Enter your login credentials and then click Log in.

  6. To allow Workbench to access your information, click Allow.

  7. After logging in, select queries > SOQL Query.

  8. 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.


SOQL queries


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

or

SELECT count() FROM Contact

or

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



393 views

©2019 by SFDC Blogs.