SOQL (Salesforce Object Query Language)
- Use SOQL to construct simple but powerful query strings in the queryString parameter in the query() call, in Apex statements, in Visualforce controllers and getter methods, or in the Schema Explorer of the Force.com IDE
- SOQL statements are similar to the select statement in SQL
- SOQL statements are case-sensitive
- SOQL statements can be used in Apex and the Web Services API
- If the output of a SOQL statement is an “Consider and Index Filter” error, it means the statement is returning too many records in which case you need to use a WHERE clause to restrict the # of records returned
- You can insert a SOQL statement in-line within Apex code using the following syntax: [SELECT Name FROM Account]
- Alternatively, you can use the Database.query method e.g. Database.query (‘SELECT’ + fieldName + ‘FROM Account’]
- SOQL Statements can return four data types:
- List of sObject: Use if the SOQL statement returns multiple records
- Single sObject: Use if the SOQL statement returns only one record
- Integer: Use if the SOQL statement returns an Integer value
- List of Aggregate results: Use when the SOQL statement uses a GROUP BY clause
SOQL Functions
List of SOQL functions:
- AVG()
- COUNT()
- COUNT_DISTINCT()
- MIN()
- MAX()
- SUM()
- CALENDAR_MONTH()
- DAY_IN_MONTH()
- FISCAL_YEAR()
- WEEK_IN_YEAR()
SOQL Keywords
List of SOQL Keywords:
- IN [used for bulk queries; uses a Set of a List of sObject(s) as an argument]
- LIKE
- AND/OR
- NOT
- ORDER BY
- GROUP BY
- LIMIT [returns the TOP record when used with ORDER BY]
- FOR UPDATE [locks the record from being updated by another request]
- ALL ROWS [returns active and deleted rows]
SOQL Bindings
SOQL statements in [ ] can reference an expression that is preceded by a : e.g.
String jobType = ‘Full-Time’;
List <Position> positions;
positions = [SELECT Name FROM Position__c WHERE Type__c = :jobType];
SOQL Queries – Different types of Joins
- Right-Outer
- Left-Outer
- Semi-Join
- Right-Inner
- Right Anti-Join
- Left Anti-Join
- Right-Inner Join w/Remote condition
When designing relationship queries, consider these limitations:
- Relationship queries are not the same as SQL joins
- You must have a relationship between objects to create a join in SOQL
- No more than 35 child-to-parent relationships can be specified in a query
- A custom object allows up to 25 relationships, so you can reference all the child-to-parent relationships for a custom object in one query
- No more than 20 parent-to-child relationships can be specified in a query
- In each specified relationship, no more than five levels can be specified in a child-to-parent relationship e.g. Contact.Account.Owner.FirstName (three levels)
- In each specified relationship, only one level of parent-to-child relationship can be specified in a query e.g. if the FROM clause specifies Account, the SELECT clause can only specify the Contact or other objects at that level. It could not specify a child object of Contact
SOSL (Salesforce Object Search Language)
- Use the Salesforce Object Search Language (SOSL) to construct text searches in the search() call, in Apex statements, in Visualforce controllers and getter methods, or the Schema Explorer of the Eclipse Toolkit
- Unlike SOQL, which can query only one object at a time, SOSL enables you to search text, email and phone fields for multiple object simultaneously
- Syntax e.g. FIND {Joe Smith} IN Name Fields RETURNING lead{name, phone}
Use SOQL when:
- You know in which objects or fields the data resides
- You want to retrieve data from a single object or from multiple objects that are related to one another
- You want to count the number of records that meet specified criteria
- You want to sort results as part of the query
- You want to retrieve data from number, date, or check-box fields
Use SOSL when:
- You don’t know in which object or field the data resides and you want to find it in the most efficient way possible
- You want to retrieve multiple objects and fields efficiently, and the objects may or may not be related to one another
- You want to retrieve data for a particular division in an organization using the divisions feature, and you want to find it in the most efficient way possible
Some additional considerations when using SOQL or SOSL
- Both SOSL search queries and SOQL WHERE filters can specify text to look for
- When a given search can use either language, SOSL is generally faster than SOQL if the search expression uses leading wildcards or a CONTAINS term
- In some cases, when multiple WHERE filters are being used in SOQL, indexes cannot be used even though the fields in the WHERE clause may be indexed. In this situation, decompose the single query into multiple queries each with one WHERE filter and then combine the results
- Executing a query with a null in a WHERE filter makes it impossible to use indexing. Such queries must scan the entire database to find appropriate records. Design the data model not to rely on nulls as valid field values
- If dynamic values are being used for the WHERE field and null values can be passed in, don’t let the query run to determine there are no records; instead check for the nulls and avoid the query if necessary
[message_box title=”Salesforce.com Certified” color=”blue”]From the small business to the large enterprise, we have solutions to help improve win rates.[/message_box]
Contact Us