how to used IN & Between Clause in YII ACtive Reco

Question

I want write a Following Query in Active record .

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

Thanks

Answer 1

I still using this way:

public function getInterval( $data_start, $data_stop ){
  $criteria = new CDbCriteria;
  $criteria->condition = "date  >= '$data_start' AND date <= '$data_stop'";
  return $criteria;
}
$model = Object::model()->findAll(getInterval('2014-06-01','2014-06-30');

Answer 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)));

Answer 3

Either you can use addInCondition or you can also use compare method.

 $criteria = new CDbCriteria();
 $criteria->compare('UserId',array(6,7,8,9));
 $userDataObj = User::model()->findAll($criteria);

Answer 4

You can use CDbCriteria statement:

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

Answer 5

There is an function called findAllBySql in yii to run sql query and get the outputs.

$sql="SELECT * FROM `User` WHERE `UserId` IN ( 6, 7, 8, 9 )";//Your Sql query
$value=User::model()->findAllBySql($sql);// "User" is the model belongs to the table 'User'

The "$value" will return the result in a array. To get the values you can use the following method.

foreach($value as $val){
   echo $val->UserId;
}

(or)

var index=0;
echo $val[$index]->UserId;

Answer 6

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';