Get PriceBook items details using Pricebook id in nested query

Question

I have an sObject called Edition, it has the related PriceBook id, how to get the items of the PriceBook when querying the Edition details:

What I tried:

  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}'
        # """

Update:

Price_Book__c in 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",

I have a working solution with 2 queries but I don't know it's the best solution:

 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)

Link to the same question in salesforce exchange : https://salesforce.stackexchange.com/questions/419463/get-pricebook-items-details-using-pricebook-id-in-nested-query/419471#419471

Last note, an Edition__c can have multiple Pricebook2 related to it.

Answer 1

If the field is a real lookup you should be able to do JOINs in one go. If it's just a text field that happens to contain a value that matches pricebook's ID - you'll need 2 queries.

What do you get when you run this?

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

Could even try to make some variations on the topic if you know the "relationship names"

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

I ended up getting the wanted result with one query:

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}'"""