4

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?

4
  • Could your malformed query be because of weird apostrophe signs? Is it only a formatting problem here or did you really use something that's not '? – eyescream Jun 4 '13 at 6:34
  • I have probably found the solution but it may give some weird results that needs parsing. Like Executing the following: sfdc.getChild("DataBases__r"); will result into xmlobjects and getting those to be parsed again. Please let me know if I'm proceeding in right way or there is any other solution for this? P.S: Here sfdc represents the SObject while the Databases__r is one of the child for the current object to be queried. – sanchit Jun 4 '13 at 7:19
  • Can you please vote me up for 2 more points! As its not possible as I'm 8 pointer in this forum. I need to represent you what exactly I'm getting in the results and my thoughts regarding it. Or let me know if any other solution for representing the output to you guys? – sanchit Jun 4 '13 at 7:30
  • Done ;) In salesforce.com/us/developer/docs/dbcom_soql_sosl/Content/… find the text that starts with "Subquery results are like regular query results" for some hints. Check boards.developerforce.com/t5/NET-Development/… too, it might help. – eyescream Jun 4 '13 at 8:21
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?

0

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, value=acb@xyz.com, 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, value=abc@xyz.com, 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.

8
  • 1
    What's your language? Chances are there's some library built for you already that uses Partner API. wiki.developerforce.com/page/Tools, scroll down to "Language Integration Libraries" (or wiki.developerforce.com/page/Java directly for example). But roughly speaking yes, I'd say that's the way to parse it... – eyescream Jun 4 '13 at 10:00
  • I'm using Java to have the library available from the Partner WSDL. I'm not able to find any generalized code to fetch records from different objects. I need to confirm if there is any other library instead of token based or any other conversion from this library to some standard one? Its complex even for 2 Junction object records. – sanchit Jun 4 '13 at 10:10
  • Do you have to use Partner API? Enterprise API (strongly typed) would be much easier for you but it'll tightly couple to current state of your SF org. You'll be able to generate lots of useful Java classes for mapping to Account, Contact etc... but everytime a new field / object will be added you'll have to regenerate part of your code (from WSDL file) in order to "see" it. – eyescream Jun 4 '13 at 10:30
  • Yes, my previous application is completely relying on Enterprise API. One of the 2 parts to make it independent of Enterprise has been done. While the other part which is I'm dealing with is doing some relationship queries records. Yes, I'm also going to do code modifications in order to sense the Objects and Fields modifications in Salesforce. – sanchit Jun 4 '13 at 10:43
  • checkout WSC for use from java, it provides a much nicer API – superfell Aug 3 '13 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.