Issue Bug Closed

#102298SQL JOIN in inherited SuggestWizardReceiver leeds to SQL Exception

Achim Fritz 10/31/23, 15:12 updated 07/09/24, 15:19

Description

the “SuggestWizardDefaultReceiver” is inteded to be inherited from other Implementation of Ajax Suggest Receiver (s. https://github.com/TYPO3/typo3/blob/main/typo3/sysext/backend/Classes/Form/Wizard/SuggestWizardDefaultReceiver.php#L33)

if we extend it and add a JOIN to an other table it leeds to an SQL Exception

(1/3) Doctrine\DBAL\Exception\NonUniqueFieldNameException

An exception occurred while executing 'SELECT COUNT(`uid`) FROM `mmn_person_function_unit` INNER JOIN `domain_model_person` `person` ON `person`.`uid` = mmn_person_function_unit.person_uid WHERE (`mmn_person_function_unit`.`foreign_uid` = ?) AND (`person`.`sortingname` LIKE ? ESCAPE '\\') AND (`person`.`sys_language_uid` LIKE ? ESCAPE '\\') AND (`person`.`deleted` = 0) LIMIT 50' with params [1809, "%karl%", 0]: Column 'uid' in field list is ambiguous
class MyRelationReceiver extends SuggestWizardDefaultReceiver

    protected function prepareSelectStatement()
    {
        $expressionBuilder = $this->queryBuilder->expr();

        $this->queryBuilder->resetQueryParts();

        $this->queryBuilder
            ->join(
                $this->table,
                'domain_model_person',
                'person',
                $expressionBuilder->eq('person.uid', $this->table . '.person_uid')
            )
            ->where(
                $expressionBuilder->eq($this->table . '.foreign_uid', $this->queryBuilder->createNamedParameter((int)$this->params['uid'], \PDO::PARAM_INT)),
                $expressionBuilder->like('person.sortingname', $this->queryBuilder->createNamedParameter('%' . $this->params['value'] . '%'))
            );
    }

and TCA

        '<my-field>' => [
            'label' => 'My Relation',
            'config' => [
                'type' => 'group',
                'allowed' => 'mmn_person_function_unit',
                'foreign_table' => 'mmn_person_function_unit',
                'suggestOptions' => [
                    'default' => [
                        'receiverClass' => <MyRelationReceiver>,
                    ],
                ],
            ],
        ],

the “select()” and “count()” in the “queryTable” Method should prefix fields with “table”

this affects all TYPO3 Versions (11,12,13)

Reviews 3