how to used IN & Between Clause in YII ACtive Record?

Question

I want write a Following Query in Active record .

SELECT *
FROM `User`
WHERE `UserId`
IN ( 6, 7, 8, 9 ) ;

Thanks

Solution 1

You can use CDbCriteria statement:

$criteria = new CDbCriteria();
$criteria->addInCondition('userId', array(6,7,8,9));
$result = User::model()->findAll($criteria);

Solution 2

You can put your array as a value for a specific attribute, like this (no tested):

$model=new User();
$result=$model->findAllByAttributes(array('UserId'=>array(6,7,8,9)));

Solution 3

If you want to get your query faster, use Command Builder:

Yii::app()->db->createCommand()
    ->select('*')
    ->from('user')
    ->where(array('in', 'UserId', array(6, 7, 8, 9)))
    ->queryAll();

To get it via CActiveRecord, use findAllByAttributes

User::model()
    ->findAllByAttributes(array(
         'UserId' => array(6,7,8,9)
    ));

But it will get full object of User with all associated relations, so it's slower.

Solution 4

You might use both IN and BETWEEN statements thru CDbCriteria:

$criteria = new CDbCriteria();
$criteria->addInCondition("id", array(6,7,8,9));
$criteria->addBetweenCondition('id', '10', '20', 'OR');
$result = User::model()->findAll($criteria);

this will result in SQL query like this:

SELECT *
FROM `User`
WHERE `id`
IN ( 6, 7, 8, 9 )
OR `id` BETWEEN 10 AND 20

Note the 4-th paramenter OR in addBetweenCondition() method; missing it, the default AND will be applied to concatenate that condition to the rest of WHERE-query.

P.S. Strictly speaking those addBetweenCondition() and addInCondition() methods should be added to an existing condition. So, you might need to set first a criteria's initial condition like this:

$criteria->condition = '1=1';

View more solutions

Comments

[Jane Panda]

Why new User()? Is that superior to User::model() in some way?

[The Bndr]

Hi Bob, user is the name of the model under protected/models/user.php

[Twisted Whisper]

I think Bob was asking why do it in two lines instead of one User::model()->findAllByAttributes(array('UserId'=>array(6,7‌​,8,9)));?

[The Bndr]

@Bob Instancing the model is in my case default. But in this case it's not really necessary, you can use the static version User::model()->...

[Justinas]

isn't it exposed to SQL injection?

[SaidbakR]

Is there a solution like that for yii2 ?

[Mave]

where(array('in', 'column_name', array()) saved me, cheers!