By continuing to use our website, you consent to the use of cookies. Please refer our cookie policy for more details.
    Grazitti Interactive Logo

      Salesforce

      Optimize Custom Codes with SOQL Queries

      Aug 03, 2015

      4 minute read

      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

      Data RetrievalImage Credits:http:www.sammysdot.blogspot.in

      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:

      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.

      Query Plan ToolImage 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?

      At Grazitti InteractiveTM, we have diverse skill-sets across multiple technologies and cloud platforms to provide design, development, and deployment solutions. From Salesforce and other CRM implementation, third party integrations, community development to application development, our team of certified experts delivers custom, robust and agile solutions. Companies like Marketo, PingIdentity, MobileIron, Centrify, Glassdoor, and more rely on us for their custom Salesforce requirements. To get in touch, drop us a line at info@grazitti.com

      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.

      What do you think?

      0 Like

      0 Love

      0 Wow

      0 Insightful

      0 Good Stuff

      0 Curious

      0 Dislike

      0 Boring

      Didn't find what you are looking for? Contact Us!