Querying Related objects using Partner Api

Question

I'm experimenting with relationship queries using the partner wsdl. The docs say:

"The partner WSDL does not contain the detailed type information available in the enterprise WSDL to get the information you need for a relationship query. You must first execute a describeSObjects call, and from the results, gather the information you need to create your relationship query:

• The relationshipName value for one-to-many relationships, for example, in an account object, the relationship name for the asset child is Assets.
• Use the reference fields available for the relevant object, for example, whoId, whatId, or ownerIdon a lead, case, or custom object."

I can't make this into sense. Let's make things specific so they are easier to follow. Say I want to run one of the example queries relating accounts and contacts from the docs. describeSObject on Account gives me a childRelationship[] that shows me that I've got one relating AccountId and Contact called "Contacts". So how do I build this up into a query? Where do I put my relationshipName? It doesn't seem to be included in any of the examples. An example in the docs:

SELECT Id, FirstName, LastName, AccountId, Account.Name 
FROM Contact 
WHERE Account.Name LIKE ’Acme%’

I can't run this query, because I get a MALFORMED_SQL error about not being able to query relationships.

That note at the end of the doc page quote above makes me think that I can, if only I could build up thequery correctly. Any advice?

Answer 1

Before you deep dive into Describe calls and SOQL you might want to catch up reading about relationships in general. It's a long read and you'll probably frown that you know most of it already but it'll make your knowledge bit more systematic. You might also want to play with queries in Force Explorer, Eclipse or similar SOQL tool. That way you'll know what's the result you're after and describes will only get you there with proper column and rleationship names.

If you have a background with normal relational databases (LEFT JOIN etc) - it all depends from which side you'll start the join.

This will give you all Contacts (including those without Account):

SELECT Id, FirstName, LastName, Email, AccountId, Account.Name FROM Contact

This will give you all Accounts (including these that don't have any Contacts)

SELECT Id, Name, (SELECT Id, FirstName, LastName, Email FROM Contacts)

By applying WHERE clause you can limit the results so the end result could be similar in both cases (all depends on which side of relation you're more interested in...).

Check these for more goodies: How can i get the id of the max record?, REST API query to get all accounts which have child records?

Answer 2

I have probably found the solution but it may give some weird results that needs parsing.

For the soql query:

SELECT Account_Description__c, Account_Type__c, CreatedById, CreatedDate, IsDeleted, Email_Notifications_BCC__c, Email_Notifications_CC__c, LastModifiedById, LastModifiedDate, OwnerId, Password__c, Id, Name, Security_Token__c, SystemModstamp, sfdc_Token__c, (SELECT Access_Database__c, Access_Database__r.Name, Access_Database__r.ID, Salesforce_User_Instance__c, Salesforce_User_Instance__r.Name FROM Salesforce_Instance_With_DataBases__r) FROM sfdcInformation__c 

Executing the following:

sfdc.getChild("Salesforce_Instance_With_DataBases__r");

where sfdc is the SObject reference.

will result into something like:

XmlObject{name={urn:sobject.partner.soap.sforce.com}Salesforce_Instance_With_DataBases__r, value=null, 
    children=[
        XmlObject{name={urn:partner.soap.sforce.com}done, value=true, children=[]}, 
        XmlObject{name={urn:partner.soap.sforce.com}queryLocator, value=null, children=[]}, 

        XmlObject{name={urn:partner.soap.sforce.com}records, value=null, 
            children=[
                XmlObject{name={urn:sobject.partner.soap.sforce.com}type, value=Salesforce_Instance_With_DataBase__c, children=[]}, 
                XmlObject{name={urn:sobject.partner.soap.sforce.com}Id, value=null,children=[]}, 
                XmlObject{name={urn:sobject.partner.soap.sforce.com}Access_Database__c, value=a05G00010092CvK, children=[]}, 
                XmlObject{name={urn:sobject.partner.soap.sforce.com}Access_Database__r, value=null, 
                    children=[  
                        XmlObject{name={urn:sobject.partner.soap.sforce.com}type, value=AccessDBs__c, children=[]}, 
                        XmlObject{name={urn:sobject.partner.soap.sforce.com}Id, value=a05G00021092CvK, children=[]}, 
                        XmlObject{name={urn:sobject.partner.soap.sforce.com}Name, value=D:\DATA\DPPI\DATA\DPPI\DPPIPR01, children=[]}, 
                        XmlObject{name={urn:sobject.partner.soap.sforce.com}Id, value=a05G00020092DvK, children=[]}
                    ]
                }, 

                XmlObject{name={urn:sobject.partner.soap.sforce.com}Salesforce_User_Instance__c, value=a00G020002MEnauIAD, children=[]}, 
                XmlObject{name={urn:sobject.partner.soap.sforce.com}Salesforce_User_Instance__r, value=null, 
                    children=[
                        XmlObject{name={urn:sobject.partner.soap.sforce.com}type, value=sfdcInformation__c, children=[]}, 
                        XmlObject{name={urn:sobject.partner.soap.sforce.com}Id, value=null, children=[]}, 
                        XmlObject{name={urn:sobject.partner.soap.sforce.com}Name, [email protected], children=[]}
                    ]
                }
            ]
        }, 

        XmlObject{name={urn:partner.soap.sforce.com}records, value=null, 
            children=[
                XmlObject{name={urn:sobject.partner.soap.sforce.com}type, value=Salesforce_Instance_With_DataBase__c, children=[]}, 
                XmlObject{name={urn:sobject.partner.soap.sforce.com}Id, value=null, children=[]}, 
                XmlObject{name={urn:sobject.partner.soap.sforce.com}Access_Database__c, value=a05G0100009283sIAA, children=[]}, 
                XmlObject{name={urn:sobject.partner.soap.sforce.com}Access_Database__r, value=null, 
                    children=[
                        XmlObject{name={urn:sobject.partner.soap.sforce.com}type, value=AccessDBs__c, children=[]}, 
                        XmlObject{name={urn:sobject.partner.soap.sforce.com}Id, value=a05G0000003283sIAA, children=[]}, 
                        XmlObject{name={urn:sobject.partner.soap.sforce.com}Name, value=D:\DATA\6011\DATA\6011\6011PR01, children=[]}, 
                        XmlObject{name={urn:sobject.partner.soap.sforce.com}Id, value=a05G0000019283sIAA, children=[]}
                    ]
                }, 
                XmlObject{name={urn:sobject.partner.soap.sforce.com}Salesforce_User_Instance__c, value=a00G000030MEnauIAD, children=[]}, 
                XmlObject{name={urn:sobject.partner.soap.sforce.com}Salesforce_User_Instance__r, value=null, 
                    children=[
                        XmlObject{name={urn:sobject.partner.soap.sforce.com}type, value=sfdcInformation__c, children=[]}, 
                        XmlObject{name={urn:sobject.partner.soap.sforce.com}Id, value=null, children=[]}, 
                        XmlObject{name={urn:sobject.partner.soap.sforce.com}Name, [email protected], children=[]}
                    ]
                }
            ]
        }, 

        XmlObject{name={urn:partner.soap.sforce.com}size, value=2, children=[]}
    ]
}

We need to parse this to get the child records accordingly with the help of XmlObject reference returned having the methods like:

1. getChildren(): Iterator<XmlObject> SObject
2. getName(): QName XmlObject

And I think we need to parse these token based response using next() and hasNext().

Please let me know if I'm proceeding in the right way or there is any other solution?

EDIT If it is the only way around. How can we make sure for different object structures some general scenario is possible.

PS: In this case the records of the Junction Object with relationship name Salesforce_Instance_With_DataBases__r is queried.