How to set schema for generating the parent-to-child relationship query
This section demonstrates how to set the module name and schema columns for
generating a parent-to-child relationship query.
The following two parent-to-child relationship query examples will be used for
demonstration purpose in the following steps.
-
SELECT Name, Owner.Name (SELECT CreatedBy.Name FROM Notes) FROM, a parent-to-child relationship query with standard object
Account
and fields, and -
SELECT LastName__c, (SELECT FirstName__c FROM Daughters__r) FROM, a parent-to-child relationship query with custom object
Mother__c
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 outer query
FROMclause,Accountand
Mother__cin above examples. -
Create a column for each field (inclulding the fields in subquery) after
SELECTin the schema dialog box.For the first example, you need to create three columns for the three fields,
including two fieldsNameandOwner.Name
after the outerSELECT, and one field
CreatedBy.Nameafter the subquery
SELECT.For the second example, you need to create two columns for the two fields,
including the fieldLastName__cafter the outer
SELECT, and the fieldFirstName__c
after the subquerySELECT. -
For the fields in the outer
SELECTclause, which are outside
parentheses, 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 column names for the two fields
Name and Owner.Name in the
outerSELECTclause are set to Name
and Owner_Name.For the second example, there is no dot in the field name, so the column name
is same as the field name. -
For the fields in the subquery
SELECT, construct the column
names using the pattern<$XXX>_records_<$YYY>, where
<$XXX>corresponds to the name of the object specified
in the subqueryFROMclause, and<$YYY>
will be the field name with all dots replaced by underscore characters.For the first example, the column name for the field
CreatedBy.Name in the subquery is set to
Notes_records_CreatedBy_Name.For the second example, the column name for the field
FirstName__c in the subquery is set to
Daughters__r_records_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__cin the query, you need to
replace ‘_’ in the generated query with ‘.’ manually.