CODEWITHSUNDEEP
sqlyii2dao
user4985526
user4985526

Reputation:

Yii2 bind param to the raw sql statement for the IN field

When I'm using bindParam in the Yii2:

$arr = [1,3];
$str = implode(' ,', $arr);
Yii::$app->db->createCommand('DELETE FROM article_tag WHERE article_id=:id AND tag_id IN (:str)')
        ->bindValue(':id', $this->id)
        ->bindValue(':str', $str)
        ->execute();

if I bind str to the IN field , the raw sql seems to becomes 

SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect DOUBLE value: '1 ,3'
The SQL being executed was: DELETE FROM article_tag WHERE article_id=13 AND tag_id IN ('1 ,3')

and it tells me the incorrect double value here. I guess it's because it adds single quotes to the sql . How could I deal with this ??

Upvotes: 1

Views: 2273

Answers (1)

arogachev
arogachev

Reputation: 33548

The code can be refactored to:

Yii::$app
    ->db
    ->createCommand()
    ->delete('article_tag', ['article_id' => $this->id, 'tag_id' => $arr])
    ->execute();

Use framework features, writing raw SQL is needed for more complex queries.

See this question to understand how params are automatically binded. Here you can see how to specify condition to where statement.

For most cases it's better to create ActiveRecord and use ActiveQuery for building queries.

Upvotes: 2

Related Questions