Rolf//Thomas

ist ein Internetentwickler bei idee[x] digitale dienste

Intermediate relations with additional data in Typo3

Fahrul Azmi

Initial situation

We have 2 connected extbase models A — M:N — B via a intermediate table. In the TCA they are connected via selectMultipleSideBySide. Our goal is to extend this relation so it can hold more information about the connection.

Current backend

Simple direct relation:

Desired outcome

Adding more information to this relation:

Existing code

Model A

namespace Vendor\Example\Domain\Model;
class A extends \TYPO3\CMS\Extbase\DomainObject\AbstractEntity
{
  /**
    * @var \TYPO3\CMS\Extbase\Persistence\ObjectStorage<\Vendor\Example\Domain\Model\B>
    */
  protected $relationToB = null;

  // getter/setter yada yada yada
}

TCA of Model A

…
config' => [
  'type' => 'select',
  'renderType' => 'selectMultipleSideBySide',
  'foreign_table' => 'tx_example_domain_model_B',
  'MM' => 'tx_example_A_B_mm',
  …
],
…

SQL

CREATE TABLE tx_example_domain_model_A (
-- …
  relation_to_B   int(11) unsigned DEFAULT '0' NOT NULL,
-- …
);
CREATE TABLE tx_example_domain_model_B (
-- …
);
CREATE TABLE tx_example_A_B_mm
(
  uid_local       int(11) unsigned DEFAULT '0' NOT NULL,
  uid_foreign     int(11) unsigned DEFAULT '0' NOT NULL,
  sorting         int(11) unsigned DEFAULT '0' NOT NULL,
  sorting_foreign int(11) unsigned DEFAULT '0' NOT NULL,
  KEY             uid_local (uid_local),
  KEY             uid_foreign (uid_foreign)
);

Necessary conversion to allow data in the Intermediate table

Overview

  1. Change TCA of Model A to field type inline
  2. Add TCA for intermediate table (was not necessary until now)
  3. Extend Intermediate db table with necessary fields (including additional data fields)
  4. Add a model for the relation to use data in extbase
  5. Switch relation field B in Model A to this new relation model
  6. Add repository for relation model
  7. Add class mapping for relation model
  8. Clear every cache you can

Not that difficult isn’t it? 😀 Let’s do this!

1. Change TCA of Model A to field type inline

<?php 
return [
  'columns' => [
    'relation_to_b' => [
      'config' => [
        'type' => 'inline',
        'foreign_table' => 'tx_example_A_B_mm',
        'foreign_field' => 'uid_foreign',
        'foreign_label' => 'uid_local',
        'foreign_selector' => 'uid_local',
        'foreign_sortby' => 'sorting_foreign',
        'foreign_table_field' => 'tablenames',
        'foreign_match_fields' => [
          'fieldname' => 'relation_to_b'
        ]
      ]
    ]
  ]
]

2. Add TCA for intermediate table (was not necessary until now)

File example/Configuration/TCA/tx_example_A_B_mm.php

<?php

return [
    'ctrl' => [
        'title' => 'Relation to B',
        'label' => 'uid_local',
        'label_alt' => '', // actually important to add for display in backend!
        'tstamp' => 'tstamp',
        'crdate' => 'crdate',
        'cruser_id' => 'cruser_id',
        'type' => 'uid_local:type',
        'hideTable' => true,
        'delete' => 'deleted',
        'versioningWS' => true,
        'languageField' => 'sys_language_uid',
        'transOrigPointerField' => 'l10n_parent',
        'transOrigDiffSourceField' => 'l10n_diffsource',
        'rootLevel' => -1,
        'enablecolumns' => [
            'disabled' => 'hidden',
        ],
        'typeicon_classes' => [
            'default' => 'mimetypes-other-other',
        ],
        'security' => [
            'ignoreWebMountRestriction' => true,
            'ignoreRootLevelRestriction' => true,
        ],
        'searchFields' => 'field_1, field_2',
    ],
    'columns' => [
        'sys_language_uid' => [
            'label' => 'LLL:EXT:core/Resources/Private/Language/locallang_general.xlf:LGL.language',
            'config' => [
                'type' => 'language',
            ],
        ],
        'l10n_parent' => [
            'displayCond' => 'FIELD:sys_language_uid:>:0',
            'label' => 'LLL:EXT:core/Resources/Private/Language/locallang_general.xlf:LGL.l18n_parent',
            'config' => [
                'type' => 'group',
                'allowed' => 'tx_example_A_B_mm',
                'size' => 1,
                'maxitems' => 1,
                'default' => 0,
            ],
        ],
        'l10n_diffsource' => [
            'config' => [
                'type' => 'passthrough',
                'default' => '',
            ],
        ],
        'hidden' => [
            'label' => 'LLL:EXT:core/Resources/Private/Language/locallang_general.xlf:LGL.hidden',
            'config' => [
                'type' => 'check',
                'default' => 0,
            ],
        ],
        'uid_local' => [
            'label' => 'B',
            'config' => [
                'type' => 'group',
                'size' => 1,
                'maxitems' => 1,
                'allowed' => 'tx_example_domain_model_B',
                'hideSuggest' => true,
            ],
        ],
        'uid_foreign' => [
            'label' => 'Used by content elements',
            'config' => [
                'type' => 'input',
                'size' => 10,
                'eval' => 'int',
            ],
        ],
        'tablenames' => [
            'label' => 'Tablename',
            'config' => [
                'type' => 'input',
                'size' => 30,
                'eval' => 'trim',
            ],
        ],
        'fieldname' => [
            'label' => 'Foreign Field',
            'config' => [
                'type' => 'input',
                'size' => 30,
            ],
        ],
        'sorting_foreign' => [
            'label' => 'Sorting foreign',
            'config' => [
                'type' => 'input',
                'size' => 4,
                'max' => 4,
                'eval' => 'int',
                'default' => 0,
            ],
        ],
        'table_local' => [
            'label' => 'Local table',
            'config' => [
                'type' => 'input',
                'size' => 20,
                'default' => 'tx_example_domain_model_B',
            ],
        ],
        'example_field_1' => [
            'exclude' => true,
            'label' => 'Example select field',
            'config' => [
                'type' => 'select',
                'renderType' => 'selectSingle',
                'items' => [
                    ['None','0'],
                    ['Item 1','1'],
                    ['Item 2','2'],
                    ['Item 3','3'],
                ],
                'default' => 0,
            ],
        ],
        'example_field_2' => [
            'exclude' => true,
            'label' => 'Example toggle field',
            'config' => [
                'type' => 'check',
                'renderType' => 'checkboxToggle',
            ],
        ],
    ],
    'types' => [
        '0' => [
            'showitem' => '
            --palette--;;basicoverlayPalette,
            --palette--;;filePalette',
        ],
    ],
    'palettes' => [
        'basicoverlayPalette' => [
            'label' => 'Model B (Additional intermediate fields)',
            'showitem' => 'example_field_1, --linebreak--, example_field_2',
        ],
        // File palette, hidden but needs to be included all the time
        'filePalette' => [
            'showitem' => 'uid_local, hidden, sys_language_uid, l10n_parent',
            'isHiddenPalette' => true,
        ],
    ],
];

3. Extend Intermediate db table with necessary fields (including additional data fields)

CREATE TABLE tx_example_A_B_mm
(
    uid             int(11) NOT NULL auto_increment, -- new!
    pid             int(11) DEFAULT '0' NOT NULL, -- new!

    uid_local       int(11) unsigned DEFAULT '0' NOT NULL,
    uid_foreign     int(11) unsigned DEFAULT '0' NOT NULL,
    tablenames      varchar(64) DEFAULT '' NOT NULL, -- new!
    fieldname       varchar(64) DEFAULT '' NOT NULL, -- new!
    sorting         int(11) unsigned DEFAULT '0' NOT NULL,
    sorting_foreign int(11) unsigned DEFAULT '0' NOT NULL,
    table_local     varchar(64) DEFAULT '' NOT NULL, -- new!

    # Local usage overlay fields
    example_field_1 int(11) unsigned DEFAULT '0' NOT NULL, -- new!
    example_field_2 tinyint(4) unsigned DEFAULT '0' NOT NULL, -- new!

    PRIMARY KEY     (uid),
    KEY             tablenames_fieldname (example_field_1(32),example_field_2(12)), -- new!
    KEY             uid_local (uid_local),
    KEY             uid_foreign (uid_foreign)
);

I had to flip the existing values from tx_example_A_B_mm.uid_local and ….uid_foreign for some reason.
You can do this by the following SQL Statement:

UPDATE tx_example_A_B_mm SET uid_local=(@temp:=uid_local), uid_local = uid_foreign, uid_foreign = @temp;

4. Add a model for the relation to use data in extbase

<?php
namespace Vendor\Example\Domain\Model;

class RelationToB extends \TYPO3\CMS\Extbase\DomainObject\AbstractEntity
{
  /**
    * @var \Vendor\Example\Domain\Model\B
    */
  protected $relationToB = null;

  /**
    * exampleField1
    *
    * @var int
    */
  protected $exampleField1 = 0;

  /**
    * exampleField2
    *
    * @var boolean
    */
  protected $exampleField2 = false;

// add getter and setting functions…

}

5. Switch relation field B in Model A to this new relation model

  // change every ocurrence of:
  * @var \TYPO3\CMS\Extbase\Persistence\ObjectStorage<\Vendor\Example\Domain\Model\B>
  // to:
  * @var \TYPO3\CMS\Extbase\Persistence\ObjectStorage<\Vendor\Example\Domain\Model\RelationToB>

6. Add repository for relation model

In example/Classes/Domain/Repository/RelationToBRepository.php

<?php
namespace Vendor\Example\Domain\Repository;
class RelationToBRepository extends \TYPO3\CMS\Extbase\Persistence\Repository
{
}

7. Add class mapping for relation model

Tell extbase how resolve the new relation in example/Configuration/Extbase/Persistence/Classes.php

<?php
declare(strict_types=1);
return [
    \Vendor\Example\Domain\Model\RelationToB::class => [
        'tableName' => 'tx_example_A_B_mm',
        'properties' => [
            'relation_to_b' => [
                'fieldName' => 'uid_local',
            ],
        ],
    ],
];

8. Clear every cache you can