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 Account, a parent-to-child relationship query with standard object and fields, and
SELECT LastName__c, (SELECT FirstName__c FROM Daughters__r) FROM Mother__c, a parent-to-child relationship query with custom 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.

Procedure

Set the module name with the name of the object specified in the outer query FROM clause, Account and Mother__c in above examples.
Create a column for each field (inclulding the fields in subquery) after SELECT in the schema dialog box.

For the first example, you need to create three columns for the three fields, including two fields Name and Owner.Name after the outer SELECT, and one field CreatedBy.Name after the subquery SELECT.

For the second example, you need to create two columns for the two fields, including the field LastName__c after the outer SELECT, and the field FirstName__c after the subquery SELECT.
For the fields in the outer SELECT clause, 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 outer SELECT clause 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 subquery FROM clause, 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__c in the query, you need to replace '_' in the generated query with '.' manually.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here