SOQL
,
As a developer, you are no doubt already familiar with some query languages. The most common query language is SQL, or Standard Query Language, the universal standard for interacting with relational databases.
Just as the Force Platform data repository is similar to a relational database, SOQL is similar to SQL. In most places where there is overlapping functionality, the syntax for SOQL is identical to SQL. But there are a few important differences between the two data access languages.
SOQL is a query-only language – SQL has syntax for both reading and writing data, while SOQL is only used to retrieve records from your Force Platform objects. Later in this chapter, you will learn about how to write data using Apex data manipulation language.
SOQL uses relationships, not joins – In SQL, you implement relationships between tables using join syntax in the where clause of the query. SOQL syntax recognizes relationship fields to identify the connections between objects, but does not support join syntax. You will see how to use relationships in your SOQL statements in the next section.
SOQL does not support all SQL keywords – SOQL syntax is described in brief in the following section, and in more detail in the Force Platform API documentation. You should assume that SQL syntax not covered in the documentation is not accepted in SOQL.
|
As a developer, your career might have encompassed using several different relational databases, and all of them used a similar form of SQL. You might be asking yourself the question in the title of this sidebar—why didn’t the Force Platform developers use SQL?
The simple answer is that Force Platform objects are not the same as SQL tables and the Force Platform data store is not the same as a relational database. Access to data is always through some dialect, rather than directly, whether that interface is SQL or something else. In this fashion, SOQL provides query capabilities for Force Platform objects. The Force Platform environment offers you a lot of functionality and developer productivity based on the use of these objects, which are not the same as relational tables.
The complete Force Platform platform gives you everything you need to store and manipulate your data–the language and methods used to accomplish these ends are just a little different than those used for classic relational databases.
|
SOQL syntax
At a high level, SOQL syntax follows this pattern:
select <fieldnames> from <object>
[where <conditions>] [<order_by> field_name] [<limit> integer]Each of the areas of the SOQL statement is explained in more detail below.
| fieldnames |
| | A list of fields returned from the SOQL statement. You can return any fields in any of the objects named in the statement, as well as the expression count(), which returns the number of records selected by the statement. All SOQL statements also return the IDs for the returned records. |
Voice of the Developer
| Note that the * qualifier in the select clause is not used in SOQL—you must explicitly state the fields you require. If your code attempts to read a field that has not been selected by the SOQL statement, an exception is thrown. |
| object |
| | A SOQL query is aimed at an individual object, although you can also access data in related objects, as discussed below. You can specify an alias after the object name, as in the following:
select p.Name, p.Department from position__c p
|
| where <condition> |
| | The optional where clause can contain one or more conditions that limit the records returned from the SOQL query. Each where condition consists of a field name in the target object, a comparison operation, and a value. Comparison operators include all the standard comparisons, such as =, !=, <, >, <= and >=, as well as LIKE, IN and NOT IN. LIKE performs a case-insensitive comparison between the value of the field and the specified value. The values for the LIKE operator frequently use the wildcards '_' (to indicate a single character) or '%' (to indicate any number of characters, including no characters at all). The IN operator points at a set of values and returns true if the value for the field is one of the values listed, while NOT IN returns true if the field value is not in the list of values. Both of these operators can use a collection object as the value.
You can use the AND and OR keywords to combine multiple conditions. Best practices recommend that you use parentheses to insure clarity between multiple where conditions, as in the following:
where (p.Name like 'a') OR (p.Name like 'B%' and
p.Department = 'IT')
A where clause can use a bind variable for a value. A bind variable is a variable defined in your Apex code whose value is substituted into the SOQL statement at runtime. A bind variable is indicated by a colon preceding the variable name, as in the following:
String[] validNames =
new string[]('Jones', 'Smith', 'Katz'),
select Name from Candidate__c c
where c.Last_Name__c IN :validNames;
|
| order by <field> |
| | The optional order by clause specifies an order for the records returned from a SOQL query. You can sort on one or more columns, and use the keywords ASC or DESC to indicate the sorting order. As with comparisons, all sorting is done in a case-insensitive manner.
You can also specify NULLS FIRST, the default, or NULLS LAST to indicate where null values are placed in the sorting order.
|
| limit integer |
| | The optional limit clause places a limit on the number of records returned from a SOQL query. |
Relationship Queries
As mentioned above, SOQL statements target a single Force Platform object. But you can easily access related objects to the target object with simple syntax.
To access a parent object from a child object, you simply use dot notation, as in the following:
Select JA.Name, JA.postion__r.Job_Description__c
from Job_Application__c JAYour SOQL statement travels up the relationship chain from the base object, but notice that you use the syntax of __r (two underscores and an 'r') to indicate the relationship, rather than the __c to indicate the field.
The lookup field contains the ID of the related record.
You can traverse up to 5 levels of child to parent relationships, so the following SOQL statement is valid:
select Int.Name,
Int.Job_Application__r.Position__r.Job_Description__c
from Interview__c IntYou can also use SOQL to retrieve data from child objects of the target object. Among the objects you are using in this book, a Position record can be the parent of one or more Job Application records. To access the Position record and values from its related child Job Application records, you use syntax similar to a sub-query, as in the following:
Select p.Name, (select JA.Name
from Job_Applications__c JA) from Position__cYou can only include one level of child records in a single SOQL statement.
|
With SOQL, the default behavior is to perform an outer join, which includes results that do not have a matching value on the other side of the relationship. This behavior has several repercussions involving lookup relationships.
If you use a lookup relationship field in a where clause, and there is no parent record, the condition is ignored. In the following query, an Interview record that does not have a parent Job Application is returned, along with any Interview records whose associated Job Application meet the selection criteria:
select Int.job_application__r.Name from Interview__c Int
where Int.job_application__r.Status = 'Open';
|
SOQL in Action
Now that you understand the basics of SOQL, you can get some practice in using SOQL statements in your Apex code.
You begin by creating a trigger to assign interviewers for a particular Job Application. This trigger starts in this section and finishes when you learn about how to write data back to a Force Platform object in a later section.
This trigger automatically assigns interviewers for a candidate once the Status of their job application changes to Interview.
Create a trigger using the Force Platform Setup menu in the following manner.
1. | Go to Setup
Objects
Job Application and click New in the Trigger section.
|
2. | Set the name of the trigger to assignInterviewers and the events that will fire the trigger to after insert and after update. Your initial code should look like the following:
trigger assignInterviewers on Job_Application__c (after insert,
after update) {
} |
3. | Since all triggers work on bulk records, you should begin your trigger with a loop that will iterate through the members of the trigger.new array by adding the highlighted code:
trigger assignInterviewers on Job_Application__c (after insert,
after update) {
for (Job_Application__c JA : trigger.new){
}
} |
4. | You only want this trigger to run if the Status field on the Job Application object changes to Interviewing. Add a conditional statement to pick up this condition with the new code you see highlighted below:
trigger assignInterviewers on Job_Application__c (after insert,
after update) {
for (Job_Application__c JA : trigger.new){
if (JA.status__c == 'Interviewing') {
}
}
}Caution
| This trigger automatically adds Interviewers to a Job Application record when the Status is changed to Interviewing, but does not handle other potential situations. In the current scenario, a user can change the Status from Interviewing to another Status. Prevent this from occurring with either a validation rule, which only allows the Status to be changed to certain values when the previous value was Interviewing, or another trigger to delete the existing Interview records associated with the Job Application in the appropriate situations. |
|
5. | Your first use of SOQL comes next, when you define a new variable to hold the User records for potential interviewers in the highlighted line of code below:
trigger assignInterviewers on Job_Application__c (after insert,
after update) {
User selectedInterviewers = [select ID from user
order by last_interview_assigned__c limit 2];
for (Job_Application__c JA : trigger.new){
if (JA.status__c == 'Interviewing') {
}
}
} |
This SOQL statement returns records from the User object. The sorting of the records is on the last_interview_assigned__c field, which was added to the User record with the initialization scripts at the start of this chapter. insures that the three returned will be those users whose last interview was the most distant, since Universal Containers assigns interviews in a round robin fashion. Of course, you could have a different selection or sort criteria to retrieve different interviewers.
At this point, you are ready to assign interviewers to a particular job application, once the status of that application is changed to Interviewing. But before moving to the final stage of adding interview records to the job application record, you should learn about another way to retrieve records from your Force Platform data store—a method that allows you to retrieve records from different objects with a single query.
