Multiple Query - Union - Query Builder Loses Parameters

Hello all,

I may have found a bug in Yii Query Builder. Before reporting - I wanted to verify that my code is correct.

I am creating a query with query builder and using the getText() option and saving this into a variable. Then I am creating a new query with query builder and first using union($variable) to join the previous query with this new query.

When using the recommended where syntax of:

->where('product_id=:product_id2 AND type=:type2 AND purch_date <=:purch_date2', array(':product_id2'=>$inv->id, ':type2'=>Trans::TYPE_BUY, ':purch_date2'=>$currentDate))

The first saved query when attached to the second query via a union seems to lose it’s parameter references. The MY SQL error is:

CDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

Here’s the unusual thing (and what makes me think its an issue with yii’s parameter parsing) if I directly assign the variables to each condition such as:

->where('product_id=' . $inv->id . ' AND type=' . Trans::TYPE_BUY . ' AND purch_date <="' . $currentDate . '"')

and run the code, it executes flawlessly. No other changes are made except hard coding the where conditions for each query text that is not the ‘final’ query.

Am I incorrect in how I am assigning conditions? I would like my variables escaped, so would very much like the proper implementation of the param option of the where condition.

Full code is listed below:

//get list of purchases

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

			->select('sum(units*price)as purAmount, sum(units)as unitAmount')


			->where('purchase_id=:purchase_id AND type=:type AND purch_date <=:purch_date', array(':purchase_id'=>$inv->id, ':type'=>OrTransaction::TYPE_BUY, ':purch_date'=>$currentDate))




		//get list of sells

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


			->select('(sum(units*price)*-1)as purAmount, (sum(units)*-1) as unitAmount')


		        ->where('purchase_id=:purchase_id AND type=:type AND purchase_date <=:purch_date', array(':purchase_id'=>$inv->id, ':type'=>OrTransaction::TYPE_SELL, ':purch_date'=>$currentDate))



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

			->select('sum(purAmount) as PurAmount, sum(unitAmount) as UnitAmount')

			->from('('.$sells.') as tmp')


		return $transactions;	

Just noticed in the doc:

that it says:

What on earth does that mean? How do you access, save it, and then reapply it to the next query?