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

When you create a new custom relationship in the Database.com 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 Database.com 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 Widget__c records that includes a subquery, your client application must handle results from the subquery as well:
  1. Perform the query on Widget__c.
  2. Iterate over the Widget__c QueryResult with queryMore().
  3. For each Widget__c object, retrieve the Model__c QueryResult.
  4. Iterate over the child models, using queryMore() on each model’s QueryResult.

The following sample illustrates how to process subquery results:

private void querySample() {
  QueryResult qr = null;
  try {
    qr = connection.query("SELECT w.Id, w.Name, " +
      "(SELECT m.Id, m.Name FROM a.Models__r m) +
      "FROM Widget__c w");
    boolean done = false;
    if (qr.getSize() > 0) {
      while (!done) {
        for (int i = 0; i < qr.getRecords().length; i++) {
        Widget__c widget = (Widget__c) qr.getRecords()[i];
        String name = widget.getName();
        System.out.println("Widget " + (i + 1) + ": " + name);
        printModels(widget.getModels__r());
        }
        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 printModels(QueryResult qr) throws ConnectionException {
  boolean done = false;
  if (qr.getSize() > 0) {
    while (!done) {
    for (int i = 0; i < qr.getRecords().length; i++) {
      Model__c model = (Model__c) qr.getRecords()[i];
      String name = model.getName();
      System.out.println("Child model " + (i + 1) + ": " + name );
    }
    if (qr.isDone()) {
      done = true;
    } else {
      qr = connection.queryMore(qr.getQueryLocator());
    }
    }
  } else {
    System.out.println("No child records 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 Database.com 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 Owner relationship field of a Merchandise__c custom object could be a User or a Group. 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 User or a Group can be the owner of a custom object. In other words, the OwnerId field of a custom object can contain the ID of either a User or a Group. 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 OwnerId field in a Merchandise__c record can be a User or Group.
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 Merchandise__c
    WHERE Owner.Name like 'R%'

    This example query works for Merchandise__c records whose owners are either Users or Groups.

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 Owner polymorphic relationship field in a Merchandise__c record.
SELECT
  TYPEOF Owner
    WHEN User THEN FirstName, LastName
    WHEN Group THEN Name, Email
    ELSE Name
  END
FROM Merchandise__c
At run time this SELECT statement checks the object type referenced by the Owner field in a Merchandise__c. If the object type is User, the referenced User’s FirstName and LastName fields are returned. If the object type is Group, the referenced Group’s Name and Email fields are returned. If the object type is any other type, the Name field is returned. Note that if an ELSE clause isn’t provided and the object type isn’t User or Group,then null is returned for that Merchandise__c record.

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 Line_Item__c
    WHERE CreatedById IN
        (
        SELECT
            TYPEOF Owner
                WHEN User THEN Id
                WHEN Group THEN CreatedById
            END
        FROM Merchandise__c
        )
    The following semi-join clause is valid because TYPEOF is only used in the outer SELECT clause:
    SELECT
        TYPEOF Owner
            WHEN User THEN FirstName
            WHEN Group THEN Name
        END
    FROM Line_Item__c
    WHERE CreatedById IN
        (
        SELECT CreatedById
        FROM Merchandise__c
        )
  • GROUP BY 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 Owner field in Merchandise__c.
SELECT Description
FROM Merchandise__c
WHERE Owner.Type IN ('User', 'Group')
At run time this SELECT statement returns the descriptions for Merchandise__c records that reference Users or Groups in the Owner field. If a Merchandise__c references something other than a User or Group in the Owner 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 Owner field in Merchandise__c.
SELECT Id,
  TYPEOF Owner
    WHEN User THEN FirstName
    WHEN Group THEN Email
  END
FROM Merchandise__c
WHERE Owner.Type IN ('User', 'Group')
At run time this SELECT statement always returns the ID for a Merchandise__c, and then either User.FirstName, or Group.Email, depending on the object type referenced by the Merchandise__c record’s Owner field. Note that no ELSE clause has been provided. Since the statement filters based on the Owner field in the WHERE clause, only Merchandise__c records that reference either a User or Group 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 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 a Widget object, the relationship name for the Model child is Models__r.
  • The reference fields available for the relevant object.

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

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