sf-soql: Salesforce SOQL Query Expert

Core Responsibilities

1. Natural Language → SOQL: Convert plain English requests to optimized queries
2. Query Optimization: Analyze and improve query performance
3. Relationship Queries: Build parent-child and child-parent traversals
4. Aggregate Functions: COUNT, SUM, AVG, MIN, MAX with GROUP BY
5. Security Enforcement: Ensure FLS and sharing rules compliance
6. Governor Limit Awareness: Design queries within limits

Workflow (4-Phase Pattern)

Phase 1: Requirements Gathering

• What data is needed (objects, fields)
• Filter criteria (WHERE conditions)
• Sort requirements (ORDER BY)
• Record limit requirements
• Use case (display, processing, reporting)

Phase 2: Query Generation

SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account WHERE IsActive__c = true

SELECT Id, Name, Email FROM Contact WHERE CreatedDate = THIS_MONTH

SELECT StageName, COUNT(Id) FROM Opportunity GROUP BY StageName

SELECT Id, Name, AnnualRevenue FROM Account WHERE AnnualRevenue > 1000000 ORDER BY Name

Phase 3: Optimization

1. Selectivity: Does WHERE clause use indexed fields?
2. Field Selection: Only query needed fields (not SELECT *)
3. Limit: Is LIMIT appropriate for use case?
4. Relationship Depth: Avoid deep traversals (max 5 levels)
5. Aggregate Queries: Use for counts instead of loading all records

Phase 4: Validation & Execution

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

# Analyze query plan
sf data query --query "..." --target-org my-org --use-tooling-api --plan

Best Practices (100-Point Scoring)

Quick Reference

Security (Always Apply)

-- Enforce FLS (throws exception on inaccessible fields)
SELECT Id, Name, Phone FROM Account WITH SECURITY_ENFORCED

-- Respect sharing rules
SELECT Id, Name FROM Account WITH USER_MODE

See references/query-optimization.md for

stripInaccessible

in Apex,

SYSTEM_MODE

, governor limits, SOQL FOR loops, indexing strategy, and selectivity rules.

Governor Limits (Key Numbers)

Anti-pattern: Never query inside a loop. Use

Map<Id, SObject>

with

WHERE Id IN :idSet

instead.

SOQL Syntax, Relationships & Aggregates

See references/soql-syntax-reference.md for the complete reference including: basic query structure, WHERE operators, date literals, child-to-parent dot notation, parent-to-child subqueries, relationship names, aggregate functions (COUNT, SUM, AVG, GROUP BY, HAVING, ROLLUP), polymorphic queries (TYPEOF), semi-joins, and anti-joins.

• Child-to-Parent:

  SELECT Contact.Account.Name FROM Case

  (up to 5 levels)
• Parent-to-Child:

  SELECT Id, (SELECT Id FROM Contacts) FROM Account

• Custom relationships: Use

  __r

  suffix (e.g.,

  Custom_Object__r.Name

  )
• Aggregates:

  SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry HAVING COUNT(Id) > 10

Query Optimization

See references/query-optimization.md for indexing strategy, selectivity rules, optimization patterns, query plan analysis, and efficient Apex patterns.

• Use indexed fields in WHERE (Id, Name, CreatedDate, Email, External IDs)
• Trailing wildcards use indexes (

  LIKE 'Acme%'

  ), leading wildcards don't (

  LIKE '%corp'

  )
• Filter in SOQL, not in Apex — use

  LIMIT

  appropriate to use case
• Use

  sf data query --plan

  to analyze query cost

Natural Language Examples

SELECT Id, Name FROM Account LIMIT 1000

SELECT Id, Name FROM Contact WHERE Email = null

SELECT Id, Name, Amount FROM Opportunity ORDER BY Amount DESC LIMIT 10

SELECT Id, Name, Email FROM Contact WHERE Email LIKE '%@gmail.com'

SELECT Id, Name, CloseDate FROM Opportunity WHERE CloseDate = THIS_QUARTER

SELECT Industry, SUM(AnnualRevenue) FROM Account GROUP BY Industry

CLI Commands

# Basic query (JSON output)
sf data query --query "SELECT Id, Name FROM Account LIMIT 10" --target-org my-org --json

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

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

# SOSL search
sf data search --query "FIND {Acme} IN ALL FIELDS RETURNING Account(Id, Name), Contact(Id, Name)" --target-org my-org

Cross-Skill Integration

Document Map

References (Extracted)

Docs

Templates

Dependencies

sf

Credits