Yii2: DB Query: bug with array parameter

[uaoleg]

If I bind array parameters for string condition (first andWhere method with parameters list1 and list2) only first values of each array parameter will be passed to SQL query:

$query = new \yii\db\Query();
$query
    ->andWhere('list1 IN (:list1) OR list2 IN (:list2)', [
        ':list1' => [1, 2, 3],
        ':list2' => [4, 5, 6],
    ])
    ->andWhere(['list3' => [7, 8, 9]])
;
echo $query->createCommand()->getRawSql();

Actual result:

SELECT * WHERE (list1 IN (1) OR list2 IN (4)) AND (`list3` IN (7, 8, 9))

Expected result:

SELECT * WHERE (list1 IN (1, 2, 3) OR list2 IN (4, 5, 6)) AND (`list3` IN (7, 8, 9))

[SilverFire]

No, actual result is correct. PDO is not designed to bind arrays.

You must prepare valid parameter by yourself:

$query = new \yii\db\Query();
$query
    ->andWhere('list1 IN (:list1) OR list2 IN (:list2)', [
        ':list1' => implode(',', [1, 2, 3]),
        ':list2' => implode(',', [4, 5, 6]),
    ])
    ->andWhere(['list3' => [7, 8, 9]])
;
echo $query->createCommand()->getRawSql();

Or use QueryBuilder:

$query = new \yii\db\Query();
$query
    ->andWhere(['or', ['in', 'list1', [1, 2, 3]], ['in', 'list2', [4, 5, 6]]])
    ->andWhere(['list3' => [7, 8, 9]])
;
echo $query->createCommand()->getRawSql();

[yii-bot]

_This is an automated comment, triggered by adding the label question._

Please note, that the GitHub Issue Tracker is for bug reports and feature requests only.

We are happy to help you on the support forum, on IRC (#yii on freenode), or Gitter.

Please use one of the above mentioned resources to discuss the problem.
If the result of the discussion turns out that there really is a bug in the framework, feel free to
come back and provide information on how to reproduce the issue. This issue will be closed for now.

[cebe]

':list1' => implode(',', [1, 2, 3]),

this is not correct, it will generate test against a single string value IN( '1,2,3' )

[SilverFire]

Yepp. It will work if we wrap it in new yii\db\Expression(implode(',', [1, 2, 3]))

[uaoleg]

@SilverFire it also does not work

[SilverFire]

What SQL was generated?