TYPO3 10 Exbase bidirectional m:n relation sorting

Question

In my extension I have two models team members (parent) and expertise (child). The bidirectional relationsship between the two models is stored in an intermediate table. This works fine bit there is one problem.

When I edit the team member record in the TYPO3 Backend and assign an expertise record to it and then open the corresponding expertise record, the newly assigend team member record is shown at the top of the list instead of at the end. The problem also occurs the other way around.

This is because the sorting respectively sorting_foreign field in the intermediate table is set to its default value (0) when the record is saved.

How can I fix this?

TCA for model team member:

...
'expertise' => [
    'label' => 'Expertise',
    'config' => [
        'type' => 'select',
        'renderType' => 'selectMultipleSideBySide',
        'foreign_table' => 'tx_btp_domain_model_expertise',
        'foreign_table_where' => 'ORDER BY tx_btp_domain_model_expertise.title',
        'MM' => 'tx_btp_team_expertise_mm',
        'maxitems' => 50,
    ],
],
...

TCA for model expertise:

...
'team' => [
    'label' => 'Team',
    'config' => [
        'type' => 'select',
        'renderType' => 'selectMultipleSideBySide',
        'foreign_table' => 'tx_btp_domain_model_team',
        'MM' => 'tx_btp_team_expertise_mm',
        'MM_opposite_field' => 'expertise',
        'maxitems' => 50,
    ],
],
...

Intermediate table definition:

CREATE TABLE tx_btp_team_expertise_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)
);

Answer 1

On Database level you could do something, the solutions I link below are increasing the values based on existing values, so there is no consideration about the pages or records included and that will lead to higher sorting values than when the values are related to single pages only: https://stackoverflow.com/a/3292500/1019850

Another solution on the same page is using a Trigger, that might be a good approach (read my comments there to adjust the outdated code): https://stackoverflow.com/a/3324116/1019850

Either of these code examples could be a base to adjust the proposition to the own needs and optimize it.

On TYPO3 level you could include a function itemsProcFunc which could include the logic to retrieve the existing values and increase the new value only so much like required: https://docs.typo3.org/m/typo3/reference-tca/11.5/en-us/ColumnsConfig/CommonProperties/ItemsProcFunc.html

Additional Info
The reason for common records that new records are sorted in the top is not that the default value is 0. If there exist already sorted records on a page, new records without position are sorted in the top but have already a sorting value assigned. In the beginning the records 1 and 2 had sorting 0, then I sorted manually twice, to get sorting values assigned and to have the same sorting again (they have been sorted by uid, title or timestamp in the beginning) and then I created record 3 which got the sorting value 128 assigned:

The behavior in mm-tables is different:

I can reproduce the described behavior, I had a team record (uid=3) with 3 sorted expertise records (uids: 1,2,3). Then I created a new expertise record and assigned team record 3, the resulting sorting value was 0:

Solution on Database level
Having written a trigger function for MySql I got some promising results and would be glad if it's confirmed to be correct:

--
-- Triggers `tx_btp_team_expertise_mm`
--
DELIMITER $$
CREATE TRIGGER `btp_team_expertise_mm_insert_before` BEFORE INSERT ON `tx_btp_team_expertise_mm` FOR EACH ROW begin

declare uidLocal int(11) unsigned default 0;
declare uidForeign int(11) unsigned default 0;
declare sortingLocal int(11) unsigned default 0;
declare sortingForeign int(11) unsigned default 0;
declare maxSortingLocal int(11) unsigned default 0;
declare maxSortingForeign int(11) unsigned default 0;

-- TEAM (from expertise)
SELECT  new.uid_local into uidLocal;
SELECT  new.sorting into sortingLocal;
SELECT IFNULL(MAX(sorting),0) into  maxSortingLocal FROM tx_btp_team_expertise_mm WHERE uid_local = new.uid_local;

-- EXPERTISE (from team)
SELECT  new.uid_foreign into uidForeign;
SELECT  new.sorting_foreign into sortingForeign;
SELECT IFNULL(MAX(sorting_foreign),0) into maxSortingForeign FROM tx_btp_team_expertise_mm WHERE uid_foreign = new.uid_foreign;

IF maxSortingLocal > 0 AND sortingLocal = 0 THEN
    SET NEW.sorting = maxSortingLocal + 1;
ELSEIF maxSortingLocal = 0 AND sortingLocal = 0 THEN
    SET NEW.sorting = 1;
END IF;

IF maxSortingForeign > 0 AND sortingForeign = 0 THEN
    SET NEW.sorting_foreign = maxSortingForeign + 1;
ELSEIF maxSortingForeign = 0 AND sortingForeign = 0 THEN
    SET NEW.sorting_foreign = 1;
END IF;

end
$$
DELIMITER ;

To test the extension / the trigger you can install a demo and test version from my repository
at best by composer: composer req wdb/trigger-sort-mm-table. Keep in mind that the extension has no further intention than to test or demonstrate the trigger on the tables like described in the question.