Optimize Custom Codes with SOQL Queries

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

Image Credits:http:www.sammysdot.blogspot.in

General syntax for writing queries in SOQL:

SELECT field1, field2, field3

FROM an object

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.

For example:

SELECT Id FROM Account USING SCOPE Mine

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.

For example:

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

Public class FindDistance

{

Public static void calcDistance()

{

Double myCurrentLocationLatitude = 10.20;

Double myCurrentLocationLongitude = 10.20;

List allAccounts = [SELECT ID FROM ACCOUNT WHERE

DISTANCE(Office_Location__c,

GEOLOCATION(:myCurrentLocationLatitude,:myCurrentLocationLongitude),’mi’) < 5];

if(allAccounts.size() > 0)

{

System.Debug(‘Total number of accounts: ‘ + allAccounts.size());

}

else

{

System.Debug(‘No Account Found’);

}

}

}

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.

For example:

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.

For example:

SELECT parentId FROM KnowledgeArticleViewStat WHERE parentId in (SELECT KnowledgeArticleId FROM KnowledgeArticleVersion WHERE publishStatus = ‘Online’ AND language = ‘en_US’ AND Id in (select EntityId from TopicAssignment where

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.

Image credits:www.womencodeheroes.com

Benefits:

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

Limitations:

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.