ApexSensei

Drills › Query the org (SOQL)

Query with a Set of Ids

Query the org (SOQL) · Beginner · 12 min · Runs in your Salesforce Developer org · Apex Path

Scenario. A trigger gets new Contacts that are not saved yet and needs their Accounts. It must use one query for all of them, not one query per Contact.

Collect Account Ids from a list of Contacts into a Set, then load those Accounts with one IN query.

What you will learn

IN matches any value in a List or Set. A common job pattern: collect Ids from records into a Set<Id>, then run one query for all of them.

Set<Id> accountIds = new Set<Id>();
for (Opportunity opp : deals) {
    accountIds.add(opp.AccountId);
}
List<Account> rows = [SELECT Id FROM Account WHERE Id IN :accountIds];

Gotcha. A Set keeps each value once, so two records on the same Account add one Id. Skip null Ids.

The task

Collect the Account Ids from a list of Contacts, then load those Accounts with one IN query.

Examples:

  • Contacts on A, C and C, plus one with no Account → Unique Account Ids: 2
  • the query returns A and C once each → Accounts found: 2
  • no Contact points to B → Account B left out: true, and one query in all → Queries used: 1

Requirements:

  1. Loop over contacts and add each AccountId that isn't null to accountIds. The Contacts are not inserted, so read AccountId straight from the list.
  2. After the loop, query Account once with WHERE Id IN :accountIds.
  3. Use the result Ids to check that B (accounts[1]) was not returned.
  4. Print Unique Account Ids:, Accounts found:, Account B left out: and Queries used: in that order.
  5. The last line, Queries used:, uses Limits.getQueries(). It must be exactly 1 — if your query is inside a loop, this count will be higher.

Starter code

String tag = 'AS-Q308-' + UserInfo.getUserId() + '-' +
    String.valueOf(Datetime.now().getTime());
List<Account> accounts = new List<Account>{
    new Account(Name = tag + '-A'),
    new Account(Name = tag + '-B'),
    new Account(Name = tag + '-C')
};
insert accounts;
List<Contact> contacts = new List<Contact>{
    new Contact(LastName = tag + '-1', AccountId = accounts[0].Id),
    new Contact(LastName = tag + '-2', AccountId = accounts[2].Id),
    new Contact(LastName = tag + '-3', AccountId = accounts[2].Id),
    new Contact(LastName = tag + '-4')
};

Set<Id> accountIds = new Set<Id>();
List<Account> matches = new List<Account>();
System.debug('Unique Account Ids: ' + accountIds.size());
System.debug('Accounts found: ' + matches.size());
System.debug('Account B left out: false');

When it passes

The Set holds two Ids (A and C). One IN query returns those two Accounts, and B is not returned.

Try this drill free

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

‹ Bind an Account name · Find missing Contact emails ›