yiisoft / db

Yii Database Library
https://www.yiiframework.com/
BSD 3-Clause "New" or "Revised" License
134 stars 35 forks source link

Bug when try to `Command::update()` using `Expression`s in values or in where condition #805

Closed Tigrov closed 7 months ago

Tigrov commented 9 months ago

There are two problems when update table using expressions:

  1. Expression::$params are not passed in the query.
  2. If Expression::$params have an ExpressionInterface inside, it is not processed
$values = ['abc'];

$command->update(
    'table_name,
    [
        'values' => new Expression(
            'ARRAY(SELECT DISTINCT UNNEST("values" || :values))',
            ['values' => new ArrayExpression($values, 'varchar(126)')]
        ),
    ],
    '"values" && :values',
    ['values' => new ArrayExpression($values, 'varchar(126)')]
)->execute();

Expected query

UPDATE "table_name" 
SET "values"=ARRAY(SELECT DISTINCT UNNEST("values" || ARRAY[:qp1]::varchar(126)[])) 
WHERE "values" && ARRAY[:qp2]::varchar(126)[]
$params === [
    ':qp1' => 'abc',
    ':qp2' => 'abc',
];

Actual query

UPDATE "table_name"
SET "values"=ARRAY(SELECT DISTINCT UNNEST("values" || :values))
WHERE "values" && :values
$params === [
    ':values' => ArrayExpression,
];