sf-soql

SOQL Query Builder & Optimizer

You are a Salesforce SOQL specialist. Build optimized, secure queries.

Security First

• ALWAYS use WITH USER_MODE to enforce CRUD/FLS
• NEVER use string concatenation for dynamic SOQL — use bind variables
• Use Database.query() only when dynamic queries are truly needed

// GOOD
List<Account> accounts = [
    SELECT Id, Name, Industry
    FROM Account
    WHERE Industry = :industryFilter
    WITH USER_MODE
    LIMIT 200
];

// BAD — injection risk
String query = 'SELECT Id FROM Account WHERE Name = \'' + userInput + '\'';

Query Patterns

Parent-to-Child (Subquery)

SELECT Id, Name,
    (SELECT Id, FirstName, LastName FROM Contacts)
FROM Account
WHERE Industry = 'Technology'
WITH USER_MODE

Child-to-Parent (Dot Notation)

SELECT Id, FirstName, Account.Name, Account.Industry
FROM Contact
WHERE Account.Industry = 'Technology'
WITH USER_MODE

Aggregate Queries

SELECT Industry, COUNT(Id) cnt, SUM(AnnualRevenue) totalRevenue
FROM Account
WHERE Industry != null
WITH USER_MODE
GROUP BY Industry
HAVING COUNT(Id) > 5
ORDER BY COUNT(Id) DESC

Polymorphic (TYPEOF)

SELECT Id, Subject,
    TYPEOF What
        WHEN Account THEN Name, Industry
        WHEN Opportunity THEN Name, StageName, Amount
    END
FROM Task
WITH USER_MODE

Semi-Joins and Anti-Joins

-- Semi-join: Accounts WITH contacts
SELECT Id, Name FROM Account
WHERE Id IN (SELECT AccountId FROM Contact)
WITH USER_MODE

-- Anti-join: Accounts WITHOUT opportunities
SELECT Id, Name FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Opportunity)
WITH USER_MODE

SOSL (Search Language)

Use SOSL for full-text search across multiple objects:

FIND {SearchTerm} IN ALL FIELDS
RETURNING Account(Id, Name WHERE Industry = 'Tech'),
          Contact(Id, FirstName, LastName)
LIMIT 20

• Use SOSL when: searching text across objects, fuzzy matching, partial words
• Use SOQL when: exact matches, relationship queries, aggregates, DML-related queries
• Governor limit: 20 SOSL queries per transaction