Introduction:
Every organization requires to keep track of information or data about their partnerships and customers, and it makes the CRMs very famous. Salesforce is very known as the platform of cloud-based CRM in which you can store a bunch of information or data and one that offers the user access to this information from any of the locations across the world. Whether it’s Marketing, Sales, or anything else the customer uses to connect with them, it is possible through using the Salesforce platform.
There was a time when the user wanted to use the data or information in the raw form and to query on the specific criteria. In this article, you will learn how to query the database of Salesforce to uncover the insight valuables from the customer’s data.
We have three options to query the Salesforce database:
- Salesforce Object Query Language (SOQL)
- Data Manipulation Language or DML
- Salesforce Object Search Language (SOSL)
Once you have finished reading this article, the reader will be in a position to know what is meant by SOQL and how to execute SOQL to query the database of Salesforce by using the Query Editor in the Developer Console.
sObject:
At the core data model, Salesforce is something that can be referred to as the sObject or Salesforce Object. It has the data structure in the form of a table. It is organized into a concept of fields and records that are very similar to the columns in the table of the database.
Features of sObject:
- Queryable
- An extensible data model
Standard sObjects include:
- Contact
- Account
- Lead
- Case
- Opportunity
Standard sObject fields include:
- Name
- Id
- ModifiedBy/Date
- CreatedBy/Date
- IsDeleted
- Ownerld
SOQL:
The SOQL or Salesforce Object Query Language is the one that you are going to use the Salesforce query sObject layer for specific data or information. It has SQL-like syntax that can support SELECT statements.
SOQL can be used in:
- Developer tools (Ex: Visual Studio, Eclipse, Salesforce developer console, and so on).
- Apex Web Services API.
- API calls (SOAP, REST, etc.).
Differences between SQL and SOQL are quite subtle that include:
- No views.
- No wild cards like the statements of SELECT, and it is implemented in avoiding the impact of Salesforce performance production servers.
- Limited indexes.
- SOQL doesn’t support DML operations like DELETES, INSERTS, or UPDATES.
- Schema changes are protected.
- Object-relational mapping can be automatic.
Read More : A Guide to Triggers in Salesforce
When to use SOQL?
- When the record number meets certain criteria.
- When retrieving the information or data from an object or various objects that are very much related to one another.
- While retrieving the information or data from the date, number, or checkbox fields.
- Sorting the results that are part of a certain query.
Prerequisites:
In order to follow along, you must require to have:
1. Knowledge of relational database concepts like:
- Indexes
- Tables
- Foreign/Primary Key
- Joins
2. A basic knowledge of the Salesforce platform includes:
- Knowledge of the Salesforce field API names.
- Creating custom fields and objects.
3. In order to open the developer console from the Salesforce Classic:
- Click Your Name
- Choose Developer Console
4. Complete knowledge of the Query Editor and Sales Developer Console. You can use the developer console to run all these queries.
5. From the Lightning experience, to open the Developer Console:
- Click “Gear Icon.”
- Select Developer Console.
Structure of Simple SOQL:
SELECT one or more fields, [SubQuery]
FROM an object
WHERE filter statements
GROUP BY
HAVING Count(Email) > 2
ORDER BY CreatedDate ASC
LIMIT 10
OFFSET 5
FOR {VIEW, UPDATE}
Parent to Child Queries:
Normally, an inner query is used when the user wants to retrieve the related record for the particular parent record or the parent records set.
Here is an example for you: In order to pull the contacts that are related to a certain account, the user can the below SOQL query:
set<id> AccountID = new set<id>();
SELECT Id, Name, (Select Id, Name FROM Contacts) FROM Account WHERE Id IN:AccountID
From the above query, Contacts is the name of the relationship. For the standard objects of Salesforce, the relationship name is the plural label of the object:
Case => Cases
Contact => Contacts
Opportunity => Opportunities
For the custom objects, we can often see append__c, which can represent the relationship. Suppose the custom object name is Book and has a lookup with the Account. Then, in the query that happens on the inner side, it will be Books__c.
Child to Parent Query in SOQL:
The user may want to fetch some parent fields when working with the records of a child. In this case, we can go for a query of the child to the parent.
For example, while working with the records of contacts, the user can retrieve the address of Account Shipping from a particular contact by using the below SOQL:
SELECT Id, Name, AccountId, Account.ShippingAddress FROM Contact
Related Courses: Salesforce CPQ Training
Order By in SOQL:
Order By can be used when the user wants to get fields ordered in descending or ascending format.
SELECT Name, CreatedDate, Account Name
FROM Account
ORDER BY Name DESC
LIMIT 5
Having in SOQL:
The word “Having” is used to find duplicate records that are based on certain fields. Here is an example for you:
SELECT AccountId FROM Contact GROUP BY AccountId
HAVING Count(Id) > 2
Polymorphic Queries in SOQL:
SELECT I’d
FROM Event
WHERE What.Type IN(‘Account’, ‘Opportunity’)
Aggregate Queries in SOQL:
These can be used to search the aggregate result for a field. Below are a few methods that are supported in the aggregate queries in SOQL.
- Sum
- Min
- Count
- Max
- AVG
Here is an example for you:
SELECT Count(Id), AccountId
FROM Contact
GROUP BY AccountId
Limitations:
When the user query the database of Salesforce, the usage of the query can be metered explicitly. There are:
- Apex governor limits.
- API batch limits for various records that are returned.
Conclusion:
Hope you learned about SOQL and the types of queries that are supported well in the SOQL and knowing when to use them. If you still have any queries regarding Salesforce, Contact Gologica or call 8296960414.