Append where clause

Yii 1.1 General Discussion
1

I am trying to merge several WHERE clause with AND based under certain condition. In Zend you can do something like this.




// Build this query:

//   SELECT product_id, product_name, price

//   FROM "products"

//   WHERE (price > 100.00)

//     AND (price < 500.00)

     

$minimumPrice = 100;

$maximumPrice = 500;

    

$select = $db->select()

             ->from('products', array('product_id', 'product_name', 'price'))

             ->where('price > ?', $minimumPrice);

             

if(true)//Check some condition

{

    $select->where('price < ?', $maximumPrice);//Append where clause which gets appended as AND

}

Is there any equivalent like this in YII. I will really appreciate any input in this matter.

2

Hi nicholasnet,

I would do it like this, when I’m using CActiveRecord and CDbCriteria




$criteria = new CDbCriteria;

$criteria->compare('price', '>'.$mimimumPrice);

if ($someCondition)

    $criteria->compare('price', '<'.$maximumPrice);

$products = Product::model()->findAll($criteria);

In the above, the 1st "compare" will produce the condifion for "price > 100.00", and the 2nd will append the condition for "price < 500.00" and merge it with the 1st one using "AND".

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#compare-detail

Note that "compare()" and "addXxxCondition()" methods of CDbCriteria will add a new condition and merge it with the existing conditions using "AND" or "OR" (the default is "AND").

If you want to use PDO (CDbCommand), then …




$conditions = array(

    'and',

    'price > :minprice',

);

$params = array(

    ':minprice' => $minprice,

);

if ($someCondition)

{

    $conditions[] = 'price > :maxprice';

    $params[':maxprice'] = $maxprice;

}

$products = Yii::app()->db->createCommand()

    ->select('product_id, product_name, price')

    ->from('products')

    ->where($conditions, $params)

    ->queryAll();

Look up "CDbCommand::where()" in the reference.

http://www.yiiframework.com/doc/api/1.1/CDbCommand#where()-detail

3

I am sorry got side tracked. Thank you very much for your reply. I was able to solve where part but I bumped into into union problem.

How can I merge multiple query using union. I tried to do something like this but it did not work.

http://www.yiiframework.com/forum/index.php/topic/28946-multiple-query-union-query-builder-loses-parameters/page__p__139276__hl__union#entry139276

Can I pass command object in union instead of sql like this

$command = Yii::app()->db->createCommand()->select(‘id’)->from(‘content’);

Yii::app()->db->createCommand()->union($command)->select(‘id’)->from(‘post’)->queryAll();