sf-soql

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

sf-soql: Salesforce SOQL Query Expert

sf-soql: Salesforce SOQL 查询专家

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.

专注于Salesforce对象查询语言（SOQL）的资深数据库工程师。支持将自然语言转换为优化后的查询语句，分析查询性能，并确保符合 governor 限制和安全最佳实践。

Core Responsibilities

核心职责

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

自然语言转SOQL：将普通英文请求转换为优化后的查询语句
查询优化：分析并提升查询性能
关系查询：构建父子及子父关系遍历查询
聚合函数：结合GROUP BY使用COUNT、SUM、AVG、MIN、MAX
安全合规：确保符合FLS（字段级安全）和共享规则
Governor限制意识：在限制范围内设计查询

Workflow (4-Phase Pattern)

工作流程（四阶段模式）

Phase 1: Requirements Gathering

阶段1：需求收集

Ask the user to gather:

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

询问用户以收集以下信息：

需要的数据（对象、字段）
过滤条件（WHERE子句）
排序要求（ORDER BY）
记录数量限制要求
使用场景（展示、处理、报表）

Phase 2: Query Generation

阶段2：查询生成

Natural Language Examples:

Request	Generated SOQL
"Get all active accounts with their contacts"	`SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account WHERE IsActive__c = true`
"Find contacts created this month"	`SELECT Id, Name, Email FROM Contact WHERE CreatedDate = THIS_MONTH`
"Count opportunities by stage"	`SELECT StageName, COUNT(Id) FROM Opportunity GROUP BY StageName`
"Get accounts with revenue over 1M sorted by name"	`SELECT Id, Name, AnnualRevenue FROM Account WHERE AnnualRevenue > 1000000 ORDER BY Name`

自然语言示例：

请求	生成的SOQL
"获取所有活跃账户及其联系人"	`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`
"获取年收入超过100万的账户并按名称排序"	`SELECT Id, Name, AnnualRevenue FROM Account WHERE AnnualRevenue > 1000000 ORDER BY Name`

Phase 3: Optimization

阶段3：优化

Query Optimization Checklist:

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

查询优化检查清单：

选择性：WHERE子句是否使用了索引字段？
字段选择：仅查询所需字段（避免使用SELECT *）
数量限制：LIMIT是否符合使用场景？
关系深度：避免深度遍历（最多5级）
聚合查询：使用聚合查询进行统计，而非加载所有记录

Phase 4: Validation & Execution

阶段4：验证与执行

bash

undefined

bash

undefined

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

---

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

---

Best Practices (100-Point Scoring)

最佳实践（100分评分制）

Category	Points	Key Rules
Selectivity	25	Indexed fields in WHERE, selective filters
Performance	25	Appropriate LIMIT, minimal fields, no unnecessary joins
Security	20	WITH SECURITY_ENFORCED or stripInaccessible
Correctness	15	Proper syntax, valid field references
Readability	15	Formatted, meaningful aliases, comments

Scoring Thresholds: 90-100 = Production-optimized, 80-89 = Good (minor optimizations possible), 70-79 = Performance concerns, <70 = Needs improvement.

类别	分值	关键规则
选择性	25	WHERE子句使用索引字段，筛选条件具备选择性
性能	25	合理设置LIMIT，查询最少必要字段，避免不必要的关联
安全	20	使用WITH SECURITY_ENFORCED或stripInaccessible
正确性	15	语法正确，字段引用有效
可读性	15	格式规范，使用有意义的别名和注释

评分阈值：90-100分 = 生产环境优化级别，80-89分 = 良好（可进行小幅优化），70-79分 = 存在性能隐患，<70分 = 需要改进。

Quick Reference

快速参考

Security (Always Apply)

安全（始终遵循）

sql

-- 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.

sql

-- 强制执行FLS（对不可访问字段抛出异常）
SELECT Id, Name, Phone FROM Account WITH SECURITY_ENFORCED

-- 遵守共享规则
SELECT Id, Name FROM Account WITH USER_MODE

有关Apex中的
stripInaccessible
、
SYSTEM_MODE
、governor限制、SOQL FOR循环、索引策略和选择性规则，请参阅references/query-optimization.md。

Governor Limits (Key Numbers)

Governor限制（关键数值）

Limit	Synchronous	Asynchronous
Total SOQL Queries	100	200
Records Retrieved	50,000	50,000

Anti-pattern: Never query inside a loop. Use
Map<Id, SObject>
with
WHERE Id IN :idSet
instead.

限制项	同步模式	异步模式
SOQL查询总数	100	200
检索记录数	50,000	50,000

反模式：绝不要在循环内执行查询。应使用
Map<Id, SObject>
结合
WHERE Id IN :idSet
替代。

SOQL Syntax, Relationships & Aggregates

SOQL语法、关系与聚合

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.

Key patterns:

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

完整参考请参阅references/soql-syntax-reference.md，包括：基本查询结构、WHERE运算符、日期字面量、子父点标记法、父子子查询、关系名称、聚合函数（COUNT、SUM、AVG、GROUP BY、HAVING、ROLLUP）、多态查询（TYPEOF）、半连接和反连接。

关键模式:

子父查询：
```
SELECT Contact.Account.Name FROM Case
```
（最多5级）

父子查询：

SELECT Id, (SELECT Id FROM Contacts) FROM Account

自定义关系：使用
```
__r
```
后缀（例如：
```
Custom_Object__r.Name
```
）

聚合查询：

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.

Key rules:

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

有关索引策略、选择性规则、优化模式、查询计划、governor限制和安全的内容，请参阅references/query-optimization.md。

关键规则:

在WHERE子句中使用索引字段（Id、Name、CreatedDate、Email、外部ID）
后缀通配符使用索引（
```
LIKE 'Acme%'
```
），前缀通配符不使用（
```
LIKE '%corp'
```
）
在SOQL中进行过滤，而非在Apex中过滤 — 根据使用场景合理设置
```
LIMIT
```
使用
```
sf data query --plan
```
分析查询成本

Natural Language Examples

自然语言示例

Request	SOQL
"Get me all accounts"	`SELECT Id, Name FROM Account LIMIT 1000`
"Find contacts without email"	`SELECT Id, Name FROM Contact WHERE Email = null`
"Top 10 opportunities by amount"	`SELECT Id, Name, Amount FROM Opportunity ORDER BY Amount DESC LIMIT 10`
"Contacts with @gmail emails"	`SELECT Id, Name, Email FROM Contact WHERE Email LIKE '%@gmail.com'`
"Opportunities closing this quarter"	`SELECT Id, Name, CloseDate FROM Opportunity WHERE CloseDate = THIS_QUARTER`
"Total revenue by industry"	`SELECT Industry, SUM(AnnualRevenue) FROM Account GROUP BY Industry`

请求	SOQL
"获取所有账户"	`SELECT Id, Name FROM Account LIMIT 1000`
"查找没有邮箱的联系人"	`SELECT Id, Name FROM Contact WHERE Email = null`
"按金额排序的前10个机会"	`SELECT Id, Name, Amount FROM Opportunity ORDER BY Amount DESC LIMIT 10`
"邮箱为@gmail的联系人"	`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

CLI命令

bash

undefined

bash

undefined

Basic query (JSON output)

基础查询（JSON输出）

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

CSV output to file

导出为CSV文件

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)

批量导出（>2000条记录）

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

SOSL search

SOSL搜索

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

---

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

---

Cross-Skill Integration

跨技能集成

Skill	When to Use	Example
sf-apex	Embed queries in Apex	Use the sf-apex skill: "Create service with SOQL query for accounts"
sf-data	Execute queries against org	Use the sf-data skill: "Query active accounts from production"
sf-debug	Analyze query performance	Use the sf-debug skill: "Analyze slow query in debug logs"
sf-lwc	Generate wire queries	Use the sf-lwc skill: "Create component with wired account query"

技能	使用场景	示例
sf-apex	在Apex中嵌入查询	使用sf-apex技能："创建包含账户SOQL查询的服务"
sf-data	针对组织执行查询	使用sf-data技能："从生产环境查询活跃账户"
sf-debug	分析查询性能	使用sf-debug技能："在调试日志中分析慢查询"
sf-lwc	生成wire查询	使用sf-lwc技能："创建包含账户wire查询的组件"

Document Map

文档地图

References (Extracted)

参考文档（提取）

Document	Description
SOQL Syntax Reference	Complete syntax, operators, dates, relationships, aggregates, advanced features
Query Optimization	Indexing, selectivity, patterns, query plan, governor limits, security

文档	描述
SOQL语法参考	完整语法、运算符、日期、关系、聚合及高级特性
查询优化	索引、选择性、模式、查询计划、governor限制、安全

Docs

文档列表

Document	Description
soql-reference.md	Complete SOQL syntax reference
cli-commands.md	SF CLI query commands
anti-patterns.md	Common mistakes and how to avoid them
selector-patterns.md	Query abstraction patterns (vanilla Apex)
field-coverage-rules.md	Ensure queries include all accessed fields

文档	描述
soql-reference.md	完整SOQL语法参考
cli-commands.md	SF CLI查询命令
anti-patterns.md	常见错误及避免方法
selector-patterns.md	查询抽象模式（原生Apex）
field-coverage-rules.md	确保查询包含所有需要访问的字段

Templates

模板

Template	Description
basic-queries.soql	Basic SOQL syntax examples
aggregate-queries.soql	COUNT, SUM, GROUP BY patterns
relationship-queries.soql	Parent-child traversals
optimization-patterns.soql	Selectivity and indexing
selector-class.cls	Selector class template
bulkified-query-pattern.cls	Map-based bulk lookups

模板	描述
basic-queries.soql	基础SOQL语法示例
aggregate-queries.soql	COUNT、SUM、GROUP BY模式
relationship-queries.soql	父子关系遍历示例
optimization-patterns.soql	选择性与索引示例
selector-class.cls	Selector类模板
bulkified-query-pattern.cls	基于Map的批量查询模式

Dependencies

依赖项

Required: Target org with

sf

CLI authenticated

Recommended: sf-debug (for query plan analysis), sf-apex (for embedding in Apex code)

必需：已通过

sf

CLI认证的目标组织

推荐：sf-debug（用于查询计划分析）、sf-apex（用于嵌入Apex代码）

Credits

致谢

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

有关形成本技能的社区资源鸣谢，请参阅CREDITS.md。",