TYPO3 - How can I sort a record by a field of a foreign_table and not by it's uid?

'passwordtype' => [
        'exclude' => false,
        'label' => '###label####',
        'config' => [
            'type' => 'select',
            'renderType' => 'selectMultipleSideBySide',
            'foreign_table' => 'passwordtypes',
            'foreign_sortby' => 'name',
        ],
    ],

That part of the tca works like a charm. The passwordtypes are getting sorted by name when I open the record of a password in the backend.

But I want the sorting of the passwordrecords also happening by the passwordtypes. So I tried 'sortby' => 'passwordtype' under 'ctrl' but that only sorts it by the uid, not by the name of the passwordtypes.

I also tried 'sortby' => 'passwordtype.name' but that creates an error. Is it possible to sort it by the name of passwordtypes instead of the uid? It clearly has the name of the passwordtypes already in the passwordlist because when I change the 'label' under 'ctrl' to 'passwordtype' than I can see the correct names and not uids.

I'm open for every idea even if I have to change my database-structure.

Solution

Let's dive into the core...

The record lists are generated via \TYPO3\CMS\Recordlist\Controller\RecordListController::main(). Fot getting the list itself, the method calls \TYPO3\CMS\Recordlist\RecordList\DatabaseRecordList::generateList().

generateList() does select data only from the actual table itself. No relations are resolved here. Then it becomes clearer why sorting by fields of a relation is not possible: they are not part of the selected data.

But... The labels of foreign records are shown.

Yes, that's done while rendering a concrete row of the list. At that moment, no sorting can be applied anymore.
But... It works for sorting by UIDs.

Yes and no. You have a 1:n relation, so the UID of the foreign record is saved as foreign_key in your (passwordrecords) table. The 'sortby' => 'passwordtype' is not applied to the table passwordtype but to the column passwordrecords.passwordtype containing these UIDs.

Conclusion:

Out of the box, there seems not to be an option for sorting by a foreign table field.

Maybe, you can hook into the recordlist by \TYPO3\CMS\Backend\RecordList\RecordListGetTableHookInterface::getDBlistQuery() (called in \TYPO3\CMS\Recordlist\RecordList\DatabaseRecordList::getTable) and modify the query parts to meet your needs.