Relationship & Aggregate SOQL, Subqueries, and Avoiding N+1 in Salesforce
Fast, reliable Apex leans on three pillars: relationship queries, aggregate SOQL, and thoughtful subqueries—while steering clear of N+1 patterns. Nail these and your code stays bulkified, selective, and governor-limit friendly.
Relationship SOQL (child → parent): follow lookups with dot notation
Concept: Pull fields from a parent record in the same query by walking the relationship path (e.g., Contact.Account.Name).
Real-world example: A contact list that also shows the Account name and Account Owner—no follow-up queries needed.
public with sharing class ContactRepo {
public static List<Contact> recentContacts(Integer limitSize) {
return [
SELECT Id, FirstName, LastName,
Account.Id, Account.Name,
Account.Owner.Id, Account.Owner.Name
FROM Contact
WHERE LastModifiedDate = LAST_N_DAYS:30
ORDER BY LastModifiedDate DESC
LIMIT :Math.min(limitSize, 200)
];
}
}
Why it’s good: No extra round trips for Account or Owner—SOQL returns everything at once.
Parent → child subqueries: return child collections in one go
Concept: Nest a subquery in the SELECT clause to fetch related child rows (e.g., an Account plus its Opportunities).
Real-world example: An Account detail view that previews the top Opportunities per Account.
public with sharing class AccountWithOppsRepo {
public static List<Account> topOppsPerAccount() {
return [
SELECT Id, Name,
(SELECT Id, Name, StageName, Amount
FROM Opportunities
WHERE IsClosed = false
ORDER BY Amount DESC
LIMIT 3)
FROM Account
WHERE Industry = 'Technology'
ORDER BY Name
LIMIT 100
];
}
}
Why it’s good: You get Accounts and a ready-to-use child list for each—no query-in-a-loop surprises.
Aggregate SOQL: summarize with GROUP BY / HAVING
Concept: Let the database do the math with COUNT, SUM, MIN, MAX, AVG, GROUP BY, and HAVING.
Real-world example: Show open-pipeline totals per Account and filter to accounts that actually matter.
public with sharing class PipelineSummary {
public class Row { public Id accountId; public Decimal total; public Integer deals; }
public static Map<Id, Row> openByAccount() {
Map<Id, Row> out = new Map<Id, Row>();
for (AggregateResult ar : [
SELECT AccountId a, SUM(Amount) s, COUNT(Id) c
FROM Opportunity
WHERE IsClosed = false
GROUP BY AccountId
HAVING SUM(Amount) > 10000
]) {
Row r = new Row();
r.accountId = (Id) ar.get('a');
r.total = (Decimal) ar.get('s');
r.deals = (Integer) ar.get('c');
out.put(r.accountId, r);
}
return out;
}
}
Why it’s good: One query delivers your rollups—no manual counters or per-account loops.
Semi-joins & anti-joins: filter sets efficiently
Concept: Use WHERE Id IN (SELECT …) or NOT IN (SELECT …) to filter by related records directly in SOQL—no Apex side joins.
Real-world example: Find Accounts with no open Opportunities for re-engagement campaigns.
public with sharing class AccountGaps {
public static List<Account> withoutOpenOpps() {
return [
SELECT Id, Name, OwnerId
FROM Account
WHERE Id NOT IN (
SELECT AccountId FROM Opportunity WHERE IsClosed = false
)
AND IsDeleted = false
LIMIT 500
];
}
}
The N+1 Problem: a common performance trap ?
N+1 happens when you query a list (the “1”) and then, inside a loop, run another query per record (the “+N”). That burns through SOQL limits and drags performance.
Bad (N+1): One query for Accounts, then a query for Contacts inside the loop.
Good (single query): Get Accounts with their Contacts via a subquery.
❌ Avoid this (N+1):
// 1 query to get the Accounts
List<Account> accounts = [SELECT Id, Name FROM Account WHERE Industry = 'Media'];
// N more queries inside the loop! (Very bad)
for (Account acc : accounts) {
// This query runs for every single account, hitting the 100 SOQL limit quickly.
List<Contact> contacts = [SELECT Id, Name FROM Contact WHERE AccountId = :acc.Id];
// ... process contacts
}
✅ Do this instead (one query):
// Only 1 query to get everything!
List<Account> accountsWithContacts = [
SELECT Name, (SELECT Id, Name FROM Contacts)
FROM Account
WHERE Industry = 'Media'
];
// No queries inside the loop
for (Account acc : accountsWithContacts) {
// The contacts are already in memory, attached to the account
List<Contact> contacts = acc.Contacts;
// ... process contacts efficiently
}
Concept recap to avoid N+1:
-
Collect needed IDs into a
Set<Id>. -
Query once with
WHERE Id IN :ids(or use relationship fields/subqueries). -
Put results in a
Mapand enrich in memory.
Real-world example: A trigger needs each Case’s parent Account Tier__c—without querying per Case.
public with sharing class CaseEnricher {
public static void beforeInsertOrUpdate(List<Case> cases) {
// 1) Collect parent AccountIds from Cases (via Contact or Account lookup)
Set<Id> accountIds = new Set<Id>();
for (Case c : cases) {
if (c.AccountId != null) accountIds.add(c.AccountId);
}
if (accountIds.isEmpty()) return;
// 2) Single query for needed fields
Map<Id, Account> acctById = new Map<Id, Account>([
SELECT Id, Name, Tier__c
FROM Account
WHERE Id IN :accountIds
]);
// 3) Enrich cases using the map
for (Case c : cases) {
Account a = acctById.get(c.AccountId);
if (a != null) c.SLA_Tier__c = a.Tier__c;
}
}
}
Why it’s good: One query serves the entire batch—bulk-safe, predictable, and fast.
Combine subqueries + aggregates: dashboard-ready data
Concept: Use aggregates for ranking/counts, then a parent→child subquery for on-screen details.
Real-world example: A dashboard card: “Top 10 Accounts by open pipeline” + the three biggest opps per Account.
public with sharing class TopAccountsService {
public class AccountCard {
public Account acct;
public Decimal openTotal;
public Integer openCount;
public List<Opportunity> topOpps;
}
public static List<AccountCard> build() {
// A) Aggregate totals first
List<AggregateResult> ar = [
SELECT AccountId a, SUM(Amount) s, COUNT(Id) c
FROM Opportunity
WHERE IsClosed = false
GROUP BY AccountId
ORDER BY SUM(Amount) DESC
LIMIT 10
];
Set<Id> acctIds = new Set<Id>();
Map<Id, AccountCard> cards = new Map<Id, AccountCard>();
for (AggregateResult r : ar) {
Id aid = (Id) r.get('a');
AccountCard card = new AccountCard();
card.openTotal = (Decimal) r.get('s');
card.openCount = (Integer) r.get('c');
cards.put(aid, card);
acctIds.add(aid);
}
// B) Pull account basics + top 3 opps via subquery
for (Account a : [
SELECT Id, Name, Industry,
(SELECT Id, Name, Amount, StageName
FROM Opportunities
WHERE IsClosed = false
ORDER BY Amount DESC
LIMIT 3)
FROM Account
WHERE Id IN :acctIds
]) {
AccountCard card = cards.get(a.Id);
card.acct = a;
card.topOpps = a.Opportunities; // child collection from subquery
}
return new List<AccountCard>(cards.values());
}
}
Why it’s good: Two queries power a rich card: one to rank, one to show detail—still bulk-safe.
Short summary
-
Use relationship SOQL to grab parent fields in one shot.
-
Use parent→child subqueries to bring back related lists.
-
Use aggregate SOQL for counts/totals on the server.
-
Prefer semi/anti-joins for set-based filtering.
-
Always avoid N+1: batch IDs, query once, and map in memory.
These patterns cut query counts, reduce governor pressure, and keep your Apex clean and fast.
