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')
->from('{{or_transaction}}')
->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))
->getText();
//get list of sells
$sells = Yii::app()->db->createCommand()
->union($purchases)
->select('(sum(units*price)*-1)as purAmount, (sum(units)*-1) as unitAmount')
->from('{{or_transaction}}')
->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))
->getText();
$transactions = Yii::app()->db->createCommand()
->select('sum(purAmount) as PurAmount, sum(unitAmount) as UnitAmount')
->from('('.$sells.') as tmp')
->queryAll();
return $transactions;