This section discusses some of the features and restrictions that are specific to how the component supports joins. If possible, the CData FireDAC Components for FinancialForce attempts to perform joins server side. Joins that FinancialForce cannot process are performed client side when SupportEnhancedSQL is set to true. The CData FireDAC Components for FinancialForce supports server-side joins based on Salesforce Object Query Language (SOQL). The component supports standard SQL syntax instead of proprietary SOQL to allow easy integration with a wide variety of SQL tools. Join queries in FinancialForce are based on the relationships among FinancialForce objects.
FinancialForce objects can be linked using relationships. The standard FinancialForce objects have predefined relationships. You can define relationships for your custom objects.
Parent to Child Relationships
FinancialForce relationships are directional and are of the following types: one-to-many (parent to child) or many-to-one (child to parent). Since the relationships are directional the order in which the tables are included in the query determines the path of relationship traversal.
The following query shows a simple parent-to-child join query. This query returns all Accounts and the first and last name of each Contact associated with that Account.
SELECT Contact.FirstName, Account.Name FROM Account, Contact
FinancialForce relationships can be polymorphic. That is, a given relationship on a field can refer to more than one type of entity. For example, the Task entity contains a Who relationship, which by default may refer to a Contact or Lead.
The following query shows a join based on a polymorphic relationship. To return only Tasks referring to a Contact on the Who relationship, specify the type of entity in the relationship:
SELECT Task.Subject, Contact.Name FROM Task, Contact WHERE Contact.Type='Contact'
You can specify a join condition that is a custom relationship. The following query retrieves the names of all Account records and the first names of all Contacts that match the specified join condition:
Select Contact.Firstname, Account.Name FROM Account JOIN Contact ON Account.MyCustomColumn__c = Contact.Id
Server-Side Join Syntax
You can use the syntax detailed below to execute joins on FinancialForce objects that are processed by the FinancialForce servers. Joins on related objects are processed on the server. A benefit of predefined relationships is that you do not need to specify the join conditions to execute a join with FinancialForce data; the conditions are already accounted for based on the relationship.
The following query returns the first names of all the Contacts in the organization and for each Contact the name of the parent Account associated with that Contact.
Select Contact.Firstname, Account.Name FROM Contact, Account
If there are multiple relationships between the tables, you can explicitly set the join criteria. The following query matches on a custom relationship determined by the columns in the join criteria (Account.MyCustomColumn__c and Contact.Id), instead of the default parent-to-child relationship between Accounts and Contacts:
Select Contact.Firstname, Account.Name FROM Account JOIN Contact ON Account.MyCustomColumn__c = Contact.IdFinancialForce supports inner joins. The following query retrieves all Account records that are associated with an Opportunity:
SELECT Account.Id, Account.Name, Account.Fax, Opportunity.AccountId, Opportunity.CloseDate FROM Account INNER JOIN Opportunity ON Account.Id = Opportunity.AccountIdThe following query shows a join between three custom objects. This query joins the custom tables NW_Product__c, NW_Category__c, and NW_Suppliers__c and returns the Name field from each of them. Additionally, the results from the NW_Category__c table are filtered for names that start with "Dairy":
SELECT A.Name, B.Name, C.Name FROM NW_Product__c as A, NW_Category__c as B, NW_Suppliers__c as C WHERE B.Name LIKE 'Dairy%'