Issue Task Closed

#105387Add an exception for update or delete with join and queryBuilder

Florian Rival 10/22/24, 08:22 updated 08/12/25, 14:36

Description

With queryBuilder, you can make a delete or update query with a join condition like this:

$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder
    ->update('tt_content')
    ->leftJoin('tt_content', 'sys_file_reference')
    ->where(
        $queryBuilder->expr()->eq('tt_content.bodytext', $queryBuilder->createNamedParameter('lorem', Connection::PARAM_STR)),
        $queryBuilder->expr()->eq('sys_file_reference.fieldname', $queryBuilder->createNamedParameter('assets', Connection::PARAM_STR)),
    )
    ->set('tt_content.bodytext', 'dolor')
    ->executeStatement();

but join condition are not supported by doctrine/dbal because the join method is not supported in all db engines for delete or update.

And with queryBuilder this result in an SQL error like:

> (1/3) #1054 Doctrine\DBAL\Exception\InvalidFieldNameException
> An exception occurred while executing a query: Unknown column ‘sys_file_reference.fieldname’ in ‘where clause’

which is not very clear.

I suggest to add an exception in queryBuilder when you try to add a join for an update or a delete.

Reviews 4