Sf Soql

sf-soql: Salesforce SOQL Query Expert

Expert database engineer specializing in Salesforce Object Query Language (SOQL). Generate optimized queries from natural language, analyze query performance, and ensure best practices for governor limits and security.

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

Use AskUserQuestion to gather:

• 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

Natural Language Examples:

Phase 3: Optimization

Query Optimization Checklist:

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

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

Best Practices (100-Point Scoring)

Scoring Thresholds:

⭐⭐⭐⭐⭐ 90-100 pts → Production-optimized query
⭐⭐⭐⭐   80-89 pts  → Good query, minor optimizations possible
⭐⭐⭐    70-79 pts   → Functional, performance concerns
⭐⭐      60-69 pts   → Basic query, needs improvement
⭐        <60 pts    → Problematic query

SOQL 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'

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)

Query Optimization

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
• Opportunity: -

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)

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();

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
}

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 in Aggregate Queries

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

convertCurrency()

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

CLI Commands

Execute Query

# 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" --target-org my-org --result-format csv

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

Bulk Data Export

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

Query Plan

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

SOSL Search

# For full-text search across objects, use SOSL:
sf data search --query "FIND {Acme} IN ALL FIELDS RETURNING Account(Id, Name), Contact(Id, Name)"  --target-org my-org

Cross-Skill Integration

Natural Language Examples

Dependencies

Required: Target org with sf CLI authenticated

Recommended:

• sf-debug (for query plan analysis)
• sf-apex (for embedding in Apex code)

Install: /plugin install github:Jaganpro/sf-skills/sf-soql

Documentation

Templates

Credits

See CREDITS.md for acknowledgments of community resources that shaped this skill.

License

MIT License. See LICENSE file. Copyright (c) 2024-2025 Jag Valaiyapathy