JOIN with SOQL in Python

Question

Issue: Using the documentation linked below, I tried inner joining two SOQL tables together without success. Can anyone help me figure out what's wrong? Assuming it has to do with the syntax (i.e. parenthesis). Thanks in advance!

Documentation for Joins:

https://developer.salesforce.com/blogs/developer-relations/2013/05/basic-soql-relationship-queries.html

Python SOQL Code:

records =   sf.query_all('SELECT Event__c.Id, Event__c.Supplier__c,' +
                         ' Event__c.Promo_Models__c, Event__c.Lead_Promo_Model_Name__c,' +
                         ' Event__c.Event_Date__c, Consumer_Feedback__c,' +
                         ' (SELECT Contact.Gender__c, Contact.Ethnicity__c, Contact.CreatedDate FROM Contact)' +
                         ' FROM Event__c' +
                         ' WHERE Event__c.Lead_Promo_Model__c IN (SELECT Contact.Id FROM Contact)' +
                         ' AND Supplier__c LIKE \'ABCCompany%\'' +
                         ' AND Event_Date__c > 2019-06-30' + 
                         ' AND Event_Date__c < 2020-07-01', include_deleted=False)

df_sf = pd.DataFrame(records['records'])

Error:

File "", line 10 ' WHERE Event__c.Lead_Promo_Model__c IN ('SELECT Contact.Id FROM Contact')' + ^ SyntaxError: invalid syntax

For starters, it should be IN (SELECT Id FROM Contact), no? I can't promise that's your only issue though. — Abraham Labkovsky
Similar question are more frequently answered on a specialized salesforce.stackexchange.com because a Python context is here marginal. If you would next time replace '...' + '...\'a\'...' by one """SELECT multi-line... 'a' ...""" without backslashes and then it could be answered also by people that don't use Python. — hynekcer
It is also more probable that a question would be answered if the parts that are not important for the problem are removed or if you replace some custom fields by native fields. — hynekcer

hynekcer hynekcer · Accepted Answer · 2020-06-24T12:24:49

If a relationship from a builtin object to a custom object is used then you need to read an example on this documentation page Understanding Relationship Names, Custom Objects, and Custom Fields.

You need to find the field definition of your custom field Event__c.Lead_Promo_Model__c that has a relationship to Contact (this relationship is a consequent of your WHERE condition) and read the line "Related List Label" and "Child Relationship Name". Both are probably "Contacts" by default, but the first can be any string and the second can be any API name. Then you add "__r" to the item "Child Relationship Name".

You replace (SELECT Contact.Gender__c,... FROM Contact) by (SELECT Contact.Gender__c,... FROM Contact__r). See the Contact__r replaced. That's all.

I expect that all other parts are correct or could be easily fixed by you. Nobody other knows your custom fields names. You can verify that everything except the nested SELECT works, then to add a subquery with native fields ... (SELECT Contact.Id FROM Contacts__r) ... and finally also custom fields of Contact.

The second screenshot in the documentation is little obsoleted without a "Related List Label", but it is not important for SOQL.

JOIN with SOQL in Python

1 Answers