Introduction to SOSL and SOQL
If we have built a custom User Interface for Salesforce, we can use Salesforce Object Search Language (SOSL) and Salesforce Object Query Language (SOQL) APIs to search your organization’s Salesforce data.
Here, we are going to describe that how we can use SOSL and SOQL and outlines the syntax, clauses, limits, and performance considerations for both languages. It is designed to work with APIs to interact with data for developers and assumes knowledge and experience.
Salesforce Object Search Language (SOSL) and Salesforce Object Query Language (SOQL) statements can be used to evaluate on-the-fly in Apex by surrounding the statement in square brackets.
Salesforce Object Search Language (SOSL)
Salesforce Object Search Language (SOSL) is used to write queries to search text against the search index.
Always create filters that are selective, when building efficient SOSL queries. By default, SOSL queries scan all entities. The search engine can return maximum of 2,000 records after matching the searched term or text. When the result set is returned from the search stack then sharing can be applied. There is a possibility of running into search crowding, If our filters are not selective and cause search term matches of more than 2000 records,.
SOSL statements are basically used to find or search the text format data, email, and phone fields for multiple objects, including custom objects, that we have access to in a single query.
For Free, Demo classes Call: 7350586163
Registration Link: Click Here!
SOSL Syntax:
FIND {Search Text} [IN SearchGroup/SearchField] RETURNING sObject(Fields to return);
Example: SOSL to search ‘Test’ text in all fields and return Account Object’s name, industry as result.
FIND {Test} IN ALL FIELDS RETURNING Account (Name, Industry);
Note: We can use multiple objects as returning data in SOSL.
Example: SOSL to search ‘Test’ text in all fields and return Account Object’s name, industry and Contact Object’s name, email as result.
FIND {Test} IN ALL FIELDS RETURNING Account (Name, Industry), Contact (Name, Email);
When to Use SOSL:
SOSL can be used when we don’t know which object or field the data resides in and we want to:
- Get or retrieve data for a particular text or term that we know exists within a field. Because SOSL searches are faster executable and can return more relevant results, so SOSL can be used to tokenize multiple terms within a field and build a search index from this.
- Get multiple fields & objects easily where the objects might or might not be related to one another.
- Provide or get data using the divisions feature for a particular division in an organization.
- Get or retrieve data that’s in Chinese, Japanese, Korean, or Thai. It also helps tp to ensure accurate results for Morphological tokenization for CJKT terms.
Some Examples of SOSL:
- FIND {John Smith}IN Name Fields RETURNING lead(name, phone)
- FIND {John Smith}IN Name Fields RETURNING lead (name, phone
Where createddate = THIS_FISCAL_QUARTER)
- FIND {“John Smith” OR “John Smythe”} IN Name Fields RETURNING lead(name, phone), contact(name, phone)
For Free, Demo classes Call: 7350586163
Registration Link: Click Here!
Salesforce Object Query Language (SOQL)
SOQL basically is a query language that is used to get record data from a Salesforce database. In Object-Oriented Programming Languages for the Admin we have learned that we can use Data Manipulation Language (DML) statements to insert, update, and delete records. But what if you want to retrieve or get the data that already exists in the database? SOQL makes the process very simple to fetch or retrieve data.
Writing SOQL queries is similar like we create a report. When we create a report, we have to answer questions such as:
- What fields do I want to display?
- Where are those fields located?
SOQL Syntax:
SELECT Field1, Field2, … , Field N FROM sObject_API_Name;
Example:
SELECT Id, Name, Email FROM Contact;
You ask the same questions when you create a SOQL query. In a query, the answers to these questions are the fields and the object.
Relationship SOQL Queries
As we know, In SOQL the FROM clause is limited to one object, we can access two related objects by using a relationship query. A relationship query is only based on the relationship between two different objects to return fields from both objects.
There are two types of relationship queries:
- Parent to Child SOQL
- Child to Parent SOQL
Parent to Child SOQL:
To get record for a Parent object, and include fields from a related child object, use a parent-to-child query.
Example: There are two objects Account and Contact. They are link in a relationship where Account is a parent object and Contact is a child object. Now we want to get data as Account’s name also want to get related contact’s name. So we can use parent to child query as following:
SELECT Name, (SELECT Name FROM Contacts) FROM Account
For Custom Object:
Example: There are two objects Teacher and Student. They are link in a relationship where Teacher is a parent object and Student is a child object. Now we want to get data as Teacher’s name also want to get related Student’s name. So we can use parent to child query as following:
SELECT Name, (SELECT Name FROM Students__r) FROM Teacher__c
For Free, Demo classes Call: 7350586163
Registration Link: Click Here!
Child to Parent SOQL:
To get or retrieve the record for a Child object, and include fields from a related parent object, use a child-to-parent query.
Example: There are two objects Account and Contact. They are link in a relationship where Account is a parent object and Contact is a child object. Now we want to get data as Contact’s name also want to get related Account’s name. So we can use child to parent query as following:
SELECT Name, Account.Name FROM Contact
For Custom Object:
Example: There are two objects Teacher and Student. They are link in a relationship where Teacher is a parent object and Student is a child object. Now we want to get data as Student’s name also want to get related Teacher’s name. So we can use child to parent query as following:
SELECT Name, Teacher__r.Name FROM Student__c
Aggregate Functions in SOQL
In reports, we use roll-up summary field’s functionality to calculate values from related records. For example, if we want to calculate the total number of properties or the average or highest value of properties in a city. In SOQL, we basically use aggregate functions to perform those and other calculations.
| Aggregate Function | Description | Example |
| COUNT() | Returns the number of rows that are associated with the field | SELECT COUNT(Name) FROM Broker__c |
| COUNT_DISTINCT() | Returns the number of unique rows that match the query criteria | SELECT COUNT_DISTINCT(City__c) FROM Property__c |
| MIN() | Returns the minimum value of a field | SELECT MIN(Days_On_Market__c) FROM Property__c |
| MAX() | Returns the maximum value of a field | SELECT MAX(Beds__c) FROM Property__c |
| AVG() | Returns the average value of a numeric field | SELECT City__c, AVG(Days_On_Market__c) FROM Property__c GROUP BY City__c |
| SUM() | Returns the total value of a numeric field | SELECT SUM(Price__c), Broker__r.Name FROM Property__c GROUP BY Broker__r.Name |
Summary
As you learned in this blog, SOQL & SOSL is a great tool for accessing data in your Salesforce org. As you are going to build and expand your Apex coding skills, you’ll encounter many opportunities to use your SOQL & SOSL knowledge.
Author
Rohit Kumawat
Salesforce Trainer
Seven Mentor Pvt. Ltd.