Drills › Query the org (SOQL)

Load Account Contacts

Query the org (SOQL) · Intermediate · 15 min · Runs in your Salesforce Developer org · Apex Path

Scenario. An Account summary needs related Contact rows without issuing a second query for each parent.

Use a parent-to-child subquery to load two tagged Accounts with their Contacts, including an Account with none.

What you will learn

A parent-to-child subquery sits inside the parent's SELECT list. The child relationship name from Account is Contacts (plural). Read each row's children through that same name.

for (Account acct : [
    SELECT Id, (SELECT LastName FROM Contacts ORDER BY LastName)
    FROM Account
]) {
    Integer n = acct.Contacts.size();
    String first = n > 0 ? acct.Contacts[0].LastName : null;
}

Gotcha. No Contacts means an empty list — .size() is safe, [0] throws.

The task

Load two tagged Accounts and their Contacts with one query that uses a subquery.

Examples:

• the Busy Account → Busy Account contacts: 2
• the child rows sorted by LastName → Alpha before Beta → Contacts sorted: true
• the Empty Account → Empty Account contacts: 0

Requirements:

1. Query Account with Name LIKE :tagPrefix, and add a Contacts subquery to the SELECT list.
2. Sort the child rows by LastName, A to Z, inside the subquery.
3. Find each Account in the result by its Id (parents[0] is Busy, parents[1] is Empty).
4. Print the Account count, each Account's Contact count, and whether Alpha comes before Beta. Print Accounts loaded:, Busy Account contacts:, Empty Account contacts: and Contacts sorted:.

Starter code

String tag = 'AS-Q307-' + UserInfo.getUserId() + '-' +
    String.valueOf(Datetime.now().getTime());
String tagPrefix = tag + '%';
List<Account> parents = new List<Account>{
    new Account(Name = tag + '-Busy'),
    new Account(Name = tag + '-Empty')
};
insert parents;
insert new List<Contact>{
    new Contact(LastName = tag + '-Beta', AccountId = parents[0].Id),
    new Contact(LastName = tag + '-Alpha', AccountId = parents[0].Id)
};

Integer parentCount = 0;
Integer childCount = 0;
Integer emptyCount = -1;
Boolean childrenOrdered = false;
System.debug('Accounts loaded: ' + parentCount);
System.debug('Busy Account contacts: ' + childCount);
System.debug('Empty Account contacts: ' + emptyCount);
System.debug('Contacts sorted: ' + childrenOrdered);

When it passes

Both Accounts load. The Busy Account has two Contacts, Alpha first, and the Empty Account has an empty list.

Try this drill free

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

‹ Read a Contact's Account · Find Accounts by Contact ›