Substructures and child records
The substructureFields property makes it possible to read nested structures in the imported data and the children property allows for the creation of child (IRRE) records. Each one is a bit hairy to use properly and the combination of both may melt your brain. Hopefully this example can help avoid such a crash.
This example is based on the import of orders from the External Import test extension.
The orders
The orders table contains only a few fields. The most interesting one is products,
which is of type "inline" and describes the items in the order, each with a reference to
a product and a quantity.
Here is the TCA for the column:
'products' => [
'exclude' => 0,
'label' => 'Products',
'config' => [
'type' => 'inline',
'foreign_table' => 'tx_externalimporttest_order_items',
'foreign_selector' => 'uid_foreign',
'foreign_label' => 'uid_foreign',
'foreign_field' => 'uid_local',
'foreign_sortby' => 'sorting_foreign',
'minitems' => 1,
'maxitems' => 9999,
'appearance' => [
'useSortable' => true,
],
],
],and the relevant columns in the tx_ table:
'uid_foreign' => [
'exclude' => 0,
'label' => 'Product',
'config' => [
'type' => 'select',
'renderType' => 'selectSingle',
'foreign_table' => 'tx_externalimporttest_product',
'foreign_table_where' => 'ORDER BY tx_externalimporttest_product.name',
'maxitems' => 1,
],
],
'quantity' => [
'exclude' => 0,
'label' => 'Quantity',
'config' => [
'type' => 'number',
],
],The data
The data to import presents itself as a JSON structure. An order contains several items,
each item refering to a product by its code and a quantity. For the sake of the
example, the quantity is a string rather than a number. Below is an extract, the
full file can be found in EXT:.
[
{
"order": "000001",
"date": "2014-08-07",
"customer": "Conan the Barbarian",
"items": [
{
"product_code": "000001",
"qty": "03"
},
{
"product_code": "000005",
"qty": "01"
},
{
"product_code": "000101",
"qty": "10"
},
{
"product_code": "000102",
"qty": "02"
}
]
},
{
"order": "000002",
"date": "2014-08-08",
"customer": "Sonja the Red",
"items": [
{
"product_code": "000001",
"qty": "01"
},
{
"product_code": "000005",
"qty": "02"
},
{
"product_code": "000202",
"qty": "01"
}
]
}
]Reading the structure
The orders being a nested structure, they can't be read as a single value. Instead, we need to use the substructureFields property, which makes it possible to loop on such a nested structure and extract one or more values from each. Let's look just at the "reading" part of the configuration
'products' => [
...
'external' => [
0 => [
'field' => 'items',
'substructureFields' => [
'products' => [
'field' => 'product_code',
],
'quantity' => [
'field' => 'qty',
],
],
...
],
],
],What this code does, is that the items field is read for each order. The
value being an array, the substructure property loops on each entry
and extracts the product_ (putting it into the products field)
and qty values (putting it into the quantity field).
In effect, this creates one row per item in the order within the array containing the data to import. In database terms, this corresponds to a denormalization. Considering the above JSON structure, the result can be represented like this:
| order | date | customer | products | quantity |
|---|---|---|---|---|
| 000001 | 2014-08-07 | Conan the Barbarian | 000001 | 03 |
| 000001 | 2014-08-07 | Conan the Barbarian | 000005 | 01 |
| 000001 | 2014-08-07 | Conan the Barbarian | 000101 | 10 |
| 000001 | 2014-08-07 | Conan the Barbarian | 000102 | 02 |
| 000002 | 2014-08-08 | Sonja the Red | 000001 | 01 |
| 000002 | 2014-08-08 | Sonja the Red | 000005 | 02 |
| 000002 | 2014-08-08 | Sonja the Red | 000202 | 01 |
Warning
When using substructure in conjunction with a children configuration,
it is very important that the IRRE column (products in this case) contains
the value that the child record relates to (the product code in this case). This is what
enables the TYPO3 Core Engine to create the relation properly between parent and children.
Applying transformations
For the products field, we want to map it to the products table. This is a
straightforward mapping transformation:
'products' => [
...
'external' => [
0 => [
'field' => 'items',
'substructureFields' => [
'products' => [
'field' => 'product_code',
],
'quantity' => [
'field' => 'qty',
],
],
'transformations' => [
10 => [
'mapping' => [
'table' => 'tx_externalimporttest_product',
'referenceField' => 'sku',
],
],
],
...
],
],
],But what about the quality field which was automatically created when reading
the substructure? It is also possible to put it through transformations, but transformations
cannot be directly attached to the substructure definition. Instead, we have to use
additional fields. We declate quality
as an additional field and read some dummy value into it. That value will be overridden
during the substructure processing. During the transformation step, the value coming
from the substructure will go through whatever transformation was declared. Here's the
corresponding code:
'external' => [
'general' => [
...
],
'additionalFields' => [
0 => [
'quantity' => [
// Fill with any dummy (but existing) value
'field' => 'order',
'transformations' => [
10 => [
'userFunction' => [
'class' => Transformation::class,
'method' => 'castToInteger',
],
],
],
],
],
],
],The transformation is simply - as its name makes clear - a casting of string to integer.
Creating child records
So what do we do now with our denormalized structure?
In this scenario, we want to create the IRRE (inline) structure of order items within
each order. For this, we need the children property.
The children configuration defines the table for which to create entries and
the values to attribute to each column of that table. The values come from the fields
available in the data to import, hence products and quantity in our case.
Because the children property expects the creation of multiple children, it
knows how to handle the denormalization caused by reading the substructure. It will
look to the referenceUid
column to tell which entries are repeating and are thus associated with the same parent.
Putting it all together, the configuration for the products column looks like this
(lines related to the children property are highlighted):
'products' => [
'exclude' => 0,
'label' => 'Products',
'config' => [
'type' => 'inline',
'foreign_table' => 'tx_externalimporttest_order_items',
'foreign_selector' => 'uid_foreign',
'foreign_label' => 'uid_foreign',
'foreign_field' => 'uid_local',
'foreign_sortby' => 'sorting_foreign',
'minitems' => 1,
'maxitems' => 9999,
'appearance' => [
'useSortable' => true,
],
],
'external' => [
0 => [
'field' => 'items',
'substructureFields' => [
'products' => [
'field' => 'product_code',
],
'quantity' => [
'field' => 'qty',
],
],
'transformations' => [
10 => [
'mapping' => [
'table' => 'tx_externalimporttest_product',
'referenceField' => 'sku',
],
],
],
'children' => [
'table' => 'tx_externalimporttest_order_items',
'columns' => [
'uid_local' => [
'field' => '__parent.id__',
],
'uid_foreign' => [
'field' => 'products',
],
'quantity' => [
'field' => 'quantity',
],
],
'controlColumnsForUpdate' => 'uid_local, uid_foreign',
'controlColumnsForDelete' => 'uid_local',
],
],
],
],