You must be familiar with basic SQL, a database programming language used to retrieve data from database.Working with SOQL is not much different. Salesforce’s official object query language lets you access records in your Salesforce database with simple and powerful query strings. These query strings can be used in:
- Apex statements
- Visualforce getter methods and controllers
- queryString param passed in the query() call
- Schema Explorer in the Eclipse Toolkit
SOQL uses the SELECT statement combined with filtering statements to retrieve results from databases. As a case insensitive language, it allows you write queries in Uppercase, Lowercase or both.
General syntax for writing queries in SOQL:
WHERE filter statement(s) and (optionally) order the results
Some of the latest features in SOQL:
Here is the list of new SOQL features introduced by Salesforce in the past one year:
1. “USING SCOPE” query string
You can use USING SCOPE clause to return records within a specified scope. It provides query filtering that lets you limit results with filterScope.
In this case ‘Mine’ will return everything that the logged in user owns. Other values that can be used in place of ‘Mine’ are Everything, Queue, Delegated, MyTerritory, MyTeamTerritory or Team.
2. Location based SOQL queries
You can use location and distance variables in binding expression that lets you reference Apex variables in SOQL and SOSL queries preceded by a colon(:).
To have a better understanding of the GeoLocation field, click here.
Let’s try to find all the accounts whose head offices are located within 5 miles of my current location. To achieve this, use your current location’s latitude and longitude as apex bind variable in SOQL query.
In my account object, create one custom field of type Geolocation with the name “Office Location” (API Name: Office_Location__c).
The “DISTANCE” and “GEOLOCATION” function works as given below:
- DISTANCE(myloc1, myloc2, ‘unit’)
- GEOLOCATION(latitude, longitude)
3. Submit Apex jobs with SOQL queries
You can submit Apex jobs by performing SOQL queries on AsyncApexJob by filtering on the job ID that System.enqueueJob method returns.
AsyncApexJob jobInfo = [SELECT Status, NumberOfErrors FROM AsyncApexJob WHERE Id=:jobID];
4. Query topics using nested semi-joins
You can use nested semi-joins for the TopicAssignment object to allow queries on Knowledge articles assigned to specific topics.
TopicId =’0T0xx0000000xxx’)) ORDER by NormalizedScore DESC LIMIT 900
5. COUNT() Aggregate Function in SOQL Queries for external objects
You can include COUNT() in SOQL queries for external objects:
- By including total row count that’s determined after any $filter system query options
- By supporting $inlinecount system query option
For more details about $inlinecount and $filter check this link
6. Indexed Column added to lists of fields in Setup
You can add Indexed Column in Setup to indicate indexed field in the database.
Filter conditions in reports, list views, and SOQL queries target indexed fields for better response time.
7. Speed up queries with the Query Plan Tool
You can speed up SOQL queries with the Query Plan Tool available in the developer console.
To use the tool you can enter your query and click the Query Plan button in the Query Editor. The Query Plan window displays all query operations and the cost of each.
SOQL is a tool that lets you access records in your Salesforce database. Here’s a list of benefits that Salesforce Object Query Language provides:
- Retrieving information from Salesforce via API
- Optimizing code
- Reducing workload and time
- Extracting data from objects
- Accessing records that aren’t available in trigger
SOQL does not allow you to:
- perform arbitrary join operations
- use wildcards in field lists
- use calculation expressions
- run query for more than 120 seconds
- modify datasets directly
Want to implement these features?
Disclaimer: Salesforce, Salesforce Object Query Language,Service Cloud, Sales Cloud, Marketing Cloud, Community Cloud, Analytics Cloud, Wave, SFDC, Salesforce Communities, etc. are all trademarks of Salesforce.com Inc.
Popular Blog Posts