How to set schema for generating the child-to-parent relationship query
This section demonstrates how to set the module name and schema columns for
generating a child-to-parent relationship query.
The following two child-to-parent relationship query examples will be used for
demonstration purpose in the following steps.
-
SELECT Name, Account.Name, Account.Owner.Name FROM Contact
, a
child-to-parent relationship query with standard object and fields, and -
SELECT Id, FirstName__c, MotherOfDaughter__r.FirstName__c FROM
, a child-to-parent relationship query with custom
Daughter__c
object and fields.Note that here you must use the relationship name with
__r
instead of__c
. For more information, see Understanding Relationship Names, Custom
Objects, and Custom Fields.
-
Set the module name with the name of the object specified in the
FROM
clause,Contact
and
Daughter__c
in above examples. -
Create a column for each field in the field list (separated by commas) after
SELECT
in the schema dialog box.For the first example, you need to create three columns for the three fields
Name
,Account.Name
, and
Account.Owner.Name
.For the second example, you need to create three columns for the three fields
Id
,FirstName__c
, and
MotherOfDaughter__r.FirstName__c
. -
Set the name of each column with the name of each field and replace all dots in
the column name with underscore characters.For the first example, the names of the three columns are set to
Name, Account_Name, and
Account_Owner_Name
.For the second example, the names of the three columns are set to
Id, FirstName__c, and
MotherOfDaughter__r_FirstName__c
. -
Set the type of each column.
The schema for the first example should be set like this:
And the schema for the second example should be set like this:
Note that the underscore character ‘_’ is
used as a separator between the relationship name and the field name in
Talend
schema, so only the underscore character ‘_’ that goes after ‘__r’ or ‘__c’
will be replaced by the dot character ‘.’ when generating the query. If the
underscore character ‘_’ is a part of any custom name in the schema, for
example,Contact_custom_field__c
, which should be
Contact.custom_field__c
in the query, you need to
replace ‘_’ in the generated query with ‘.’ manually.