Querying Soql

querying-soql: Salesforce SOQL Query Expert

Use this skill when the user needs SOQL/SOSL authoring or optimization: natural-language-to-query generation, relationship queries, aggregates, query-plan analysis, and performance/safety improvements for Salesforce queries.

When This Skill Owns the Task

Use querying-soql when the work involves:

• .soql files
• query generation from natural language
• relationship queries and aggregate queries
• query optimization and selectivity analysis
• SOQL/SOSL syntax and governor-aware design

Delegate elsewhere when the user is:

• performing bulk data operations → handling-sf-data
• embedding query logic inside broader Apex implementation → generating-apex
• debugging via logs rather than query shape → debugging-apex-logs

---

Required Context to Gather First

Ask for or infer:

• target object(s)
• fields needed
• filter criteria
• sort / limit requirements
• whether the query is for display, automation, reporting-like analysis, or Apex usage
• whether performance / selectivity is already a concern

---

Recommended Workflow

1. Generate the simplest correct query

Prefer:

• only needed fields
• clear WHERE criteria
• reasonable LIMIT when appropriate
• relationship depth only as deep as necessary

2. Choose the right query shape

3. Optimize for selectivity and safety

Check:

• indexed / selective filters
• no unnecessary fields
• no avoidable wildcard or scan-heavy patterns
• security enforcement expectations

4. Validate execution path if needed

If the user wants runtime verification, hand off execution to:

• handling-sf-data

---

High-Signal Rules

• never use SELECT * style thinking; query only required fields
• do not query inside loops in Apex contexts
• prefer filtering in SOQL rather than post-filtering in Apex
• use aggregates for counts and grouped summaries instead of loading unnecessary records
• evaluate wildcard usage carefully; leading wildcards often defeat indexes
• account for security mode / field access requirements when queries move into Apex

---

Output Format

When finishing, report in this order: 1. Query purpose 2. Final SOQL/SOSL 3. Why this shape was chosen 4. Optimization or security notes 5. Execution suggestion if needed

Suggested shape — use references/soql-syntax-reference.md for exact syntax:

Query goal: <summary>
Query: <soql or sosl>
Design: <relationship / aggregate / filter choices>
Notes: <selectivity, limits, security, governor awareness>
Next step: <run in handling-sf-data or embed in Apex>

---

Cross-Skill Integration

---

Score Guide

---

Reference File Index

Credits & Acknowledgments

This skill integrates concepts and patterns from the Salesforce developer community and official Salesforce documentation. The goal is to teach Apex developers how to write efficient, maintainable SOQL using vanilla Apex patterns that don't require external package installation.

---

Key Patterns Integrated

---

Philosophy

This skill integrates concepts and patterns from community resources, not specific libraries as dependencies. All patterns produce vanilla Apex code that works in any Salesforce org without additional package installation.

querying-soql

Salesforce SOQL query generation, optimization, and analysis skill with 100-point scoring. Convert natural language into performant SOQL and validate queries for security, selectivity, and governor-limit awareness.

Features

• Natural Language to SOQL: Convert requests into executable queries
• Query Optimization: Improve selectivity, LIMIT usage, and field selection
• Relationship Queries: Parent-child, child-parent, and polymorphic patterns
• Security Guidance: WITH USER_MODE, WITH SECURITY_ENFORCED, and Apex-safe usage
• 100-Point Scoring: Performance, correctness, security, and readability checks

Quick Start

1. Invoke the skill

Skill: querying-soql
Request: "Find Accounts with open Opportunities created this quarter"

2. Typical use cases

• Generate SOQL from plain-English requirements
• Optimize slow or non-selective queries
• Build aggregates and relationship queries
• Validate queries before using them in Apex or CLI workflows

Documentation

• SKILL.md - Core workflow and optimization guidance
• references/query-optimization.md - Selectivity and performance tuning
• references/soql-syntax-reference.md - Syntax, relationships, and aggregates
• references/selector-patterns.md - Reusable Apex selector patterns
• references/cli-commands.md - sf CLI query execution examples

Related Skills

• handling-sf-data - For data creation/import/export workflows
• generating-apex - For inline SOQL inside Apex code
• running-apex-tests - For validating query behavior in tests

<!-- Parent: querying-soql/SKILL.md -->

SOQL Anti-Patterns: What to Avoid

A catalog of common SOQL mistakes and their solutions. Avoiding these patterns will help you stay within governor limits, improve query performance, and write more maintainable code.

---

Anti-Pattern #1: SOQL Inside Loops

The Problem: Executing queries inside a loop quickly exhausts the 100 SOQL query limit.

// ❌ ANTI-PATTERN: Query per record
for (Contact c : Trigger.new) {
    Account a = [SELECT Name FROM Account WHERE Id = :c.AccountId];
    c.Account_Name__c = a.Name;
}
// 200 contacts = 200 queries = LIMIT EXCEEDED

The Solution: Query once, use a Map for lookups.

// ✅ CORRECT: Single query with Map lookup
Set<Id> accountIds = new Set<Id>();
for (Contact c : Trigger.new) {
    accountIds.add(c.AccountId);
}

Map<Id, Account> accountMap = new Map<Id, Account>(
    [SELECT Id, Name FROM Account WHERE Id IN :accountIds]
);

for (Contact c : Trigger.new) {
    Account a = accountMap.get(c.AccountId);
    if (a != null) {
        c.Account_Name__c = a.Name;
    }
}
// 200 contacts = 1 query = SAFE

Key Insight: Collect IDs first, query once with IN clause, then use Map for O(1) lookups.

---

Anti-Pattern #2: Non-Selective WHERE Clauses

The Problem: Queries on non-indexed fields cause full table scans, which fail on large objects (100k+ records).

// ❌ ANTI-PATTERN: Non-selective filter
SELECT Id FROM Lead WHERE Status = 'Open'
// Status is not indexed - scans ALL Lead records

The Solution: Add an indexed field to make the query selective.

// ✅ CORRECT: Add indexed field filter
SELECT Id FROM Lead
WHERE Status = 'Open'
AND CreatedDate = LAST_N_DAYS:30
// CreatedDate is indexed - uses index

// ✅ ALTERNATIVE: Use OwnerId (indexed)
SELECT Id FROM Lead
WHERE Status = 'Open'
AND OwnerId = :UserInfo.getUserId()

Indexed Fields (Always use these in WHERE):

• Id, Name, OwnerId, CreatedDate, LastModifiedDate
• RecordTypeId, External ID fields, Master-Detail fields
• Standard indexed fields: Account.AccountNumber, Contact.Email, Case.CaseNumber

---

Anti-Pattern #3: Leading Wildcards

The Problem: LIKE '%value' cannot use indexes and scans all records.

// ❌ ANTI-PATTERN: Leading wildcard
SELECT Id FROM Account WHERE Name LIKE '%Corporation'
// Cannot use index - full table scan

The Solution: Use trailing wildcards or exact matches.

// ✅ CORRECT: Trailing wildcard (uses index)
SELECT Id FROM Account WHERE Name LIKE 'Acme%'

// ✅ CORRECT: Exact match
SELECT Id FROM Account WHERE Name = 'Acme Corporation'

// ✅ CORRECT: Contains check (if absolutely necessary)
// Do the filtering in Apex after a selective query
List<Account> allAccounts = [
    SELECT Id, Name FROM Account
    WHERE CreatedDate = THIS_YEAR
];
List<Account> filtered = new List<Account>();
for (Account a : allAccounts) {
    if (a.Name.contains('Corporation')) {
        filtered.add(a);
    }
}

---

Anti-Pattern #4: Negative Operators

The Problem: !=, NOT IN, NOT LIKE often prevent index usage.

// ❌ ANTI-PATTERN: Negative operators
SELECT Id FROM Opportunity WHERE StageName != 'Closed Lost'
SELECT Id FROM Contact WHERE AccountId NOT IN :excludedIds

The Solution: Query for what you want, not what you don't want.

// ✅ CORRECT: Positive filter with specific values
SELECT Id FROM Opportunity
WHERE StageName IN ('Prospecting', 'Qualification', 'Proposal', 'Negotiation')

// ✅ CORRECT: Use a formula field for complex exclusions
// Create IsExcluded__c formula, then:
SELECT Id FROM Contact WHERE IsExcluded__c = false

---

Anti-Pattern #5: Querying for NULL

The Problem: WHERE Field = null is non-selective and scans all records.

// ❌ ANTI-PATTERN: Null check in WHERE
SELECT Id FROM Contact WHERE Email = null
// Non-selective - scans all contacts

The Solution: Combine with selective filters or redesign data model.

// ✅ CORRECT: Add selective filter
SELECT Id FROM Contact
WHERE Email = null
AND CreatedDate = LAST_N_DAYS:30

// ✅ BETTER: Use a checkbox field
// Create HasEmail__c formula checkbox
SELECT Id FROM Contact WHERE HasEmail__c = false

---

Anti-Pattern #6: SELECT * (All Fields)

The Problem: Querying all fields wastes resources and can hit heap limits.

// ❌ ANTI-PATTERN: Selecting everything
SELECT FIELDS(ALL) FROM Account LIMIT 200
// Loads ALL fields into memory

// ❌ ANTI-PATTERN: Listing every field manually
SELECT Id, Name, Description, BillingStreet, BillingCity,
       BillingState, BillingPostalCode, BillingCountry, ...
FROM Account

The Solution: Query only the fields you need.

// ✅ CORRECT: Minimal field selection
SELECT Id, Name, Industry FROM Account

// ✅ FOR DISPLAY: Just display fields
SELECT Id, Name FROM Account

// ✅ FOR PROCESSING: Just processing fields
SELECT Id, Status__c, ProcessedDate__c FROM Account

---

Anti-Pattern #7: No LIMIT on Queries

The Problem: Unbounded queries can return 50,000 records and consume heap memory.

// ❌ ANTI-PATTERN: No limit
SELECT Id, Name FROM Account
// Could return 50,000 records!

// ❌ ANTI-PATTERN: Excessive limit
SELECT Id, Name FROM Account LIMIT 50000

The Solution: Use appropriate limits for your use case.

// ✅ CORRECT: Reasonable limit for UI display
SELECT Id, Name FROM Account LIMIT 200

// ✅ CORRECT: Pagination
SELECT Id, Name FROM Account
ORDER BY Name
LIMIT 50 OFFSET 0

// ✅ CORRECT: Single record lookup
SELECT Id, Name FROM Account WHERE Name = 'Acme' LIMIT 1

// ✅ CORRECT: Existence check
SELECT Id FROM Account WHERE Name = 'Acme' LIMIT 1
// In Apex: if (!results.isEmpty()) { /* exists */ }

---

Anti-Pattern #8: Deep Relationship Traversal

The Problem: Deep nesting (>3 levels) hurts performance and readability.

// ❌ ANTI-PATTERN: Deep traversal
SELECT Id,
       Account.Owner.Manager.Department.Name
FROM Contact
// 4 levels deep - hard to maintain, performance hit

The Solution: Flatten queries or use multiple queries.

// ✅ CORRECT: Flatten to 1-2 levels
SELECT Id, Account.Name, Account.OwnerId FROM Contact

// Then query Owner separately if needed
Map<Id, User> owners = new Map<Id, User>(
    [SELECT Id, ManagerId FROM User WHERE Id IN :ownerIds]
);

---

Anti-Pattern #9: Unfiltered Subqueries

The Problem: Child subqueries without filters can return massive datasets.

// ❌ ANTI-PATTERN: Unfiltered subquery
SELECT Id,
       (SELECT Id FROM Contacts),
       (SELECT Id FROM Opportunities)
FROM Account
// Could return thousands of child records per account

The Solution: Always filter and limit subqueries.

// ✅ CORRECT: Filtered and limited subqueries
SELECT Id,
       (SELECT Id, Name FROM Contacts
        WHERE IsActive__c = true
        LIMIT 5),
       (SELECT Id, Name FROM Opportunities
        WHERE StageName != 'Closed Lost'
        LIMIT 5)
FROM Account
WHERE Industry = 'Technology'

---

Anti-Pattern #10: Formula Fields in WHERE

The Problem: Formula fields are not indexed and require full table scans.

// ❌ ANTI-PATTERN: Filter on formula field
SELECT Id FROM Opportunity
WHERE Days_Since_Created__c > 30
// Formula field - cannot use index

The Solution: Use the underlying indexed field.

// ✅ CORRECT: Use base field
SELECT Id FROM Opportunity
WHERE CreatedDate < LAST_N_DAYS:30

// ✅ ALTERNATIVE: Store computed value in regular field
// Use workflow/flow to update a Number field
SELECT Id FROM Opportunity
WHERE Days_Open__c > 30

---

Quick Reference: Selectivity Rules

A filter is SELECTIVE when:
├── Uses an indexed field, AND
├── Returns < 10% of first million records, OR
├── Returns < 5% of records beyond first million
└── Absolute max: 333,333 records (1M / 3)

Always Indexed Fields:

• Id, Name, OwnerId, CreatedDate, LastModifiedDate
• RecordTypeId, External ID fields, Master-Detail relationship fields

Request Custom Index: Contact Salesforce Support with:

• Object name and field API name
• Sample SOQL query
• Cardinality (unique values count)
• Business justification

---

Testing Checklist

Before deploying SOQL to production:

1. [ ] Run Query Plan tool (Developer Console or CLI) 2. [ ] Verify LeadingOperationType is "Index" not "TableScan" 3. [ ] Test with 200+ records in trigger context 4. [ ] Verify query count stays under 100 per transaction 5. [ ] Check heap usage for large result sets

# CLI Query Plan
sf data query \
  --query "SELECT Id FROM Account WHERE Name = 'Test'" \
  --target-org my-org \
  --use-tooling-api \
  --plan

<!-- Parent: querying-soql/SKILL.md -->

Salesforce CLI SOQL Commands

Quick Reference

---

Basic Queries

Run a Query

sf data query \
  --query "SELECT Id, Name, Industry FROM Account LIMIT 10" \
  --target-org my-sandbox

Query with Filters

sf data query \
  --query "SELECT Id, Name FROM Account WHERE Industry = 'Technology'" \
  --target-org my-sandbox

Query Relationships

# Child-to-parent
sf data query \
  --query "SELECT Id, Name, Account.Name FROM Contact LIMIT 10" \
  --target-org my-sandbox

# Parent-to-child
sf data query \
  --query "SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account LIMIT 5" \
  --target-org my-sandbox

---

Output Formats

Human-Readable (Default)

sf data query \
  --query "SELECT Id, Name FROM Account LIMIT 5" \
  --target-org my-sandbox

JSON

sf data query \
  --query "SELECT Id, Name FROM Account LIMIT 5" \
  --target-org my-sandbox \
  --json

CSV

sf data query \
  --query "SELECT Id, Name, Industry FROM Account" \
  --target-org my-sandbox \
  --result-format csv > accounts.csv

Direct to File

sf data query \
  --query "SELECT Id, Name, Industry FROM Account" \
  --target-org my-sandbox \
  --result-format csv \
  --output-file accounts.csv

---

Bulk Data Export

For large result sets (> 2,000 records), use the dedicated bulk export command:

# Export to CSV (default)
sf data export bulk \
  --query "SELECT Id, Name FROM Account" \
  --target-org my-sandbox \
  --output-file accounts.csv

# Export as JSON
sf data export bulk \
  --query "SELECT Id, Name FROM Account" \
  --target-org my-sandbox \
  --output-file accounts.json \
  --result-format json

Note: --bulk and --wait flags on sf data query were removed in v2.87.7. Use sf data export bulk instead.

---

Query Plan Analysis

Analyze query performance before running:

sf data query \
  --query "SELECT Id FROM Account WHERE Name = 'Acme'" \
  --target-org my-sandbox \
  --use-tooling-api \
  --plan

Understanding Query Plan Output

{
  "plans": [{
    "cardinality": 50,           // Estimated rows returned
    "fields": ["Name"],          // Fields used for filtering
    "leadingOperationType": "Index",  // Index = good, TableScan = bad
    "relativeCost": 0.1,         // Lower is better
    "sobjectCardinality": 10000, // Total records in object
    "sobjectType": "Account"
  }]
}

Key Indicators:

• leadingOperationType: "Index" = Query uses index (good)
• leadingOperationType: "TableScan" = Full table scan (bad for large tables)
• relativeCost < 1 = Efficient query
• cardinality = Expected number of results

---

Tooling API Queries

Query metadata objects:

# Query ApexClass
sf data query \
  --query "SELECT Id, Name, Body FROM ApexClass WHERE Name = 'MyController'" \
  --target-org my-sandbox \
  --use-tooling-api

# Query CustomField
sf data query \
  --query "SELECT Id, DeveloperName, TableEnumOrId FROM CustomField WHERE TableEnumOrId = 'Account'" \
  --target-org my-sandbox \
  --use-tooling-api

---

Query from File

Store query in file and execute:

# Create query file
echo "SELECT Id, Name FROM Account WHERE Industry = 'Technology'" > query.soql

# Execute from file
sf data query \
  --file query.soql \
  --target-org my-sandbox

---

Useful Patterns

Get Record Count

sf data query \
  --query "SELECT COUNT() FROM Account" \
  --target-org my-sandbox

Export to File

# CSV export
sf data query \
  --query "SELECT Id, Name, Industry, Phone FROM Account" \
  --target-org my-sandbox \
  --result-format csv > accounts.csv

# JSON export
sf data query \
  --query "SELECT Id, Name, Industry FROM Account" \
  --target-org my-sandbox \
  --json > accounts.json

Query with jq Processing

# Get just the names
sf data query \
  --query "SELECT Name FROM Account LIMIT 10" \
  --target-org my-sandbox \
  --json | jq -r '.result.records[].Name'

# Count records
sf data query \
  --query "SELECT Id FROM Account" \
  --target-org my-sandbox \
  --json | jq '.result.totalSize'

# Filter in shell
sf data query \
  --query "SELECT Id, Name, Industry FROM Account" \
  --target-org my-sandbox \
  --json | jq '.result.records[] | select(.Industry == "Technology")'

Query with Dates

# Records created today
sf data query \
  --query "SELECT Id, Name FROM Account WHERE CreatedDate = TODAY" \
  --target-org my-sandbox

# Records from last 30 days
sf data query \
  --query "SELECT Id, Name FROM Account WHERE CreatedDate = LAST_N_DAYS:30" \
  --target-org my-sandbox

Aggregate Queries

# Count by industry
sf data query \
  --query "SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry" \
  --target-org my-sandbox

# Sum of amounts
sf data query \
  --query "SELECT SUM(Amount) FROM Opportunity WHERE StageName = 'Closed Won'" \
  --target-org my-sandbox

---

Troubleshooting

Query Timeout

For long-running queries, export via bulk API:

sf data export bulk \
  --query "SELECT Id, Name FROM Account" \
  --target-org my-sandbox \
  --output-file results.csv

Too Many Results

Add LIMIT or filter:

# With limit
sf data query \
  --query "SELECT Id, Name FROM Account LIMIT 1000" \
  --target-org my-sandbox

# With filter
sf data query \
  --query "SELECT Id, Name FROM Account WHERE CreatedDate = THIS_YEAR" \
  --target-org my-sandbox

Non-Selective Query Error

Add indexed field to WHERE:

# Add CreatedDate filter (indexed)
sf data query \
  --query "SELECT Id FROM Lead WHERE Status = 'Open' AND CreatedDate = LAST_N_DAYS:90" \
  --target-org my-sandbox

Permission Errors

Check field-level security:

# Query accessible fields only
sf data query \
  --query "SELECT Id, Name FROM Account" \
  --target-org my-sandbox

---

Integration with Other Tools

Pipe to File

sf data query \
  --query "SELECT Id, Name FROM Account" \
  --target-org my-sandbox \
  --result-format csv | tee accounts.csv

Use in Scripts

#!/bin/bash

ORG=${1:-"my-sandbox"}

# Get count
COUNT=$(sf data query \
  --query "SELECT COUNT() FROM Account" \
  --target-org $ORG \
  --json | jq -r '.result.totalSize')

echo "Total accounts: $COUNT"

# Get top accounts
sf data query \
  --query "SELECT Name, AnnualRevenue FROM Account ORDER BY AnnualRevenue DESC LIMIT 10" \
  --target-org $ORG

Compare Orgs

#!/bin/bash

PROD_COUNT=$(sf data query --query "SELECT COUNT() FROM Account" --target-org prod --json | jq '.result.totalSize')
SANDBOX_COUNT=$(sf data query --query "SELECT COUNT() FROM Account" --target-org sandbox --json | jq '.result.totalSize')

echo "Production accounts: $PROD_COUNT"
echo "Sandbox accounts: $SANDBOX_COUNT"
echo "Difference: $((PROD_COUNT - SANDBOX_COUNT))"

<!-- Parent: querying-soql/SKILL.md -->

SOQL Field Coverage Rules

This guide documents field coverage validation rules for SOQL queries — ensuring that all fields accessed in Apex code are actually queried. This is a common source of runtime errors, especially in AI-assisted code generation.

---

Table of Contents

1. The Field Coverage Problem 2. Direct Field Access 3. Relationship Field Access 4. Dynamic Field Access 5. Aggregate Queries 6. Subquery Fields 7. Validation Patterns

---

The Field Coverage Problem

When you query an sObject, only the fields in the SELECT clause are populated. Accessing any other field results in a runtime error:

System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Account.Industry

This error is particularly common in LLM-generated code because the LLM may: 1. Query some fields but access others in subsequent code 2. Forget to include relationship fields (e.g., Account.Name on Contact) 3. Access fields in conditional logic that weren't anticipated in the query

---

Direct Field Access

Rule: Every field accessed must be in the SELECT clause

❌ BAD: Accessing Unqueried Fields

// Query only includes Id and Name
List<Account> accounts = [SELECT Id, Name FROM Account];

for (Account acc : accounts) {
    // RUNTIME ERROR: Industry was not queried
    if (acc.Industry == 'Technology') {
        // RUNTIME ERROR: Description was not queried
        acc.Description = 'Tech company';
    }

    // RUNTIME ERROR: AnnualRevenue was not queried
    Decimal revenue = acc.AnnualRevenue;
}

✅ GOOD: Query All Accessed Fields

// Query ALL fields that will be accessed
List<Account> accounts = [
    SELECT Id, Name, Industry, Description, AnnualRevenue
    FROM Account
];

for (Account acc : accounts) {
    if (acc.Industry == 'Technology') {
        acc.Description = 'Tech company';  // OK - queried
    }
    Decimal revenue = acc.AnnualRevenue;  // OK - queried
}

Field Access Locations to Check

Fields can be accessed in many places—ensure coverage for all:

---

Relationship Field Access

Rule: Parent relationship fields require dot notation in SELECT

❌ BAD: Missing Relationship Fields

// Contact query without Account relationship fields
List<Contact> contacts = [SELECT Id, Name, AccountId FROM Contact];

for (Contact c : contacts) {
    // RUNTIME ERROR: Account.Name was not queried
    String accountName = c.Account.Name;

    // RUNTIME ERROR: Account.Industry was not queried
    if (c.Account.Industry == 'Technology') {
        // ...
    }
}

✅ GOOD: Include Relationship Fields

// Use dot notation to include parent fields
List<Contact> contacts = [
    SELECT Id, Name, AccountId,
           Account.Name,           // Parent field
           Account.Industry,       // Parent field
           Account.Owner.Name      // Grandparent field (up to 5 levels)
    FROM Contact
];

for (Contact c : contacts) {
    String accountName = c.Account.Name;  // OK - queried

    if (c.Account.Industry == 'Technology') {  // OK - queried
        String ownerName = c.Account.Owner.Name;  // OK - queried
    }
}

Relationship Traversal Limits

❌ BAD: Assuming Relationship is Populated

List<Contact> contacts = [SELECT Id, AccountId FROM Contact];

for (Contact c : contacts) {
    // AccountId is queried, but Account object is NOT populated
    // This will throw: Account.Name not queried
    if (c.Account != null) {
        String name = c.Account.Name;  // ERROR!
    }
}

✅ GOOD: Query Relationship or Use Separate Query

// Option 1: Include relationship field
List<Contact> contacts = [SELECT Id, AccountId, Account.Name FROM Contact];

for (Contact c : contacts) {
    if (c.Account != null) {
        String name = c.Account.Name;  // OK
    }
}

// Option 2: Separate query using collected IDs
List<Contact> contacts = [SELECT Id, AccountId FROM Contact];
Set<Id> accountIds = new Set<Id>();
for (Contact c : contacts) {
    if (c.AccountId != null) {
        accountIds.add(c.AccountId);
    }
}

Map<Id, Account> accountMap = new Map<Id, Account>(
    [SELECT Id, Name FROM Account WHERE Id IN :accountIds]
);

for (Contact c : contacts) {
    Account acc = accountMap.get(c.AccountId);
    if (acc != null) {
        String name = acc.Name;  // OK
    }
}

---

Dynamic Field Access

Rule: Dynamic field access (using get()) also requires queried fields

❌ BAD: Dynamic Access to Unqueried Field

List<Account> accounts = [SELECT Id, Name FROM Account];
String fieldName = 'Industry';  // Dynamic field name

for (Account acc : accounts) {
    // RUNTIME ERROR: Industry was not queried
    Object value = acc.get(fieldName);
}

✅ GOOD: Query Fields Used Dynamically

// If you know which fields will be accessed dynamically, query them
List<Account> accounts = [SELECT Id, Name, Industry FROM Account];
String fieldName = 'Industry';

for (Account acc : accounts) {
    Object value = acc.get(fieldName);  // OK - Industry is queried
}

✅ GOOD: Build Dynamic Query

// For truly dynamic scenarios, build the query dynamically
Set<String> fieldsToQuery = new Set<String>{'Id', 'Name'};
fieldsToQuery.addAll(dynamicFieldList);  // Add dynamic fields

String query = 'SELECT ' + String.join(new List<String>(fieldsToQuery), ', ') +
               ' FROM Account WHERE Id IN :accountIds';

List<Account> accounts = Database.query(query);

---

Aggregate Queries

Rule: Aggregate queries return AggregateResult, not sObjects

❌ BAD: Treating Aggregate as sObject

// This returns AggregateResult, not Account
List<Account> accounts = [
    SELECT Industry, COUNT(Id) cnt
    FROM Account
    GROUP BY Industry
];  // COMPILE ERROR - wrong type

// Even with correct type, can't access normal fields
AggregateResult[] results = [
    SELECT Industry, COUNT(Id) cnt
    FROM Account
    GROUP BY Industry
];

for (AggregateResult ar : results) {
    // Cannot access like sObject fields
    String industry = ar.Industry;  // COMPILE ERROR
}

✅ GOOD: Use get() for Aggregate Results

AggregateResult[] results = [
    SELECT Industry, COUNT(Id) cnt, SUM(AnnualRevenue) totalRevenue
    FROM Account
    GROUP BY Industry
];

for (AggregateResult ar : results) {
    // Use get() with field alias
    String industry = (String) ar.get('Industry');
    Integer count = (Integer) ar.get('cnt');
    Decimal totalRevenue = (Decimal) ar.get('totalRevenue');

    System.debug(industry + ': ' + count + ' accounts, $' + totalRevenue);
}

Aggregate Field Aliases

---

Subquery Fields

Rule: Child relationship subqueries create nested lists

❌ BAD: Accessing Subquery Fields Incorrectly

// Query with contact subquery
List<Account> accounts = [
    SELECT Id, Name,
           (SELECT Id, Name FROM Contacts)
    FROM Account
];

for (Account acc : accounts) {
    // ERROR: Contacts is a List, not a single Contact
    String contactName = acc.Contacts.Name;

    // ERROR: Cannot access unqueried field from subquery
    for (Contact c : acc.Contacts) {
        String email = c.Email;  // Email not in subquery SELECT!
    }
}

✅ GOOD: Proper Subquery Field Access

// Query all needed fields in subquery
List<Account> accounts = [
    SELECT Id, Name,
           (SELECT Id, Name, Email, Phone FROM Contacts)
    FROM Account
];

for (Account acc : accounts) {
    // Contacts is a List<Contact>
    List<Contact> contacts = acc.Contacts;

    if (contacts != null && !contacts.isEmpty()) {
        for (Contact c : contacts) {
            String name = c.Name;    // OK - in subquery SELECT
            String email = c.Email;  // OK - in subquery SELECT
            String phone = c.Phone;  // OK - in subquery SELECT
        }
    }
}

Subquery Null Safety

List<Account> accounts = [
    SELECT Id, (SELECT Id FROM Contacts)
    FROM Account
];

for (Account acc : accounts) {
    // Subquery result can be null if no child records
    if (acc.Contacts != null) {
        for (Contact c : acc.Contacts) {
            // Process contact
        }
    }

    // Or use null-safe size check
    Integer contactCount = acc.Contacts?.size() ?? 0;
}

---

Validation Patterns

Pattern 1: Field-to-Query Mapping

Create a systematic approach to track field usage:

public class AccountProcessor {
    // Document required fields at the top
    private static final Set<String> REQUIRED_FIELDS = new Set<String>{
        'Id', 'Name', 'Industry', 'Description', 'AnnualRevenue',
        'OwnerId', 'Owner.Name', 'Owner.Email'
    };

    // Single method for consistent querying
    public static List<Account> queryAccounts(Set<Id> accountIds) {
        return [
            SELECT Id, Name, Industry, Description, AnnualRevenue,
                   OwnerId, Owner.Name, Owner.Email
            FROM Account
            WHERE Id IN :accountIds
        ];
    }

    public static void processAccounts(List<Account> accounts) {
        for (Account acc : accounts) {
            // All fields in REQUIRED_FIELDS are safe to access
            if (acc.Industry == 'Technology') {
                acc.Description = 'Tech: ' + acc.Name;
            }
        }
    }
}

Pattern 2: Selector Layer

Use a selector pattern to centralize query field management:

public class AccountSelector {

    // Default fields for most operations
    private static final List<String> DEFAULT_FIELDS = new List<String>{
        'Id', 'Name', 'Industry', 'Type', 'OwnerId'
    };

    // Extended fields for detailed views
    private static final List<String> DETAIL_FIELDS = new List<String>{
        'Id', 'Name', 'Industry', 'Type', 'OwnerId',
        'Description', 'AnnualRevenue', 'NumberOfEmployees',
        'BillingCity', 'BillingState', 'BillingCountry',
        'Owner.Name', 'Owner.Email'
    };

    public List<Account> selectById(Set<Id> ids) {
        return selectByIdWithFields(ids, DEFAULT_FIELDS);
    }

    public List<Account> selectByIdDetailed(Set<Id> ids) {
        return selectByIdWithFields(ids, DETAIL_FIELDS);
    }

    private List<Account> selectByIdWithFields(Set<Id> ids, List<String> fields) {
        String query = 'SELECT ' + String.join(fields, ', ') +
                       ' FROM Account WHERE Id IN :ids';
        return Database.query(query);
    }
}

Pattern 3: Field Validation Helper

public class SObjectFieldValidator {

    /**
     * Check if a field was queried on an sObject
     * @param obj The sObject to check
     * @param fieldName The API name of the field
     * @return true if the field is populated (was queried)
     */
    public static Boolean isFieldPopulated(SObject obj, String fieldName) {
        try {
            obj.get(fieldName);
            return true;
        } catch (SObjectException e) {
            return false;
        }
    }

    /**
     * Get field value with default if not queried
     * @param obj The sObject
     * @param fieldName The field API name
     * @param defaultValue Value to return if field not queried
     * @return The field value or default
     */
    public static Object getFieldOrDefault(SObject obj, String fieldName, Object defaultValue) {
        try {
            Object value = obj.get(fieldName);
            return value != null ? value : defaultValue;
        } catch (SObjectException e) {
            return defaultValue;
        }
    }
}

---

Quick Reference: Field Coverage Checklist

Before running code that processes SOQL results:

Direct Fields

• [ ] All fields in if conditions are queried
• [ ] All fields on left side of assignments are queried
• [ ] All fields passed to methods are queried
• [ ] All fields used in map keys/values are queried

Relationship Fields

• [ ] Parent fields use dot notation (e.g., Account.Name)
• [ ] Parent object null checks before field access
• [ ] Relationship traversal doesn't exceed 5 levels

Subqueries

• [ ] Child records accessed as List, not single record
• [ ] Subquery SELECT includes all accessed child fields
• [ ] Null check before iterating subquery results

Dynamic Access

• [ ] Fields accessed via get(fieldName) are queried
• [ ] Dynamic queries include all needed fields

---

Common LLM Mistakes Summary

---

Reference

• SOQL Anti-Patterns: See references/anti-patterns.md for general SOQL mistakes
• Selector Patterns: See references/selector-patterns.md for query organization

<!-- Parent: querying-soql/SKILL.md -->

Query Optimization & Governor Limits

Indexing Strategy

Indexed Fields (Always Selective):

• Id, Name, OwnerId, CreatedDate, LastModifiedDate, RecordTypeId
• External ID fields, Master-Detail relationship fields
• Lookup fields (when unique)

Standard Indexed Fields by Object:

• Account: AccountNumber, Site
• Contact: Email
• Lead: Email
• Case: CaseNumber

Selectivity Rules

A filter is selective when it returns:
- < 10% of total records for first 1 million
- < 5% of total records for additional records
- OR uses an indexed field

Optimization Patterns

-- ❌ NON-SELECTIVE (scans all records)
SELECT Id FROM Lead WHERE Status = 'Open'

-- ✅ SELECTIVE (uses index + selective filter)
SELECT Id FROM Lead
WHERE Status = 'Open'
AND CreatedDate = LAST_N_DAYS:30
LIMIT 10000

-- ❌ LEADING WILDCARD (can't use index)
SELECT Id FROM Account WHERE Name LIKE '%corp'

-- ✅ TRAILING WILDCARD (uses index)
SELECT Id FROM Account WHERE Name LIKE 'Acme%'

Query Plan Analysis

# Get query plan
sf data query \
  --query "SELECT Id FROM Account WHERE Name = 'Test'" \
  --target-org my-org \
  --use-tooling-api \
  --plan

Plan Output Interpretation:

• Cardinality: Estimated rows returned
• Cost: Relative query cost (lower is better)
• Fields: Index fields used
• LeadingOperationType: How the query starts (Index vs TableScan)

---

Governor Limits

Efficient Patterns

-- ❌ Query all, filter in Apex
SELECT Id, Name FROM Account
-- Then filter 50,000 records in Apex

-- ✅ Filter in SOQL
SELECT Id, Name FROM Account
WHERE Industry = 'Technology' AND IsActive__c = true
LIMIT 1000

-- ❌ Multiple queries in loop
for (Contact c : contacts) {
    Account a = [SELECT Name FROM Account WHERE Id = :c.AccountId];
}

-- ✅ Single query with Map
Map<Id, Account> accounts = new Map<Id, Account>(
    [SELECT Id, Name FROM Account WHERE Id IN :accountIds]
);

SOQL FOR Loops

// For large datasets - doesn't load all into heap
for (Account acc : [SELECT Id, Name FROM Account WHERE Industry = 'Technology']) {
    // Process one record at a time
    // Governor: Uses queryMore internally (200 at a time)
}

// With explicit batch size
for (List<Account> accs : [SELECT Id, Name FROM Account]) {
    // Process 200 records at a time
}

Security Patterns

WITH SECURITY_ENFORCED

-- Throws exception if user lacks FLS
SELECT Id, Name, Phone
FROM Account
WITH SECURITY_ENFORCED

WITH USER_MODE / SYSTEM_MODE

-- Respects sharing rules (default in Apex)
SELECT Id, Name FROM Account WITH USER_MODE

-- Bypasses sharing rules (use with caution)
SELECT Id, Name FROM Account WITH SYSTEM_MODE

In Apex: stripInaccessible

// Strip inaccessible fields instead of throwing
SObjectAccessDecision decision = Security.stripInaccessible(
    AccessType.READABLE,
    [SELECT Id, Name, SecretField__c FROM Account]
);
List<Account> safeAccounts = decision.getRecords();

<!-- Parent: querying-soql/SKILL.md -->

Selector Patterns: Query Abstraction in Vanilla Apex

This guide teaches query abstraction patterns using pure Apex — no external libraries required. These patterns improve testability, maintainability, and security compliance.

---

Why Use a Selector Layer?

The Problem

Without abstraction, SOQL queries are scattered everywhere:

// In TriggerHandler.cls
List<Account> accounts = [SELECT Id, Name FROM Account WHERE Id IN :accountIds];

// In BatchJob.cls (duplicate!)
List<Account> accounts = [SELECT Id, Name FROM Account WHERE Id IN :ids];

// In ServiceClass.cls (slightly different fields!)
List<Account> accounts = [SELECT Id, Name, Industry FROM Account WHERE Id IN :accountIds];

Problems: 1. Duplication: Same query logic repeated 2. Inconsistency: Different fields queried in different places 3. Fragility: Field deletion breaks multiple classes 4. Testability: Must create real records to test 5. Security: FLS/sharing often forgotten

The Solution

Centralize queries in Selector classes:

// Single source of truth
public class AccountSelector {
    public static List<Account> byIds(Set<Id> accountIds) {
        return [
            SELECT Id, Name, Industry
            FROM Account
            WHERE Id IN :accountIds
            WITH SECURITY_ENFORCED
        ];
    }
}

// Usage everywhere
List<Account> accounts = AccountSelector.byIds(accountIds);

---

Pattern 1: Basic Selector Class

The simplest approach - a class with static query methods.

/**
 * AccountSelector - Centralized queries for Account object
 */
public inherited sharing class AccountSelector {

    // ═══════════════════════════════════════════════════════════════════
    // FIELD SETS (centralized field lists)
    // ═══════════════════════════════════════════════════════════════════

    private static final List<SObjectField> STANDARD_FIELDS = new List<SObjectField>{
        Account.Id,
        Account.Name,
        Account.Industry,
        Account.AnnualRevenue,
        Account.OwnerId
    };

    // ═══════════════════════════════════════════════════════════════════
    // QUERY METHODS
    // ═══════════════════════════════════════════════════════════════════

    /**
     * Query accounts by their IDs
     */
    public static List<Account> byIds(Set<Id> accountIds) {
        if (accountIds == null || accountIds.isEmpty()) {
            return new List<Account>();
        }
        return [
            SELECT Id, Name, Industry, AnnualRevenue, OwnerId
            FROM Account
            WHERE Id IN :accountIds
            WITH SECURITY_ENFORCED
        ];
    }

    /**
     * Query accounts by Owner
     */
    public static List<Account> byOwnerId(Id ownerId) {
        return [
            SELECT Id, Name, Industry, AnnualRevenue, OwnerId
            FROM Account
            WHERE OwnerId = :ownerId
            WITH SECURITY_ENFORCED
            LIMIT 1000
        ];
    }

    /**
     * Query accounts with their contacts
     */
    public static List<Account> withContactsByIds(Set<Id> accountIds) {
        return [
            SELECT Id, Name,
                   (SELECT Id, FirstName, LastName, Email
                    FROM Contacts
                    WHERE IsActive__c = true
                    LIMIT 50)
            FROM Account
            WHERE Id IN :accountIds
            WITH SECURITY_ENFORCED
        ];
    }
}

Usage:

// Clean, readable, testable
List<Account> accounts = AccountSelector.byIds(accountIdSet);
List<Account> myAccounts = AccountSelector.byOwnerId(UserInfo.getUserId());

---

Pattern 2: Selector with Sharing Modes

Control sharing rules at the selector level.

/**
 * ContactSelector with sharing mode control
 */
public class ContactSelector {

    // ═══════════════════════════════════════════════════════════════════
    // USER MODE (respects sharing rules - default)
    // ═══════════════════════════════════════════════════════════════════

    public inherited sharing class UserMode {
        public static List<Contact> byAccountIds(Set<Id> accountIds) {
            return [
                SELECT Id, FirstName, LastName, Email, AccountId
                FROM Contact
                WHERE AccountId IN :accountIds
                WITH USER_MODE
            ];
        }
    }

    // ═══════════════════════════════════════════════════════════════════
    // SYSTEM MODE (bypasses sharing - use carefully!)
    // ═══════════════════════════════════════════════════════════════════

    public without sharing class SystemMode {
        public static List<Contact> byAccountIds(Set<Id> accountIds) {
            return [
                SELECT Id, FirstName, LastName, Email, AccountId
                FROM Contact
                WHERE AccountId IN :accountIds
                WITH SYSTEM_MODE
            ];
        }
    }
}

// Usage
List<Contact> visibleContacts = ContactSelector.UserMode.byAccountIds(ids);
List<Contact> allContacts = ContactSelector.SystemMode.byAccountIds(ids);

---

Pattern 3: Mockable Selector (for Unit Tests)

Enable query mocking without database calls.

/**
 * OpportunitySelector with mocking support
 */
public inherited sharing class OpportunitySelector {

    // Test-visible mock data
    @TestVisible
    private static List<Opportunity> mockData;

    /**
     * Query opportunities by Account IDs
     * Returns mock data in tests if set
     */
    public static List<Opportunity> byAccountIds(Set<Id> accountIds) {
        if (Test.isRunningTest() && mockData != null) {
            return mockData;
        }
        return [
            SELECT Id, Name, StageName, Amount, CloseDate, AccountId
            FROM Opportunity
            WHERE AccountId IN :accountIds
            WITH SECURITY_ENFORCED
        ];
    }

    /**
     * Set mock data for testing
     */
    @TestVisible
    private static void setMockData(List<Opportunity> opportunities) {
        mockData = opportunities;
    }
}

Test Usage:

@IsTest
private class OpportunitySelectorTest {
    @IsTest
    static void testByAccountIds_returnsMockData() {
        // Arrange - no database records needed!
        List<Opportunity> mockOpps = new List<Opportunity>{
            new Opportunity(Name = 'Test Opp', StageName = 'Prospecting', Amount = 1000)
        };
        OpportunitySelector.setMockData(mockOpps);

        // Act
        List<Opportunity> result = OpportunitySelector.byAccountIds(new Set<Id>());

        // Assert
        System.assertEquals(1, result.size());
        System.assertEquals('Test Opp', result[0].Name);
    }
}

---

Pattern 4: Query Builder (Dynamic SOQL)

For complex, dynamic queries that vary at runtime.

/**
 * Dynamic query builder for flexible SOQL construction
 */
public inherited sharing class QueryBuilder {

    private String objectName;
    private Set<String> fields = new Set<String>();
    private List<String> conditions = new List<String>();
    private Map<String, Object> bindings = new Map<String, Object>();
    private String orderByClause;
    private Integer limitCount;

    /**
     * Constructor
     */
    public QueryBuilder(String objectName) {
        this.objectName = objectName;
    }

    /**
     * Add fields to select
     */
    public QueryBuilder selectFields(List<String> fieldList) {
        fields.addAll(fieldList);
        return this;
    }

    /**
     * Add fields using SObjectField tokens (type-safe!)
     */
    public QueryBuilder selectFields(List<SObjectField> fieldTokens) {
        for (SObjectField token : fieldTokens) {
            fields.add(String.valueOf(token));
        }
        return this;
    }

    /**
     * Add WHERE condition with binding
     */
    public QueryBuilder whereEquals(String field, Object value) {
        String bindName = 'bind' + bindings.size();
        conditions.add(field + ' = :' + bindName);
        bindings.put(bindName, value);
        return this;
    }

    /**
     * Add WHERE IN condition
     */
    public QueryBuilder whereIn(String field, Set<Id> ids) {
        String bindName = 'bind' + bindings.size();
        conditions.add(field + ' IN :' + bindName);
        bindings.put(bindName, ids);
        return this;
    }

    /**
     * Add ORDER BY
     */
    public QueryBuilder orderBy(String field, Boolean ascending) {
        orderByClause = field + (ascending ? ' ASC' : ' DESC');
        return this;
    }

    /**
     * Add LIMIT
     */
    public QueryBuilder setLimit(Integer count) {
        limitCount = count;
        return this;
    }

    /**
     * Build and execute the query
     */
    public List<SObject> execute() {
        String query = buildQuery();
        return Database.queryWithBinds(query, bindings, AccessLevel.USER_MODE);
    }

    /**
     * Build query string (for debugging)
     */
    public String buildQuery() {
        List<String> parts = new List<String>();

        // SELECT
        if (fields.isEmpty()) {
            fields.add('Id');
        }
        parts.add('SELECT ' + String.join(new List<String>(fields), ', '));

        // FROM
        parts.add('FROM ' + objectName);

        // WHERE
        if (!conditions.isEmpty()) {
            parts.add('WHERE ' + String.join(conditions, ' AND '));
        }

        // ORDER BY
        if (orderByClause != null) {
            parts.add('ORDER BY ' + orderByClause);
        }

        // LIMIT
        if (limitCount != null) {
            parts.add('LIMIT ' + limitCount);
        }

        return String.join(parts, ' ');
    }
}

Usage:

// Fluent API for dynamic queries
List<SObject> results = new QueryBuilder('Account')
    .selectFields(new List<SObjectField>{Account.Id, Account.Name, Account.Industry})
    .whereEquals('Industry', 'Technology')
    .whereIn('Id', accountIds)
    .orderBy('Name', true)
    .setLimit(100)
    .execute();

// Debug the generated query
System.debug(new QueryBuilder('Account').selectFields(...).buildQuery());
// "SELECT Id, Name, Industry FROM Account WHERE Industry = :bind0 AND Id IN :bind1 ORDER BY Name ASC LIMIT 100"

---

Pattern 5: Bulkified Query Pattern

The Map-based lookup pattern for bulk operations.

/**
 * BulkQueryHelper - Reusable bulk query patterns
 */
public inherited sharing class BulkQueryHelper {

    /**
     * Get Accounts by ID as a Map (O(1) lookup)
     */
    public static Map<Id, Account> getAccountMapByIds(Set<Id> accountIds) {
        return new Map<Id, Account>([
            SELECT Id, Name, Industry
            FROM Account
            WHERE Id IN :accountIds
            WITH SECURITY_ENFORCED
        ]);
    }

    /**
     * Get Contacts grouped by AccountId
     */
    public static Map<Id, List<Contact>> getContactsByAccountId(Set<Id> accountIds) {
        Map<Id, List<Contact>> contactsByAccount = new Map<Id, List<Contact>>();

        for (Contact c : [
            SELECT Id, FirstName, LastName, Email, AccountId
            FROM Contact
            WHERE AccountId IN :accountIds
            WITH SECURITY_ENFORCED
        ]) {
            if (!contactsByAccount.containsKey(c.AccountId)) {
                contactsByAccount.put(c.AccountId, new List<Contact>());
            }
            contactsByAccount.get(c.AccountId).add(c);
        }

        return contactsByAccount;
    }
}

Usage in Trigger:

// ❌ WRONG: Query per record
for (Opportunity opp : Trigger.new) {
    Account a = [SELECT Name FROM Account WHERE Id = :opp.AccountId];
}

// ✅ CORRECT: Bulk query with Map lookup
Set<Id> accountIds = new Set<Id>();
for (Opportunity opp : Trigger.new) {
    accountIds.add(opp.AccountId);
}

Map<Id, Account> accountMap = BulkQueryHelper.getAccountMapByIds(accountIds);

for (Opportunity opp : Trigger.new) {
    Account a = accountMap.get(opp.AccountId);
    if (a != null) {
        // Use account data
    }
}

---

Best Practices Summary

---

When to Use Each Pattern

<!-- Parent: querying-soql/SKILL.md -->

SOQL Quick Reference

Query Structure

SELECT fields
FROM object
[WHERE conditions]
[WITH filter]
[GROUP BY fields]
[HAVING conditions]
[ORDER BY fields [ASC|DESC] [NULLS FIRST|LAST]]
[LIMIT number]
[OFFSET number]
[FOR UPDATE]

---

Operators

Comparison Operators

Logical Operators

LIKE Patterns

---

Date Literals

Relative Dates

N Days/Weeks/Months/Years

Specific Dates

-- Date only
WHERE CloseDate = 2024-12-31

-- DateTime
WHERE CreatedDate >= 2024-01-01T00:00:00Z

---

Aggregate Functions

---

Date Functions

---

Relationship Queries

Child-to-Parent (Dot Notation)

-- Standard objects
SELECT Contact.Name, Contact.Account.Name FROM Contact

-- Custom objects (use __r)
SELECT Child__c.Name, Child__c.Parent__r.Name FROM Child__c

Parent-to-Child (Subquery)

-- Standard objects
SELECT Id, (SELECT Id FROM Contacts) FROM Account

-- Custom objects (use __r)
SELECT Id, (SELECT Id FROM Children__r) FROM Parent__c

---

WITH Clauses

---

Governor Limits

---

Index Usage

Always Indexed

• Id
• Name
• OwnerId
• CreatedDate
• LastModifiedDate
• RecordTypeId
• External ID fields
• Master-Detail fields

Selective Query Rules

• Query is selective if WHERE returns < 10% of first 1M records
• Or uses an indexed field with < 1M matching records
• Non-selective queries on large tables fail

---

CLI Commands

# Basic query
sf data query --query "SELECT Id, Name FROM Account LIMIT 10" --target-org my-org

# JSON output
sf data query --query "SELECT Id, Name FROM Account" --target-org my-org --json

# CSV output
sf data query --query "SELECT Id, Name FROM Account" --result-format csv --target-org my-org

# Bulk export (for large results, > 2,000 records)
sf data export bulk --query "SELECT Id, Name FROM Account" --target-org my-org --output-file accounts.csv

# Query plan (uses REST API explain endpoint)
sf api request rest "/query/?explain=SELECT+Id+FROM+Account+WHERE+Name='Test'" --target-org my-org --json

<!-- Parent: querying-soql/SKILL.md -->

SOQL Syntax Reference

Basic Query Structure

SELECT field1, field2, ...
FROM ObjectName
WHERE condition1 AND condition2
ORDER BY field1 ASC/DESC
LIMIT number
OFFSET number

Field Selection

-- Specific fields (recommended)
SELECT Id, Name, Industry FROM Account

-- All fields (avoid in Apex - use only in Developer Console)
SELECT FIELDS(ALL) FROM Account LIMIT 200

-- Standard fields only
SELECT FIELDS(STANDARD) FROM Account

WHERE Clause Operators

Date Literals

-- Created in last 30 days
SELECT Id FROM Account WHERE CreatedDate = LAST_N_DAYS:30

-- Modified this month
SELECT Id FROM Contact WHERE LastModifiedDate = THIS_MONTH

---

Relationship Queries

Child-to-Parent (Dot Notation)

-- Access parent fields
SELECT Id, Name, Account.Name, Account.Industry
FROM Contact
WHERE Account.AnnualRevenue > 1000000

-- Up to 5 levels
SELECT Id, Contact.Account.Owner.Manager.Name
FROM Case

Parent-to-Child (Subquery)

-- Get parent with related children
SELECT Id, Name,
       (SELECT Id, FirstName, LastName FROM Contacts),
       (SELECT Id, Name, Amount FROM Opportunities WHERE StageName = 'Closed Won')
FROM Account
WHERE Industry = 'Technology'

Standard Relationship Names

Custom Object Relationships

-- Custom relationship: add __r suffix
SELECT Id, Name, Custom_Object__r.Name
FROM Another_Object__c

-- Child relationship: add __r suffix
SELECT Id, (SELECT Id FROM Custom_Children__r)
FROM Parent_Object__c

---

Aggregate Queries

Basic Aggregates

-- Count all records
SELECT COUNT() FROM Account

-- Count with alias
SELECT COUNT(Id) cnt FROM Account

-- Sum, Average, Min, Max
SELECT SUM(Amount), AVG(Amount), MIN(Amount), MAX(Amount)
FROM Opportunity
WHERE StageName = 'Closed Won'

GROUP BY

-- Count by field
SELECT Industry, COUNT(Id)
FROM Account
GROUP BY Industry

-- Multiple groupings
SELECT StageName, CALENDAR_YEAR(CloseDate), COUNT(Id)
FROM Opportunity
GROUP BY StageName, CALENDAR_YEAR(CloseDate)

HAVING Clause

-- Filter aggregated results
SELECT Industry, COUNT(Id) cnt
FROM Account
GROUP BY Industry
HAVING COUNT(Id) > 10

GROUP BY ROLLUP

-- Subtotals
SELECT LeadSource, Rating, COUNT(Id)
FROM Lead
GROUP BY ROLLUP(LeadSource, Rating)

---

Advanced Features

Polymorphic Relationships (What)

-- Query polymorphic fields
SELECT Id, What.Name, What.Type
FROM Task
WHERE What.Type IN ('Account', 'Opportunity')

-- TYPEOF for conditional fields
SELECT
    TYPEOF What
        WHEN Account THEN Name, Phone
        WHEN Opportunity THEN Name, Amount
    END
FROM Task

Semi-Joins and Anti-Joins

-- Semi-join: Records that HAVE related records
SELECT Id, Name FROM Account
WHERE Id IN (SELECT AccountId FROM Contact)

-- Anti-join: Records that DON'T HAVE related records
SELECT Id, Name FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Opportunity)

Format and Currency

-- Format currency/date in results
SELECT FORMAT(Amount), FORMAT(CloseDate) FROM Opportunity

-- Convert to user's currency
SELECT Id, convertCurrency(Amount) FROM Opportunity