How to write WHERE IN using DAO

I am trying to write WHERE IN part of my query inside DAO, but I always get errors.

I have tried several variations of this, but I always miss it:


$ids = "1, 2, 4, 5";


$query = $db->createCommand("SELECT username FROM user WHERE id IN :ids")->bindValue([':ids' => '($ids)'])->queryAll();

What I am doing wrong ?

in yii2

return static::findOne([‘username’ => $username, ‘roleId’ => [‘2’,‘3’]]);

In the bindValue use the doublequotes, that the $ids get inserted




$query = $db->createCommand("SELECT username FROM user WHERE id IN :ids")->bindValue([':ids' => "($ids)"])->queryAll();

Produces this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’ ‘(1, 3, 5)’ ’ at line 5

syntax for where in is:


['2','3'] 

and ur doing this


 $ids = "1, 2, 4, 5";

change by




$ids = ['1','2','4','5'];

$query = $db->createCommand("SELECT username FROM user WHERE id IN :ids")->bindValue([':ids' => $ids])->queryAll();

hope it works

No it does not. Pure SQL WHERE IN expect this format " WHERE IN ( 1, 2, 3, 4 )…"

So I need to pass variable that will hold this value "1, 2, 3, 4"… But whatever I do I get some error.

If I hardcode values it works:


$query = $db->createCommand("SELECT username


                             FROM user


                             WHERE id IN (1, 3, 5)


                           ")->queryAll();

But instead of hardcoded values I need to pass variable.

cant u rewrite ur query like this

for this:


SELECT username FROM user WHERE id IN :ids")->bindValue([':ids' => $ids])->queryAll();


$modeldata = User::find()->where(['id' => ['1','2','3','4']])->all();

this works in my controller.

I do not use ActiveRecord. I am asking for DAO solution.

Parameter binding is implemented via prepared statements. Besides preventing SQL injection attacks, it may also improve performance by preparing a SQL statement once and executing it multiple times with different parameters. For example,

$command = $db->createCommand(‘SELECT * FROM post WHERE id=:id’);

$post1 = $command->bindValue(’:id’, 1)->queryOne();

$post2 = $command->bindValue(’:id’, 2)->queryOne();

Because bindParam() supports binding parameters by references, the above code can also be written like the following:

$command = $db->createCommand(‘SELECT * FROM post WHERE id=:id’)

          ->bindParam(':id', $id);

$id = 1;

$post1 = $command->queryOne();

$id = 2;

$post2 = $command->queryOne();

Notice that you bind the placeholder to the $id variable before the execution, and then change the value of that variable before each subsequent execution (this is often done with loops). Executing queries in this manner can be vastly more efficient than running a new query for every different parameter value.

How this is an answer to my question ?

Can someone please guide me how to use WHERE IN (…) with DAO ? How should I supply the values that should go inside the () ?

I recommend using query builder - take a look here http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html#operator-format

for variable number of variables you have to build own query

with PDO workaround (index padding). better if you can provide array with start index as ‘1’




$ids = [1,2,3,4,5];

// change index 0 to 1. PDO starts from 1

$ids = array_pad($ids, -count($ids)-1, null);

unset($ids[0]);  // we got required [1=>1,2,3,4,5];

$in = implode(',', array_fill(0, count($ids), '?'));

$c = \Yii::$app->db->createCommand("SELECT username FROM user WHERE id IN ($in)")->bindValues($ids);

echo $c->rawSql;		

die;



just looping. looks better for regular array with start index ‘0’




$ids = [1,2,3,4,5];

$in = implode(',', array_fill(0, count($ids), '?'));

$c = \Yii::$app->db->createCommand("SELECT username FROM user WHERE id IN ($in)");

foreach ($ids as $k=>$id) {

  $c->bindValue($k+1,$id);

}

echo $c->rawSql;		

die;