Erfassen Sie Ihre Fortschritte
Trailhead-Startseite
Trailhead-Startseite

Create Relationship Queries with Custom Objects

Learning Objectives

After completing this unit, you’ll be able to:

  • Interpret diagrams in the Schema Builder to identify object relationships.
  • Identify the custom relationship name to use in queries of custom objects.
  • Create a child-to-parent query for custom objects.
  • Create a parent-to-child query for custom objects.

The examples and hands-on activities in the rest of this module use a sample real estate application called DreamHouse. Let’s install the app in your Trailhead Playground and import sample data.

Launch Your Trailhead Playground and Install a Package

If it’s not already open, launch your Trailhead Playground. If you see a tab in your org labeled Install a Package, great! Skip ahead to step 1. If not, from the App Launcher (App Launcher), find and open Playground Starter and follow the steps. If you don’t see the Playground Starter app, copy this link to the Dreamhouse Package and check out Install a Package or App to Complete a Trailhead Challenge on Trailhead Help.

  1. Click the Install a Package tab.
  2. Paste 04tB00000009UeX into the field.
  3. Click Install.
  4. Select Install for Admins Only, then click Install.
  5. If prompted to approve third-party access, select Yes, grant access…  and then click Continue.

When the package is finished installing, you see a confirmation page and get an email to the address associated with your playground.

Import DreamHouse Data

  1. Click App Launcher, type Dream in the Search box, and then select the DreamHouse app.
  2. Click the Data Import tab. (If you don’t see the Data Import tab, expand the More list.)
  3. Click Initialize Sample Data.
  4. In the confirmation dialog box, click OK.
  5. Click the Properties tab, and familiarize yourself with the sample data.

Explore the DreamHouse Schema

Until now, we’ve queried only standard objects. Now we discuss how to query custom objects. We use objects in the DreamHouse app, so first let’s examine the DreamHouse schema (data model) to understand how the data is structured.

Open the Schema Builder

  1. In your Trailhead Playground, click Setup and select Setup.
  2. Click Object Manager.
  3. Click Schema Builder.
  4. In the Objects tab, click Clear All.
  5. If you see Broker rather than Broker__c, click View Options and then select Display Element Names.
  6. Remember that in code we use field API names. Display Element Names shows us field API names instead of field labels. (The API name for a custom object is the custom object name with __c [underscore underscore c] added at the end.)

  7. In the Select from list, choose Custom Objects.
  8. Select Broker__c and Property__c.

    These are the custom objects we use in this unit.

  9. Click Auto-Layout.
  10. Examine the property fields.
  11. The Property__c custom object has two custom fields: Picture__c and Broker__c. (The API name for a custom field is the custom field name with __c [underscore underscore c] added at the end.)

    The Schema Builder displaying the Broker__c and Property__c custom objects. On the Property__c custom object, the Broker__c and Picture__c custom fields are highlighted.

  12. Hover over the relationship line that connects Property__c to Broker__c.
  13. There is a lookup relationship from the Broker__c custom field on the Property__c custom object to the Broker__c custom object.

    The Broker__c and Property__c custom objects in the Schema Builder. The Broker__c custom field on the Property__c object has a lookup relationship from the Property__c custom object to the Broker__c custom object.

  14. On the Property__c element, click the gear icon and then select View Object.
    The Object Manager opens displaying the Property__c object.
  15. Click Fields & Relationships.
  16. Click the Broker field label.
  17. In the Lookup Options section, find the Child Relationship Name.

Now we know that Property__c is a child of Broker__c and that the name of the relationship between a property and a broker is Properties. (Remember, the relationship name is typically the plural form of the child object name.)

Query Custom Objects

Querying a custom object is a lot like querying a standard object but there are a few differences.

Consider this requirement: 

“Get a list of all properties with the property address, picture, and assigned broker.”

Pretty straightforward, right? We query the Property__c custom object, and get the Address__c, Picture__c, and Broker__c custom fields. Let’s try it.

Create a Query

  1. In the Query Editor, type:
  2. SELECT Address__c, Picture__c, Broker__c FROM Property__c
  3. Click Execute.
    The first six rows of your results should be:

  4. Query Results - Total Rows: 18
    Address__c
    Picture__c
    Broker__c
    18 Henry st
    https://s3-us-west-1.amazonaws.com...
    a016g000007Yxfn...
    121 Harborwalk
    https://s3-us-west-1.amazonaws.com...
    a016g000007Yxfn...
    95 Gloucester st
    https://s3-us-west-1.amazonaws.com...
    a016g000007Yxfn...
    30 Peterborough st
    https://s3-us-west-1.amazonaws.com...
    a016g000007Yxfn...
    24 Pearl st
    https://s3-us-west-1.amazonaws.com...
    a016g000007Yxfn...
    503 Park Drive
    https://s3-us-west-1.amazonaws.com...
    a016g000007Yxfn...

Notice that the contents of the Broker__c column are not very useful. That’s because the Broker__c field in the Property__c object is a lookup to the Broker object. Fields with lookup relationships contain the related object’s Id. To get the name of the Broker, we need a relationship query.

Create a Child-to-Parent Query

Because Broker__c is the parent of Property__c, we need a child-to-parent query. We use the relationship name and dot-notation to get the Broker__c object’s Name field.

  1. In the Query Editor, change Broker__c to Broker__c.Name, like this:
  2. SELECT Address__c, Picture__c, Broker__c.Name FROM Property__c
  3. Click Execute. (You get an error message.)
Error message
Didn’t understand relationship ‘Broker__c’ in field path. If you’re attempting to use a custom relationship, be sure to append the ‘__r’ after the custom relationship name.

Whoops! What happened? Remember that Broker__c is a custom field on the Property object. What we want is the Broker__c related object

Use the Custom Relationship Name in the Query

To specify the Broker__c related object (not the Broker__c field), we change __c to __r (underscore-underscore-r). Broker__r is the custom relationship name. It indicates that we want to traverse the relationship from Property__c to the Broker__c object. This portion of the query says, “Get the Name field from the related Broker__c custom object.” Let’s update our query.

  1. In the Query Editor, change Broker__c to Broker__r, like this:
  2. SELECT Address__c, Picture__c, Broker__r.Name FROM Property__c
  3. Click Execute.

    The first six rows of results should be:

  4. Query Results - Total Rows: 18
    Address__c
    Picture__c
    Broker__r.Name
    18 Henry st
    https://s3-us-west-1.amazonaws.com...
    Caroline Kingsley
    121 Harborwalk
    https://s3-us-west-1.amazonaws.com...
    Caroline Kingsley
    95 Gloucester st
    https://s3-us-west-1.amazonaws.com...
    Caroline Kingsley
    30 Peterborough st
    https://s3-us-west-1.amazonaws.com...
    Caroline Kingsley
    24 Pearl st
    https://s3-us-west-1.amazonaws.com...
    Michael Jones
    503 Park Drive
    https://s3-us-west-1.amazonaws.com...
    Michael Jones

Much better. Instead of the Id, now we have the Name field from the Broker custom object. 

Create a Parent-to-Child Query

We’ve successfully returned each property with its related broker. What if we want the opposite? 

Requirement:

“Get the names of all brokers with the address and price of all properties assigned to each broker.”

Because we want a list of brokers, in our main query, we query the Broker__c object:

SELECT Name FROM Broker__c

Broker is the parent so the parent-to-child query needs a subquery of the child object. We use the child relationship name in the subquery.

What’s the relationship name for a subquery of the Property__c object? Check the details for the Broker field on the Property object.

On the Details page for the Property object’s Broker field, under Lookup Options, the Child Relationship Name is Properties.

The Child Relationship Name is Properties. Because this is a custom relationship, when we use it in a query, we append __r (Properties__r). So our subquery (in parentheses) is:

(SELECT Address__c, Price__c FROM Properties__r)

When we insert the subquery into the main query, our full query is:

SELECT Name, (SELECT Address__c, Price__c FROM Properties__r) FROM Broker__c

Run the Query

  1. In the Query Editor, type:
  2. SELECT Name, (SELECT Address__c, Price__c FROM Properties__r) FROM Broker__c
  3. Click Execute.
    The first eight rows of your results should look like this:
  4. Query Results: Total Rows: 8. The first column lists broker names. The second column contains a comma-separated list of property addresses and prices.

Perfect! Now we have a list of all brokers and a comma separated list of each broker’s properties. When you’re developing queries, remember that the Developer Console’s Query Editor provides an easy way to test and tweak your SOQL queries.

Resources

Salesforce Developer Documentation: Relationship Names and Custom Objects 
Blog: SOQL How I Query with Thee Let Me Count the Ways