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. However, you cannot perform arbitrary SQL joins. 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 return 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

Parent-to-child and child-to-parent relationships exist between many types of objects, for example, Account is a parent of Contact.

A diagram of the parent-child relationship between Contact and Account objects

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:

Warning
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, Custom Objects, and Custom Fields for more information.

Using Relationship Queries

You can query the following relationships using SOQL:

Understanding Relationship Names, Custom Objects, and Custom Fields

Custom objects can participate in relationship queries. Salesforce 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.

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:

Step 3 of the New Custom Relationship wizard. The Field Label field is set to "Mother of Child" and the Child Relationship Name field is set to "Daughters."

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):

The detail page for the Mother of Child custom field

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 can 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.

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:

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:

Subquery results are like regular query results in that you might 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 = connection.query("SELECT a.Id, a.Name, " +
      "(SELECT c.Id, c.FirstName, " +
      "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 = connection.queryMore(qr.getQueryLocator());
        }
      }
    } else {
      System.out.println("No records found.");
    }
    System.out.println("\nQuery succesfully executed.");
  } catch (ConnectionException ce) {
    System.out.println("\nFailed to execute query successfully, error message " +
    "was: \n" + ce.getMessage());
  }
}

private void printContacts(QueryResult qr) throws ConnectionException {
  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 = connection.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:

Identifying Parent and Child Relationships

You can identify parent-child relationships by viewing the ERD diagrams in the Data Model section of the Salesforce Object Reference at www.salesforce.com/us/developer/docs/object_reference/index.htm. 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:

Understanding Polymorphic Keys and Relationships

In a polymorphic relationship, the referenced object of the relationship can be one of several different objects. For example, the What relationship field of an Event could be an Account, or a Campaign, or an Opportunity. When making queries or updating records with polymorphic relationships, you need to check the actual object type set for the relationship, and act accordingly. You can access polymorphic relationships several ways.
  • You can use the polymorphic key for the relationship.
  • You can use a TYPEOF clause in a query.
  • You can use the Type qualifier on a polymorphic field.
You can also combine these techniques for complex queries. Each of these techniques are described below.
Note
TYPEOF is currently available as a Developer Preview as part of the SOQL Polymorphism feature. For more information on enabling TYPEOF for your organization, contact salesforce.com.

Using Polymorphic Keys

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 the WhoId field in aTask record can be a Contact or Lead.
Note
If your organization has the SOQL Polymorphism feature enabled, polymorphic relationship fields reference sObjects, and not Names.

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 task records whose owners are either calendars or users.

  • who: This field represents the object type of the parent associated with the child:
    SELECT Id, Who.FirstName, Who.LastName
    FROM Task
    WHERE Owner.FirstName LIKE 'B%' 

    This example query works for task records whose owners can be either calendars or users, and whose “who” parent can be either contacts or leads.

    If you'd like to know the type of object returned in a query, use who.Type. For example:

    SELECT Id, Who.Id, Who.Type
    FROM Task

    Using this example, you could also query all the tasks associated with Contacts:

    SELECT Id, Who.Id, Who.Type
    FROM Task
    WHERE Who.Type='Contact'
  • what: This field represents the object type of a parent that is associated with the child where the object represents something other than a person (that is, not a contact, lead, or user):
    SELECT Id, What.Name
    FROM Event

    This example query works for events whose parent can be an account or a solution, or any of another number of object types.

You can also use describeSObjects() to obtain information about the parents and children of objects. For more information, see describeSObjects() and especially namePointing, which, if set to true, indicates the field points to a name.

Using TYPEOF

SOQL supports polymorphic relationships using the TYPEOF expression in a SELECT statement. TYPEOF is available in API version 26.0 and later.

Use TYPEOF in a SELECT statement to control which fields to query for each object type in a polymorphic relationship. The following SELECT statement returns a different set of fields depending on the object type associated with the What polymorphic relationship field in an Event.
SELECT 
  TYPEOF What
    WHEN Account THEN Phone, NumberOfEmployees
    WHEN Opportunity THEN Amount, CloseDate
    ELSE Name, Email
  END
FROM Event
At run time this SELECT statement checks the object type referenced by the What field in an Event. If the object type is Account, the referenced Account’s Phone and NumberOfEmployee fields are returned. If the object type is Opportunity, the referenced Opportunity’s Amount and CloseDate fields are returned. If the object type is any other type, the Name and Email fields are returned. Note that if an ELSE clause isn’t provided and the object type isn’t Account or Opportunity, then null is returned for that Event.

Note the following considerations for TYPEOF.

  • TYPEOF is only allowed in the SELECT clause of a query. You can filter on the object type of a polymorphic relationship using the Type qualifier in a WHERE clause, see Filtering on Polymorphic Relationship Fields for more details.
  • TYPEOF isn’t allowed in queries that don’t return objects, such as COUNT() and aggregate queries.
  • TYPEOF can’t be used in SOQL queries that are the basis of Streaming API PushTopics.
  • TYPEOF can’t be used in SOQL used in Bulk API.
  • TYPEOF expressions can’t be nested. For example, you can’t use TYPEOF inside the WHEN clause of another TYPEOF expression.
  • TYPEOF isn’t allowed in the SELECT clause of a semi-join query. You can use TYPEOF in the SELECT clause of an outer query that contains semi-join queries. The following example is not valid:
    SELECT Name FROM Account
    WHERE CreatedById IN
        (
        SELECT 
            TYPEOF Owner
                WHEN User THEN Id
                WHEN Group THEN CreatedById
            END
        FROM CASE
        )
    The following semi-join clause is valid because TYPEOF is only used in the outer SELECT clause:
    SELECT 
        TYPEOF What
            WHEN Account THEN Phone
            ELSE Name
        END
    FROM Event
    WHERE CreatedById IN
        (
        SELECT CreatedById
        FROM Case
        )
  • GROUP BY, GROUP BY ROLLUP, GROUP BY CUBE, and HAVING aren’t allowed in queries that use TYPEOF.

Using the Type qualifier

You can use the Type qualifier on a field to determine the object type that’s referenced in a polymorphic relationship. Use the Type qualifier in the WHERE clause of a SELECT statement to conditionally control what’s returned from the query depending on the referenced object type. The following SELECT statement uses Type to filter the query based on the What field in Event.
SELECT Id
FROM Event
WHERE What.Type IN ('Account', 'Opportunity')
At run time this SELECT statement returns the IDs for Events that reference Accounts or Opportunities in the What field. If an Event referenced a Campaign in the What field, it wouldn’t be returned as part of this SELECT. Unlike the TYPEOF expression, object types are returned as strings from Type. You can apply any WHEREcomparison operator to the object type strings, such as = (Equals) or LIKE.

Combining TYPEOF and Type

You can combine TYPEOF and Type in a SELECT statement. The following SELECT statement uses both TYPEOF and Type to filter the query and refine the set of returned fields based on the What field in Event.
SELECT Id,
  TYPEOF What
    WHEN Account THEN Phone
    WHEN Opportunity THEN Amount
  END
FROM Event
WHERE What.Type IN ('Account', 'Opportunity')
At run time this SELECT statement always returns the ID for an Event, and then either Account.Phone, or Opportunity.Amount, depending on the object type referenced by the Event’s What field. Note that no ELSE clause has been provided. Since this statement filters based on the What field in the WHERE clause, only Events that reference either an Account or Opportunity are returned, so the ELSE clause is not needed. If an ELSE clause was included in this case, it would be ignored at run time.

Understanding Relationship Query Limitations

When designing relationship queries, consider these limitations:

Using Relationship Queries with History Objects

Custom objects and some standard objects have an associated history object that tracks changes to an object record. You can use relationship queries to traverse a history object to its parent object. For example, the following query returns every history row for Foo__c and displays the name and custom fields of Foo:

SELECT OldValue, NewValue, Parent.Id, Parent.name, Parent.customfield__c 
FROM foo__history

This example query returns every Foo object row together with the corresponding history rows in nested subqueries:

SELECT Name, customfield__c, (SELECT OldValue, NewValue FROM foo__history) 
FROM foo__c

Using Relationship Queries with Data Category Selection Objects

Data categories are used to classify records. In SOQL, you can use the Article__DataCategorySelection or QuestionDataCategorySelection objects. You can also build a relationship query with the DataCategorySelections relationship name in a FROM clause.

Imagine an Offer article type. The following query returns the ID of any categorization associated with an offer and the ID of the categorized article.

SELECT Id,ParentId
FROM Offer__DataCategorySelection

The following example uses the DataCategorySelections relationship name to build a relationship query that returns the ID of published offers and the ID of all the categorizations associated to these offers.

SELECT Id, Title
  (
    SELECT Id
    FROM DataCategorySelections
  )
FROM Offer__kav WHERE publishStatus='online';

Using Relationship Queries with the Partner WSDL

The partner WSDL does not contain the detailed type information available in the enterprise WSDL to get the information you need for a relationship query. You must first execute a describeSObjects() call, and from the results, gather the information you need to create your relationship query:
  • The relationshipName value for one-to-many relationships, for example, in an Account object, the relationship name for the asset child is Assets.
  • The reference fields available for the relevant object, for example, whoId, whatId, or ownerId on a Lead, Case, or custom object.

For an example of using the partner WSDL with relationship queries, see the examples on developer.salesforce.com (requires login).

© Copyright 2000–2014 salesforce.com, inc. All rights reserved.
Various trademarks held by their respective owners.