Multi-level relationships with __r

SOQL queries · Intermediate · 14 min · Runs in your Salesforce Developer org · Apex Path

Read a Contact's Account owner in one query by walking the lookup fields in the SELECT. One query reaches `Account.Owner.Id` and `Account.Owner.Name`, with no extra round trips. The point is to see how standard lookups use `.` and custom lookups use `__r`.

Lesson

Why this matters

Most real work needs fields from more than one object: a Contact's Account's Owner, an Opportunity's Account's Industry, a custom Case's Partner's Region. If you query the first object and then query again for each lookup, you spend queries and risk governor limits. SOQL lets you walk the lookups in the SELECT, so one query returns the whole chain.

The smallest working example

List<Contact> rows = [
    SELECT Account.Name, Account.Owner.Name
    FROM Contact
    WHERE Id = :contact.Id
];
Contact row = rows[0];
String ownerName = row.Account.Owner.Name;

One query, two hops. row.Account.Owner.Name reads the chain back the same way.

Name the parts

**Account** on Contact is a standard lookup, so you dot-walk with ..
**Owner** on Account points at the User who owns the row; also a standard lookup, so still ..
A custom lookup field named Partner__c walks with Partner__r.Name (the __r is the relationship name).
The query returns real Contact rows; the dot path on each row mirrors the dot path in the SELECT.

A common wrong version

Contact row = [SELECT Id FROM Contact WHERE Id = :contact.Id];
Account parent = [SELECT Owner.Name FROM Account WHERE Id = :row.AccountId];
User owner = [SELECT Name FROM User WHERE Id = :parent.OwnerId];

Three queries for a chain that fits in one. In a loop that is one query per Contact per hop, which runs out of SOQL queries fast.

The fix

Name each hop you need in the SELECT:

List<Contact> rows = [
    SELECT Account.Name, Account.Owner.Id, Account.Owner.Name
    FROM Contact
    WHERE Id = :contact.Id
];
Contact row = rows[0];

Then read the fields back along the same path: row.Account.Name, row.Account.Owner.Id, row.Account.Owner.Name.

Now you try

The starter inserts an Account and a Contact owned by the running user, and gives you runningName. Run one SELECT that walks Account.Name, Account.Owner.Id and Account.Owner.Name, and print Account on contact:, Row count:, Owner id matches: and Owner matches: in that order.

Learn more

SOQL Reference: Relationship Queries

The challenge

Read the current user's name by walking from the Contact to its Account, then to that Account's owner.

Examples:

the Contact's Account Name → Account on contact: <tag>
row.Account.Owner.Id equals UserInfo.getUserId() → Owner id matches: true
row.Account.Owner.Name equals the user's Name → Owner matches: true

Requirements:

Account.Owner. should be present in the SELECT.
WHERE Id = :contact.Id should be present.
Owner id matches: true should be printed when the walked owner Id equals UserInfo.getUserId().
Owner matches: true should be printed when the walked owner Name equals runningName.
Row count: 1 should be printed from rows.size().
The Account name should be printed as Account on contact: AS-.

Starter code

String tag = 'AS-Q319-' + UserInfo.getUserId() + '-' +
    String.valueOf(Datetime.now().getTime());
Account acct = new Account(Name = tag);
insert acct;
Contact contact = new Contact(LastName = tag + '-C', AccountId = acct.Id);
insert contact;
String runningName = [SELECT Name FROM User WHERE Id = :UserInfo.getUserId()].Name;

System.debug('Account on contact: ');
System.debug('Row count: 0');
System.debug('Owner id matches: false');
System.debug('Owner matches: false');

Related drills

Try this drill free

You write the Apex yourself. ApexSensei runs it and tells you what passed and what did not.

‹ Group by a parent field · Paging with LIMIT and last-value ›