Getting Started with SOQL (2014)

Chapter 5. Limitations and Best Practices

In this chapter, we will take a look at the standards and best practices to be followed by an administrator or a developer while writing SOQL statements during development and administration tasks. We will also cover the limitations that should be considered while writing the SOQL statements.

Salesforce has set many limitations as all the Apex code runs on the Apex engine. Apex is an object-oriented programming language that allows the Salesforce developers to execute flow and transaction control statements on the Force.com platform. So, whenever we write the SOQL statements, we should make sure that we do not hit the limitations set by Salesforce. All the limitations will be explained in detail.

We will also discuss in detail the limitations in OFFSET, toLabel(), COUNT(), and ORDER BY in the SOQL statements and the limitations in writing the relationship queries will.

Standards to be followed in SOQL

Let's take a look the standards to be followed in SOQL.

If we want avoid the number of records fetched from an object, we have to use the LIMIT option. The LIMIT option limits the number of records that were fetched and avoids the limits in Salesforce.

We have seen the reserved keywords in SOQL in Chapter 1, Introduction to SOQL. In order to differentiate between the reserved keywords in a SQOL query, always write the reserved keywords in uppercase so that it will be easy for us to identify them. Write SELECT,FROM, WHERE, HAVING, IN, and so on in uppercase.

If we want to use a single quote inside our SOQL statement, we will have to use a backslash followed by a single quote. This is called as an escape sequence. We are allowed to use the escape sequences in SOQL as shown in the following table:

A sample query is given as follows:

SELECT Id, Industry FROM Account WHERE Name LIKE 'Infallible Techie\'s Company'

Best practices

We can use SOQL in the following situations:

· When we know the object in which we have our required data

· When we want to fetch data from multiple objects with a lookup or master-detail relationship

· When we want to have the data set in a sorted manner

· When we want to summarize the data

· When we want to limit our data while retrieving it

For indexing, the following fields can be used in SOQL:

· Id can be used as the primary key.

· Lookup or master-detail relationship fields can be used as the foreign key.

· The custom fields can be used as the external IDs. An external ID in Salesorce is used as unique record identifiers from a system outside of Salesforce. When you select this option, the import wizard will detect existing records in Salesforce that have the same external ID. Note that this operation is not case-sensitive. For example, ABC will be matched with abc.

Here, Id is the record ID that is autogenerated whenever a new record is created. The record ID of Salesforce represents a unique record within a Salesforce instance. There are two versions of every record ID in Salesforce. They are as follows:

· A 15-digit case-sensitive version, which is referenced in the user interface

· An 18-digit case-insensitive version, which is referenced through the API

Fields that can't be indexed in SOQL are as follows:

· Multiselect picklists

· The Currency fields in a multicurrency organization

· The long text fields

· Some formula fields

· The binary fields (fields of the type blob, file, or encrypted text)

Limitations in objects

An object represents database tables that contain your organization's information. Objects in Salesforce are mainly used to store records. There are two types of objects in Salesforce: standard objects and custom objects. Salesforce-defined objects are the standard objects, and the objects created by a user in an organization are the custom objects. While writing the SOQL statements to fetch records from the objects shown in the following table, we have to check the limits set by Salesforce:

Other limitations

The following sections cover the limitations in the objects, apart from those mentioned in the preceding section.

Governor limits

As Apex runs in a multitenant environment, the Apex runtime engine strictly enforces a number of limits to ensure that the runaway Apex doesn't monopolize the shared resources. If some Apex code ever exceeds a limit, the associated governor issues a runtime exception that cannot be handled. The governor limits in the following table are subject to change, so use the following link to get the latest information:

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_gov_limits.htm

If a SOQL query runs for more than 120 seconds, the request can be canceled by Salesforce.

Understanding the limitations of the ORDER BY query

The limitations of the ORDER BY query are as follows:

· The data types that are not supported in ORDER BY are reference, multiselect picklists, and long text area.

· We can use ORDER BY with the optional LIMIT option in a SELECT statement.

· We are limited to using 32 fields in an ORDER BY SOQL query. If we exceed this limit, malformed query fault information is returned.

Understanding the limitations of the toLabel() query

The limitations of the toLabel() query are as follows:

· The toLabel() method cannot be used along with ORDER BY in a SOQL

· We cannot use toLabel() in the WHERE clause for the division or currency ISO code picklists

Understanding the limitations of the COUNT() query

The limitations of the COUNT query are as follows:

· The COUNT() query cannot be used with other elements in the SELECT list.

· We cannot use COUNT() with an ORDER BY clause. The option for this is COUNT(fieldname).

· We cannot use COUNT() with a GROUP BY clause for the API Version 19.0 and higher. The option for this is COUNT(fieldName).

Understanding the limitations of the OFFSET clause

The limitations of the OFFSET clause are as follows:

· The maximum OFFSET limit is 2,000 rows. If we set an OFFSET limit higher than 2,000, we will get the result in a NUMBER_OUTSIDE_VALID_RANGE error.

· The OFFSET clause is mainly focused to be used in a top-level query, and it is not allowed in most of the subqueries.

· The OFFSET clause is allowed in the SOQL that is used in SOAP API, REST API, and Apex. It's not allowed in SOQL used within bulk API or streaming API.

Limitations of the relationship queries

The limitations of the relationship queries are as follows:

· Objects should have a relationship between them to write relationship queries. We cannot write relationship queries if the objects don't have any relationship between them.

· We cannot write more than 35 child-to-parent relationships in a SOQL statement.

· We cannot write more than 20 parent-to-child relationships in a SOQL statement.

· In each specified relationship between objects, no more than five levels can be specified in a child-to-parent relationship.

· In each specified relationship among objects, only one level of a parent-to-child relationship can be specified in a query.

· For custom relationships, __r should be mandatorily added at the end of the relationship name.

Notes and Attachments limitations

The limitations of the Notes and Attachments objects are as follows:

· The Notes and Attachments objects cannot be filtered with the help of the content or body. It can be filtered only with Name, CreatedDate, and so on.

· The Notes and Attachments objects are not supported in the subquery.

Summary

In this chapter, we saw the standards to be followed while writing the SOQL statements. The best practices explained in this chapter allow us to retrieve the required records by properly filtering the data. As a developer or an administrator, we should follow these standards and best practices.

Whenever we write SOQL statements to fetch the data, we should be very careful about the limitations. The different limitations with the objects are to be considered while writing the SOQL statements to fetch data from the objects. The other limitations with OFFSET,ORDER BY, COUNT(), toLabel(), and governor limits should also be considered while writing the SOQL statements with these functions.

In the next chapter, we are going to take a look at the recommended installation guidelines to be followed while installing the tools for the SOQL statement execution. These tools will help us execute all the queries that we have discussed so far.