Skip to content

XAware Community

Get the Flash Player to see this player.
Flash Image Rotator Module by Joomlashack.
XAware 5.6
Need Help
Webinars and Events
Advanced Tutorials
Webinars and Events

You are here: Home arrow Blogs

Untagged  19 Jan 2009 8:22 PM
avatar
Salesforce BizComp JOINS by tferguson
Over the last week I have been working on enhancing the Salesforce BizComp to handle the return from a query that involves JOINS.  Unlike an RDBMS, Salesforce does not allow JOINS on between any two objects, they must have a pre-established relationship.  Relationships in Salesforce are heirarchical, so there is always a parent which can have many children, but the children only have oneparent record.  If you want to learn more about this, http://wiki.apexdevnet.com/index.php/Web_Services_API#API  is a good starting point to learn the SOQL API.

 In general there are two types of JOINS that can be issued, a parent to child, and a child to parent.  The formers syntax involves nested queries and the latter simply uses dot notation.

An example parent to child query would be: SELECT Name, (SELECT LastName, FirstName FROM Contacts) FROM Account.  This query selects each Account object and the LastName and FirstName from each Contact that is associated with it.  Notice the nested SELECT actually comes from the relationship name defined in the Account object (Contacts) but it returns objects of type Contact - this is important to remember when we go to define the xa:row_template(s).    The challenge here is that there can be multiple children returned for each parent record, and the nesting can occur up to 20 times.  Up until this week the Salesforce BizComp only allowed for the definition of one xa:row_template to handle the parent object, as well as it did not handle the child objects if left to generate the return xml without an xa:row_template.

There are two ways that the results from a query can be returned, one is to auto-generate xml to be returned and the other is to define xa:row_template(s) and define custom xml.  In the first case, the xml that is generated will Use the parent object name as the root element, each field will have an element corresponding to it's name with the text of the element the value from the field, then a child element named after the relationship (in this case Contacts) with repeating children with the name of the child object, Contact.  In this case the xml structure would be:
  <Account>
    <Name/>
    <Contacts>
      <Contact>
        <LastName/>
        <FirstName/>
      </Contact>
    </Contacts>
  </Account>

 The other option is to define nested xa:row_templates, with each one having an attribute of xa:type="".  In general each type of object being returned (i.e. Account and Contact) must have a corresponding xa:row_template.  So for this example you might define the following:

     <xa:row_template xa:type="Account">
         <name>%Name% </name>
         <contacts>
             <xa:row_template xa:type="Contact">
                 <contact>
                     <lname>%LastName% </lname>
                     <fname>%FirstName% </fname>
                 </contact>
             </xa:row_template>
         </contacts>
    </xa:row_template>

One other rule to keep in mind is that if Contact objects are the children of the Account object, then the xa:row_template for Contact must be nested within the Account xa:row_template.

An example child to parent query would be:  SELECT LastName, FirstName, Account.Name FROM Contact.  This query selects each Contact's FirstName and LastName along with the name of the Account it belongs to.  Since it can only belong to one account there is no need for the nested query syntax and the name of the parent Object is the left side of the dot.

If you want to have the xml returned from this auto-generated then it will simply create the top level element as the name of the Object returned and each field as child elements, including the parent.field(s).  For example for the above query the result would be:

<Contact>
        <LastName/>
        <FirstName/>
       <Account.Name/>
</Contact>

To define custom xml to be returned nested xa:row_tempates are not needed, only one is needed and the parent fields are just referred to by using the dot notation.  For example:

    <xa:row_template>
        <contact>
            <lname>%LastName%</lname>
            <fname>%FirstName%</fname>
            <account>%Account.Name%</account>
        </contact>
    </xa:row_template>

 So, the good news is that as of today daily builds are including the code for the bizcomp to handle JOINS, and Release 5.3 scheduled in mid February will contain this code.  The bad news is that the wizard will not support building a JOIN query  both on the builder page and the response mapping page.  Hopefully this will be in the 5.4 release, but it has not been officially scheduled at this point.



Trackback(0)
Comments (2)add comment

Write comment
You must be logged in to a comment. Please register if you do not have an account yet.

busy

Get the Flash Player to see this player.
Flash Image Rotator Module by Joomlashack.
Commercial
Free Training
QuickStart Packages
Image 4 Title
Image 5 Title

Polls

Which data source and BizComponent combinations do you most frequently use?
 

Blogs

Recent Entries

Visit XAware.com