109
Example
Range
Date Literal
SELECT Id FROM Account WHERE
CreatedDate = LAST_N_DAYS:365
For the number
n
provided, starts 12:00:00 of
the current day and continues for the last
n
days.
LAST_N_DAYS:n
SELECT Id FROM Opportunity WHERE
CloseDate > NEXT_N_DAYS:15
For the number
n
provided, starts 12:00:00 of
the current day and continues for the next
n
DAYS.
NEXT_N_DAYS:n
SELECT Id FROM Account WHERE
CreatedDate = THIS_QUARTER
Starts 12:00:00 of the current quarter and
continues to the end of the current quarter.
THIS_QUARTER
SELECT Id FROM Account WHERE
CreatedDate > LAST_QUARTER
Starts 12:00:00 of the previous quarter and
continues to the end of that quarter.
LAST_QUARTER
SELECT Id FROM Account WHERE
CreatedDate < NEXT_QUARTER
Starts 12:00:00 of the next quarter and
continues to the end of that quarter.
NEXT_QUARTER
SELECT Id FROM Account WHERE
CreatedDate < NEXT_N_QUARTERS:2
Starts 12:00:00 of the next quarter and
continues to the end of the
n
th quarter.
NEXT_N_QUARTERS:n
SELECT Id FROM Account WHERE
CreatedDate > LAST_N_QUARTERS:2
Starts 12:00:00 of the previous quarter and
continues to the end of the previous
n
th
quarter.
LAST_N_QUARTERS:n
SELECT Id FROM Opportunity WHERE
CloseDate = THIS_YEAR
Starts 12:00:00 on January 1 of the current year
and continues through the end of December
31 of the current year.
THIS_YEAR
SELECT Id FROM Opportunity WHERE
CloseDate > LAST_YEAR
Starts 12:00:00 on January 1 of the previous
year and continues through the end of
December 31 of that year.
LAST_YEAR
SELECT Id FROM Opportunity WHERE
CloseDate < NEXT_YEAR
Starts 12:00:00 on January 1 of the following
year and continues through the end of
December 31 of that year.
NEXT_YEAR
SELECT Id FROM Opportunity WHERE
CloseDate < NEXT_N_YEARS:5
Starts 12:00:00 on January 1 of the following
year and continues through the end of
December 31 of the
n
th year.
NEXT_N_YEARS:n
SELECT Id FROM Opportunity WHERE
CloseDate > LAST_N_YEARS:5
Starts 12:00:00 on January 1 of the previous
year and continues through the end of
December 31 of the previous
n
th year.
LAST_N_YEARS:n
SELECT Id FROM Account WHERE
CreatedDate = THIS_FISCAL_QUARTER
Starts 12:00:00 on the first day of the current
fiscal quarter and continues through the end
of the last day of the fiscal quarter.The fiscal
THIS_FISCAL_QUARTER
year is defined in the company profile at Setup
➤
Company Profile
➤
Fiscal Year.
SELECT Id FROM Account WHERE
CreatedDate > LAST_FISCAL_QUARTER
Starts 12:00:00 on the first day of the last fiscal
quarter and continues through the end of the
last day of that fiscal quarter.The fiscal year is
LAST_FISCAL_QUARTER
defined in the company profile at Setup
➤
Company Profile
➤
Fiscal Year.
369
query()
112
Example
Range
Date Literal
SELECT Id FROM Account WHERE
CreatedDate < NEXT_FISCAL_QUARTER
Starts 12:00:00 on the first day of the next fiscal
quarter and continues through the end of the
last day of that fiscal quarter.The fiscal year is
NEXT_FISCAL_QUARTER
defined in the company profile at Setup
➤
Company Profile
➤
Fiscal Year.
SELECT Id FROM Account WHERE
CreatedDate <
NEXT_N_FISCAL_QUARTERS:6
Starts 12:00:00 on the first day of the next fiscal
quarter and continues through the end of the
last day of the
n
th fiscal quarter.The fiscal year
is defined in the company profile atSetup
➤
Company Profile
➤
Fiscal Year.
NEXT_N_FISCAL_
QUARTERS:n
SELECT Id FROM Account WHERE
CreatedDate >
LAST_N_FISCAL_QUARTERS:6
Starts 12:00:00 on the first day of the last fiscal
quarter and continues through the end of the
last day of the previous
n
th fiscal quarter. The
fiscal year is defined in the company profile at
Setup
➤
Company Profile
➤
Fiscal Year.
LAST_N_FISCAL_
QUARTERS:n
SELECT Id FROM Opportunity WHERE
CloseDate = THIS_FISCAL_YEAR
Starts 12:00:00 on the first day of the current
fiscal year and continues through the end of
the last day of the fiscal year. The fiscal year is
THIS_FISCAL_YEAR
defined in the company profile at Setup
➤
Company Profile
➤
Fiscal Year.
SELECT Id FROM Opportunity WHERE
CloseDate > LAST_FISCAL_YEAR
Starts 12:00:00 on the first day of the last fiscal
year and continues through the end of the last
day of that fiscal year.The fiscal year is defined
LAST_FISCAL_YEAR
in the company profile at Setup
➤
Company
Profile
➤
Fiscal Year.
SELECT Id FROM Opportunity WHERE
CloseDate < NEXT_FISCAL_YEAR
Starts 12:00:00 on the first day of the next fiscal
year and continues through the end of the last
day of that fiscal year.The fiscal year is defined
NEXT_FISCAL_YEAR
in the company profile at Setup
➤
Company
Profile
➤
Fiscal Year.
SELECT Id FROM Opportunity WHERE
CloseDate < NEXT_N_FISCAL_YEARS:3
Starts 12:00:00 on the first day of the next fiscal
year and continues through the end of the last
day of the
n
th fiscal year. The fiscal year is
NEXT_N_FISCAL_YEARS:n
defined in the company profile at Setup
➤
Company Profile
➤
Fiscal Year.
SELECT Id FROM Opportunity WHERE
CloseDate > LAST_N_FISCAL_YEARS:3
Starts 12:00:00 on the first day of the last fiscal
year and continues through the end of the last
day of the previous
n
th fiscal year. The fiscal
LAST_N_FISCAL_YEARS:n
year is defined in the company profile at Setup
➤
Company Profile
➤
Fiscal Year.
Note: If you have defined Custom Fiscal Years in the Salesforce user interface, and in any of the
FISCAL
date literals
you specify a range that is outside the years you've defined, an invalid date error is returned.
370
query()
39
Minimum and Maximum Dates
Only dates within a certain range are valid.The earliest valid date is 1700-01-01T00:00:00Z GMT, or just after midnight on
January 1, 1700. The latest valid date is 4000-12-31T00:00:00Z GMT, or just after midnight on December 31, 4000.
Note: These values are offset by your timezone. For example, in the Pacific timezone, the earliest valid date is
1699-12-31T16:00:00, or 4:00 PM on December 31, 1699.
null
Use the value 'null’ to represent null values in SOQL queries.
For example, the following statement would return the account IDs of all events with a non-null activity date:
SELECT AccountId FROM Event WHERE ActivityDate !=null
Filtering on Boolean Fields
To filter on a Boolean field, use the following syntax:
where BooleanField = TRUE
where BooleanField = FALSE
Relationship Queries
Client applications need to be able to query for more than a single type of object at a time. SOQL provides syntax to support
these types of queries, called relationship queries, against both standard objects and custom objects.
Relationship queries traverse parent-to-child and child-to-parent relationships between objects to filter and return results.
They are similar to SQL joins. You cannot perform arbitrary SQL joins, however. The relationship queries in SOQL must
traverse a valid relationship path as defined in the rest of this section.
You can use relationship queries to search for objects of one type based on criteria that applies to objects of another type, for
example, "return all accounts created by Bob Jones and the contacts associated with those accounts." There must be a
parent-to-child or child-to-parent relationship connecting the objects. You can’t write arbitrary queries such as "return all
accounts and users created by Bob Jones."
Use the following topics to understand and use relationship queries in SOQL:
• Understanding Relationship Names
• Using Relationship Queries
• Understanding Relationship Names and Custom Objects and Custom Fields
• Understanding Query Results
• Lookup Relationships and Outer Joins
• Identifying Parent and Child Relationships
• Understanding Polymorphic Keys and Relationships
• Understanding Relationship Query Limitations
• Using Relationship Queries with History Objects
• Using Relationship Queries with the Partner WSDL
Understanding Relationship Names
Parent-to-child and child-to-parent relationships exist between many types of objects, for example, Account is a parent of
Contact.
371
query()
59
To be able to traverse these relationships for standard objects, a relationship name is given to each relationship.The form of
the name is different depending on the direction of the relationship:
• For child-to-parent relationships, the relationship name to the parent is the name of the foreign key, and there is a
relationshipName
property that holds the reference to the parent object. For example, the Contact child object has a
child-to-parent relationship to the Account object, so the value of
relationshipName
in Contact is
Account
.These
relationships are traversed by specifying the parent using dot notation in the query, for example:
SELECT Contact.FirstName, Contact.Account.Name from Contact
This query returns the first names of all the contacts in the organization, and for each contact, the account name associated
with (parent of) that contact.
• For parent-to-child relationships, the parent object has a name for the child relationship that is unique to the parent, the
plural of the child object name. For example, Account has child relationships to Assets, Cases, and Contacts among other
objects, and has a
relationshipName
for each,
Assets
,
Cases
, and
Contacts
.These relationships can be traversed
only in the
SELECT
clause, using a nested SOQL query. For example:
SELECT Account.Name, (SELECT Contact.FirstName, Contact.LastName FROM Account.Contacts)
FROM Account
This query returns all accounts, and for each account, the first and last name of each contact associated with (the child of)
that account.
Caution: You must use the correct naming convention and
SELECT
syntax for the direction of the relationship. For
information about how to discover relationship names via your organization's WSDL or
describeSObjects()
, see
Identifying Parent and Child Relationships. There are limitations on relationship queries depending on the direction
of the relationship. See Understanding Relationship Query Limitations for more information.
Relationship names are somewhat different for custom objects, though the
SELECT
syntax is the same. See Understanding
Relationship Names and Custom Objects and Custom Fields for more information.
Using Relationship Queries
You can query the following relationships using SOQL:
• Query child-to-parent relationships, which are often many-to-one. Specify these relationships directly in the
SELECT
,
FROM
, or
WHERE
clauses using the dot (
.
) operator.
For example:
SELECT Id, Name, Account.Name FROM Contact WHERE Account.Industry = ’media’
372
query()
49
This query returns the ID and name for only the contacts whose related account industry is media, and for each contact
returned, the account name.
• Query parent-to-child, which are almost always one-to-many. Specify these relationships using a subquery (enclosed in
parentheses), where the initial member of the
FROM
clause in the subquery is related to the initial member of the outer
query
FROM
clause. Note that in subqueries, you should specify the plural name of the object, as that is the name of the
relationship for each object.
For example:
SELECT Name, (SELECT LastName FROM Contacts) FROM Account
The following query returns the name for all the accounts, and for each account, the last name of each contact.
• Traverse the parent-to-child relationship as a foreign key in an aggregate query:
For example:
SELECT Name, (SELECT CreatedBy.Name FROM Notes) FROM Account
This query returns the accounts in an organization, and for each account, the name of the account, the notes for those
accounts (which can be an empty result set if there were no notes on any accounts) with the name of the user who created
each note (if the result set is not empty).
• In a similar example, traverse the parent-to-child relationship in an aggregate query:
SELECT Amount, Id, Name, (SELECT Quantity, ListPrice,
PricebookEntry.UnitPrice, PricebookEntry.Name
FROM OpportunityLineItems) FROM Opportunity
Using the same query, you can get the values on Product2 by specifying the product family (which points to the field's
data):
SELECT Amount, Id, Name, (SELECT Quantity, ListPrice,
PriceBookEntry.UnitPrice, PricebookEntry.Name,
PricebookEntry.product2.Family FROM OpportunityLineItems)
FROM Opportunity
•
WHERE
clauses can be placed on any query (including subqueries), and apply to the root element of the
FROM
clause of the
current query. These clauses can filter on any object in the current scope (reachable from the root element of the query),
via the parent relationships.
For example:
SELECT Name, (SELECT lastname FROM Contacts WHERE CreatedBy.Alias = 'x')
FROM Account WHERE industry = ’media’
This query returns the name for all accounts whose industry is media, and for each account returned, returns the last name
of every contact whose created by alias is 'x.'
Understanding Relationship Names and Custom Objects and Custom Fields
Custom objects can participate in relationship queries. Salesforce.com ensures that your custom object names, custom field
names, and the relationship names associated with them remain unique, even if a standard object with the same name is
available now or in the future. This is important in relationship queries, where the query traverses relationships using the
object, field, and relationship names.
This section explains how relationship names for custom objects and custom fields are created and used.
373
query()
21
When you create a new custom relationship in the Salesforce user interface, you are asked to specify the plural version of the
object name, which you use for relationship queries:
Notice that the
Child Relationship Name
(parent to child) is the plural form of the child object name, in this case
Daughters.
Once the relationship is created, notice that it has an
API Name
, which is the name of the custom field you created, appended
by
__c
(underscore-underscore-c):
When you refer to this field via the API, you must use this special form of the name.This prevents ambiguity in the case
where salesforce.com may create a standard object with the same name as your custom field.The same process applies to
custom objects—when they are created, they have an
API Name
, the object named appended by
__c
, which must be used.
374
query()
53
When you use a relationship name in a query, you must use the relationship names without the
__c
. Instead, append an
__r
(underscore underscore r).
For example:
• When you use a child-to-parent relationship, you can use dot notation:
SELECT Id, FirstName__c, Mother_of_Child__r.FirstName__c FROM Daughter__c WHERE
Mother_of_Child__r.LastName__c LIKE 'C%'
This query returns the ID and first name of daughter objects, and the first name of the daughter's mother if the mother's
last name begins with 'C.'
• Parent-to-child relationship queries do not use dot notation:
SELECT LastName__c, (SELECT LastName__c FROM Daughters__r) FROM Mother__c
The example above returns the last name of all mothers, and for each mother returned, the last name of the mother's
daughters.
Understanding Query Results
Query results are returned as nested objects.The primary or "driving" object of the main
SELECT
query contains query results
of subqueries.
For example, you can construct a query using either parent-to-child or child-to-parent syntax:
• Child-to-parent:
SELECT Id, FirstName, LastName, AccountId, Account.Name FROM Contact
WHERE Account.Name LIKE ’Acme%’
This query returns one query result (assuming there were not too many returned records), with a row for every contact that
met the
WHERE
clause criteria.
• Parent-to-child:
SELECT Id, Name, (SELECT Id, FirstName, LastName FROM Contacts) FROM Account
WHERE Name like ’Acme%’
This query returns a set of accounts, and within each account, a query result set of Contact fields containing the contact
information from the subquery.
Subquery results are like regular query results in that you may need to use
queryMore()
to retrieve all the records if there are
many children. For example, if you issue a query on accounts that includes a subquery, your client application must handle
results from the subquery as well:
1. Perform the query on Account.
2. Iterate over the account QueryResult with
queryMore()
.
3. For each account object, retrieve the contacts QueryResult.
4. Iterate over the child contacts, using
queryMore()
on each contact's QueryResult.
The following sample illustrates how to process subquery results:
private void querySample() {
QueryResult qr = null;
try {
qr = binding.query("SELECT a.Id, a.Name, (SELECT c.Id, c.firstname,
375
query()
62
c.lastname FROM a.Contacts c) FROM Account a");
boolean done = false;
if (qr.getSize() > 0) {
while (!done) {
for (int i = 0; i < qr.getRecords().length; i++) {
Account acct = (Account)qr.getRecords(i);
String name = acct.getName();
System.out.println("Account " + (i + 1) + ": " + name);
printContacts(acct.getContacts());
}
if (qr.isDone()) {
done = true;
} else {
qr = binding.queryMore(qr.getQueryLocator());
}
}
} else {
System.out.println("No records found.");
}
System.out.println("\nQuery succesfully executed.");
}
catch (RemoteException ex) {
System.out.println("\nFailed to execute query succesfully, error message
was: \n" + ex.getMessage());
}
}
private void printContacts(QueryResult qr) throws RemoteException {
boolean done = false;
if (qr.getSize() > 0) {
while (!done) {
for (int i = 0; i < qr.getRecords().length; i++) {
Contact contact = (Contact)qr.getRecords(i);
String fName = contact.getFirstName();
String lName = contact.getLastName();
System.out.println("Child contact " + (i + 1) + ": " + lName
+ ", " + fName);
}
if (qr.isDone()) {
done = true;
} else {
qr = binding.queryMore(qr.getQueryLocator());
}
}
} else {
System.out.println("No child contacts found.");
}
}
Lookup Relationships and Outer Joins
Beginning with Version 13.0 of the API, relationship queries return records even if the relevant foreign key field has a null
value, as you would expect with an outer join.The change in behavior applies to the following types of relationship queries:
• In an
ORDER BY
clause, if the foreign key value in a record is null, the record is returned in Version 13.0 and later, but not
returned in versions before 13.0. For example:
SELECT Id, CaseNumber, Account.Id, Account.Name FROM Case ORDER BY Account.Name
Any case record for which
AccountId
is empty is returned in Version 13.0 and later.
The following example uses custom objects:
SELECT ID, Name, Parent__r.id, Parent__r.name FROM Child__c ORDER BY Parent__r.name
376
query()
81
This query returns the
Id
and
Name
values of the Child object and the
Id
and name of the Parent object referenced in
each Child, and orders the results by the parent name. In version 13.0 and later, records are returned even if
Parent__r.id
or
Parent__r.name
are null. In earlier versions, such records are not returned by the query.
• In a WHERE clause using
OR
, if the foreign key value in a record is null, the record is returned in Version 13.0 and later,
but not returned in versions before 13.0. For example, if your organization has one contact with the value of its
LastName
field equal to
foo
and the value of its
AccountId
field equal to
null
, and another contact with a different last name and
a parent account named
bar
, the following query returns only the contact with the last name equal to
bar
:
SELECT Id FROM Contact WHERE LastName = 'foo' or Account.Name = 'bar'
The contact with no parent account has a last name that meets the criteria, so it is returned in version 13.0 and later.
• In a WHERE clause that checks for a value in a parent field, if the parent does not exist, the record is returned in Version
13.0 and later, but not returned in versions before 13.0.. For example:
SELECT Id FROM Case WHERE Contact.Lastname = null
Case record
Id
values are returned in version 13.0 and later, but are not returned in versions before 13.0.
Identifying Parent and Child Relationships
You can identify parent-child relationships by viewing the ERD diagrams in Data Model on page 77. However, not all
parent-child relationships are exposed in SOQL, so to be sure you can query on a parent-child relationship by issuing the
appropriate describe call.The results contain parent-child relationship information.
You can also examine the Enterprise WSDL for your organization:
• To find the names of child relationships, look for entries that contain the plural form of a child object and end with
type="tns:QueryResult"
. For example, from Account:
<complexType name="Account">
<complexContent>
<extension base="ens:sObject">
<sequence>
...
<element name="Contacts" nillable="true" minOccurs="0"
type="tns:QueryResult"/>
...
</sequence>
</extension>
</complexContent>
</complexType>
In the example above, the child relationship name
Contacts
is in the entry for its parent Account.
• For the parent of an object, look for a pair of entries such as
AccountId
and
Account
, where the ID field represents the
parent object referenced by the ID, and the other represents the contents of the record.The parent entry has a non-primitive
type,
type="ens:Account"
.
<complexType name="Opportunity">
<complexContent>
<extension base="ens:sObject">
<sequence>
...
<element name="Account" nillable="true" minOccurs="0"
type="ens:Account"/>
<element name="AccountId" nillable="true" minOccurs="0"
type="tns:ID"/>
...
377
query()
67
</sequence>
</extension>
</complexContent>
</complexType>
• For custom objects, look for a pair of entries with the relationship suffix
__r
:
<complextType name="Mother__c">
<complextcontent>
<extension base="ens:sObject">
<sequence>
...
<element name="Daughters__r" nillable="true" minOccurs="0"
type="tns:QueryResult"/>
<element name="FirstName__c" nillable="true" minOccurs="0"
type="xsd:string"/>
<element name="LastName__c" nillable="true" minOccurs="0"
type="xsd:string"/>
...
</sequence>
</extension>
</complexContent>
</complextType>
<complextType name="Daughter__c">
<complextcontent>
<extension base="ens:sObject">
<sequence>
...
<element name="Mother_of_Child__c" nillable="true" minOccurs="0"
type="tns:ID"/>
<element name="Mother_of_Child__r" nillable="true" minOccurs="0"
type="xsd:string"/>
<element name="LastName__c" nillable="true" minOccurs="0"
type="ens:Mother__c"/>
...
</sequence>
</extension>
</complexContent>
</complextType>
Understanding Polymorphic Keys and Relationships
A polymorphic key is an ID that can refer to more than one type of object as a parent. For example, either a contact or a lead
can be the parent of a task. In other words, the
WhoId
field of a task can contain the ID of either a contact or a lead. If an
object can have more than one type of object as a parent, the polymorphic key points to a Name object instead of a single
object type.
Executing a
describeSObjects()
call returns the Name object, whose field
Type
contains a list of the possible object types
that can parent the queried object. The
namePointing
field in the DescribeSObjectResult indicates that the relationship
points to the Name object, needed because the relationship is polymorphic. For example, the value in
WhoId
on Task can be
a contact or lead.
In order to traverse relationships where the object type of the parent is not known, you can use these fields to construct a query:
•
owner
:This field represents the object of a parent who owns the child object, regardless of the parent's object type. For
example:
SELECT Id, Owner.Name FROM Task WHERE Owner.FirstName like ’B%’
This example query works for tasks whose owners are either calendars or users.
378
query()
Documents you may be interested
Documents you may be interested
