使用嵌套查询通过价格书ID获取价格书项目详情

Question

我有一个叫做 Edition 的对象，它有一个相关的 PriceBook ID，我想在查询 Edition 详情时获取 PriceBook 的项目：

我尝试过的：

  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 在 Edition__c/describe 中：

"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")

            # SOQL query to retrieve specific edition data using the edition id
            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}'"""
            
            # Execute the query
            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:
                query = f"""
                    SELECT
                    Id,
                    Name,
                    CurrencyIsoCode,
                    UnitPrice,
                    IsActive
                    FROM PricebookEntry WHERE Pricebook2Id = '{price_book_id}'
                    """

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

这是在 Salesforce 交流平台上同样问题的链接： https://salesforce.stackexchange.com/questions/419463/get-pricebook-items-details-using-pricebook-id-in-nested-query/419471#419471

最后补充一下，一个 Edition__c 可以关联多个 Pricebook2。

Answer 1

如果这个字段是真正的查找字段，你应该可以一次性完成连接查询。如果它只是一个文本字段，里面恰好有一个和价格表ID匹配的值，那你就需要执行两次查询。

你运行这个代码会得到什么结果呢？

SELECT Name, ProductCode, UnitPrice
FROM PricebookEntry
WHERE Pricebook2Id IN (SELECT Price_Book__c FROM Edition__c WHERE Id = '...')

如果你知道“关系名称”，还可以尝试在这个话题上做一些变动。

SELECT Id, Name,
    (SELECT Name, ProductCode, UnitPrice FROM PricebookEntries),
    (SELECT Name, Start_Date__c FROM Editions__r)
FROM Pricebook2
WHERE Id IN (SELECT Price_Book__c FROM Edition__c WHERE Id = '...')

Answer 2

我最终通过一个查询得到了想要的结果：

query = f"""
                SELECT Id, 
                Name, 
                Edition_Name__c, 
                End_Date__c, 
                Start_Date__c, 
                Legal_Entity__c, 
                CurrencyIsoCode, 
                Status__c,
                (SELECT Id, Name,
                        ( SELECT Id, Name,CurrencyIsoCode,UnitPrice,IsActive FROM PricebookEntries) 
                        FROM Price_Books__r) 
                    FROM Edition__c WHERE Id ='{edition_id}'"""