Advanced SOQL

Build queries at runtime as strings when fields, objects, or filters are not known at compile time:

15.1 Dynamic SOQL

Build queries at runtime as strings when fields, objects, or filters are not known at compile time:

Binding with a map (Database.queryWithBinds, Winter '23+) avoids relying on local variable scope:

public inherited sharing class DynamicQuery {
    public static List<SObject> search(String objectName, List<String> fields, String nameFilter) {
        // validate identifiers against the schema — never trust raw input
        Schema.SObjectType sType = Schema.getGlobalDescribe().get(objectName);
        if (sType == null) throw new IllegalArgumentException('Unknown object ' + objectName);
        Map<String, Schema.SObjectField> fieldMap = sType.getDescribe().fields.getMap();
        for (String f : fields) {
            if (!fieldMap.containsKey(f.toLowerCase())) throw new IllegalArgumentException('Bad field ' + f);
        }

        String pattern = '%' + nameFilter + '%';
        String soql = 'SELECT ' + String.join(fields, ', ')
                    + ' FROM ' + objectName
                    + ' WHERE Name LIKE :pattern'          // bind variables work in dynamic SOQL
                    + ' LIMIT 100';
        return Database.query(soql, AccessLevel.USER_MODE);
    }
}

Map<String, Object> binds = new Map<String, Object>{ 'ind' => 'Retail', 'minRev' => 500000 };
List<Account> accs = Database.queryWithBinds(
    'SELECT Id, Name FROM Account WHERE Industry = :ind AND AnnualRevenue > :minRev',
    binds,
    AccessLevel.USER_MODE
);
Integer cnt = Database.countQueryWithBinds('SELECT COUNT() FROM Account WHERE Industry = :ind', binds, AccessLevel.USER_MODE);

15.2 SOQL injection prevention

// VULNERABLE: user input concatenated into the query
String bad = 'SELECT Id FROM Account WHERE Name = \'' + userInput + '\'';
// userInput = "x' OR Name != '" returns every account!

// SAFE options, in order of preference:
// 1. static SOQL with binds
List<Account> a1 = [SELECT Id FROM Account WHERE Name = :userInput];
// 2. dynamic SOQL with binds (:userInput or queryWithBinds)
// 3. escape when binding is impossible
String safe = 'SELECT Id FROM Account WHERE Name = \'' + String.escapeSingleQuotes(userInput) + '\'';
// 4. allowlist identifiers (field/object names, sort direction) against describe results

15.3 SOQL for loops

A SOQL for loop retrieves records in batches of 200 using an internal cursor, keeping heap usage low for large result sets:

The 50,000-row query limit still applies; for more, use Batch Apex (§24) or Apex Cursors (§38).

// list form — process 200 at a time, DML per chunk (bulk-friendly)
for (List<Contact> batch : [SELECT Id, Email FROM Contact WHERE Email LIKE '%@oldcorp.com']) {
    for (Contact c : batch) {
        c.Email = c.Email.replace('@oldcorp.com', '@newcorp.com');
    }
    update batch;    // one DML per 200 rows — still counts toward the 150 DML limit
}

// single-record form — simplest, still chunked internally
for (Account a : [SELECT Id, Name FROM Account]) {
    System.debug(a.Name);
}

15.4 Security-aware queries

Salesforce recommends WITH USER_MODE over WITH SECURITY_ENFORCED for new code: it also enforces sharing, checks fields in all clauses, and supports polymorphic fields. Inspect QueryException.getInaccessibleFields() to see which fields failed.

// User mode: enforces sharing, object permissions, and FLS. Throws QueryException on inaccessible fields.
List<Account> a = [SELECT Id, Name, AnnualRevenue FROM Account WITH USER_MODE];

// Older: WITH SECURITY_ENFORCED (CRUD/FLS only, not sharing; doesn't cover WHERE clause fields)
List<Account> b = [SELECT Id, Name FROM Account WITH SECURITY_ENFORCED];

// Explicit system mode
List<Account> c = [SELECT Id, Name FROM Account WITH SYSTEM_MODE];

15.5 Query performance and selectivity

Filter on indexed fields (Id, Name, OwnerId, CreatedDate, SystemModstamp, lookup/master-detail fields, External ID and unique fields, custom indexes).

Avoid leading wildcards (LIKE '%abc'), negative filters (!=, NOT IN), and null comparisons on large objects — they are often non-selective.

Use the Query Plan tool (Developer Console → Help → Preferences → Enable Query Plan) to see cost and index use.

Large data volumes (millions of rows): consider skinny tables, custom indexes (via Salesforce Support), and archiving with Big Objects.

15.6 Querying metadata and special objects

List<RecordType> rts = [SELECT Id, DeveloperName FROM RecordType WHERE SobjectType = 'Account'];
List<Discount_Rule__mdt> rules = [SELECT DeveloperName, Percent__c FROM Discount_Rule__mdt];
List<AsyncApexJob> jobs = [SELECT Id, Status, NumberOfErrors FROM AsyncApexJob WHERE JobType = 'BatchApex' ORDER BY CreatedDate DESC LIMIT 5];
List<ApexClass> classes = [SELECT Name, ApiVersion FROM ApexClass WHERE NamespacePrefix = null];
// Prefer Schema describe calls for record types: Schema.SObjectType.Account.getRecordTypeInfosByDeveloperName()