如何在查询Edition__c详情时嵌套获取关联PriceBook的条目？

我有一个自定义对象Edition__c，它包含关联Pricebook2的参考字段Price_Book__c，想在查询Edition__c详情时，一次性获取对应Pricebook下的所有PricebookEntry条目，避免分两次查询。

尝试过的嵌套SOQL（无效） #

query = f"""
           SELECT
                Id,
                Name,
                Edition_Name__c,
                End_Date__c,
                Start_Date__c,
                Legal_Entity__c,
                CurrencyIsoCode,
                Status__c,
                Price_Book__c,
                (SELECT Id, Name FROM PricebookEntry WHERE Pricebook2Id = Price_Book__c)
            FROM Edition__c
            WHERE Id = '{edition_id}'
        """

Price_Book__c字段元数据 #

{
    "name": "Price_Book__c",
    "nameField": false,
    "namePointing": false,
    "nillable": true,
    "permissionable": true,
    "picklistValues": [],
    "polymorphicForeignKey": false,
    "precision": 0,
    "queryByDistance": false,
    "referenceTargetField": null,
    "referenceTo": [
        "Pricebook2"
    ],
    "relationshipName": "Price_Book__r",
    "relationshipOrder": null,
    "restrictedDelete": false,
    "restrictedPicklist": false,
    "scale": 0,
    "searchPrefilterable": true,
    "soapType": "tns:ID",
    "sortable": true,
    "type": "reference"
}

当前可行的两次查询方案 #

try:
    sf = SFManager().sf
    edition_id = kwargs.get("id")

    # 查询Edition__c详情
    edition_query = f"""
    SELECT
    Id,
    Name,
    Edition_Name__c,
    End_Date__c,
    Start_Date__c,
    Legal_Entity__c,
    CurrencyIsoCode,
    Status__c,
    Price_Book__c
    FROM Edition__c WHERE Id = '{edition_id}'"""
    
    edition_result = sf.query(edition_query)
    edition_record = edition_result["records"][0]
    price_book_id = edition_record['Price_Book__c']
    
    if price_book_id is not None:
        # 查询对应Pricebook的PricebookEntry
        query = f"""
            SELECT
            Id,
            Name,
            CurrencyIsoCode,
            UnitPrice,
            IsActive
            FROM PricebookEntry WHERE Pricebook2Id = '{price_book_id}'
            """

        price_book_entries = sf.query(query)
        edition_record['PricebookEntries'] = price_book_entries["records"]
    return Response(edition_record)

补充说明：一个Edition__c可以关联多个Pricebook2。

最优解决方案：一次关联查询 #

原嵌套SOQL写法错误，因为子查询无法直接引用父对象字段作为筛选条件。正确做法是利用Salesforce的关系查询：通过Edition__c与Pricebook2的关联关系Price_Book__r，再结合Pricebook2到PricebookEntry的标准子查询关系PricebookEntries，实现一次查询获取所有数据。

正确的SOQL语句 #

SELECT
    Id,
    Name,
    Edition_Name__c,
    End_Date__c,
    Start_Date__c,
    Legal_Entity__c,
    CurrencyIsoCode,
    Status__c,
    Price_Book__c,
    Price_Book__r.(
        SELECT Id, Name, CurrencyIsoCode, UnitPrice, IsActive
        FROM PricebookEntries
    )
FROM Edition__c
WHERE Id = '{edition_id}'

修改后的Python代码实现 #

try:
    sf = SFManager().sf
    edition_id = kwargs.get("id")

    # 一次查询获取Edition__c及关联的PricebookEntry
    edition_query = f"""
    SELECT
        Id,
        Name,
        Edition_Name__c,
        End_Date__c,
        Start_Date__c,
        Legal_Entity__c,
        CurrencyIsoCode,
        Status__c,
        Price_Book__c,
        Price_Book__r.(
            SELECT Id, Name, CurrencyIsoCode, UnitPrice, IsActive
            FROM PricebookEntries
        )
    FROM Edition__c WHERE Id = '{edition_id}'"""
    
    edition_result = sf.query(edition_query)
    edition_record = edition_result["records"][0]
    
    # 整理返回结构（可选）
    if edition_record.get('Price_Book__r'):
        edition_record['PricebookEntries'] = edition_record['Price_Book__r']['PricebookEntries']['records']
        del edition_record['Price_Book__r']
    
    return Response(edition_record)

注意事项 #

• 确保拥有Pricebook2和PricebookEntry对象的查询权限
• 若Price_Book__c字段为空，Price_Book__r会返回null，不会包含PricebookEntry数据
• 一次查询减少API调用次数，比两次查询更高效

内容的提问来源于stack exchange，提问作者B. Mohammad