Hi!
I’m relatively new to Yii2 and currently stuck trying to create some queries with query builder.
They work until i try to bind some parameters.
The complete raw SQL would be
SELECT DISTINCT(u.ms), (SELECT SUM(quantity) FROM usage u1 WHERE u1.usagecat_id = 1 AND u1.ms = u.ms AND u1.year = u.year AND u1.month = u.month)
FROM sim s
INNER JOIN usage u ON s.ms = u.ms
WHERE s.customer_id = :customer_id AND u.year = :year AND u.month = :year;
As for simplicity, I disregarded the select inside.
I’ve put the query together as follows
$query = (new Query())
->select('u.ms')//, ['quant_recur' => $subq1]
->distinct()
->from('sim AS sim')
->innerJoin('usage AS u', 'sim.ms = u.ms')
->where(['sim.customer_id'=>':customer_id','u.year'=>':year','u.month'=>':month'])
->addParams([':customer_id' => $this->customer, ':year' => $this->year, ':month' => $this->month]);
and always get the error message (also when trying with only one parameter)
Database Exception – yii\db\Exception
SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
The SQL being executed was: SELECT DISTINCT `u`.`ms` FROM `sim` `sim` INNER JOIN `simusage` `u` ON sim.ms = u.ms WHERE `sim`.`customer_id`=':customer_id'
Error Info: Array
(
[0] => HY093
[1] => 0
)
Caused by: PDOException
SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
in [...]\vendor\yiisoft\yii2\db\Command.php at line 837
When I replace the parameter to be bound with a value that exists in the DB, it works fine.
Any ideas?
Thanks for any help in advance,
Regards,
leifox