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

Question

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 ??

score 2 · Accepted Answer · edited May 23 '17 at 12:24

2

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.

edited May 23 '17 at 12:24

Community

1
1

answered Nov 18 '15 at 08:24

arogachev

33,150
7
114
117

will it delete multiple record here? I use IN cause I want to delete multiple record using one sql. – Nov 18 '15 at 08:27
@tyan Yes, when you pass an array as a value to key-value pair in `where` statement, it will be automatically converted to `IN` statement. See [provided link](http://www.yiiframework.com/doc-2.0/yii-db-queryinterface.html#where%28%29-detail), there is an example with array and `IN`. – arogachev Nov 18 '15 at 08:29

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

1 Answers1