Querying Multiple Objects

The result of a SOQL query can be a simple list of records containing rows and columns
or hierarchies of records containing data from multiple, related objects. Relationships
between objects are navigated implicitly from the database structure.This eliminates the
work of writing accurate, efficient join conditions common to development on traditional
SQL databases.

The two ways to navigate object relationships in SOQL are child-to-parent and parent-
to-child. Listing 5-29 is an example of a child-to-parent query, returning the name,
city, and Force.com username creating its contact of all resources with a mailing address in
the state of Illinois. It selects and filters fields of the Contact object, the parent object of
Resource. It also selects the Name field from the User object, a parent two levels removed
from Resource via the Contact’s CreatedBy field.

Listing 5-29 SOQL with Child-to-Parent Relationship
SELECT Name, Contact__r.MailingCity, Contact__r.CreatedBy.Name
FROM Resource__c
WHERE Contact__r.MailingState = 'IL'

Note

The results of child-to-parent relationship queries are not completely rendered in the
Force.com IDE. You can double-click a row and column to view fields from a parent record,
but this is limited to direct parents only. Fields from parent-of-parent objects, such as the
Contact__r.CreatedBy relationship in Listing 5-29, are omitted from the results. This is a
limitation not of SOQL, but of the Force.com IDE.

At most, five levels of parent objects can be referenced in a single child-to-parent
query, and the query cannot reference more than 25 relationships in total.
The second form of relationship query is the parent-to-child query. Listing 5-30 provides
an example.The parent object is Resource, and the child is Timecard.The query
selects from every Resource its Id, Name, and a list of hours from its Timecards in the current
month.

Listing 5-30 SOQL with Parent-to-Child Relationship
SELECT Id, Name,
(SELECT Total_Hours__c
FROM Timecards__r
WHERE Week_Ending__c = THIS_MONTH)
FROM Resource__c

A parent-to-child query cannot reference more than twenty child objects. Doubleclicking
the parent record in the results table brings up the child records for viewing in
the Force.com IDE.