Hello.
I’m trying to select all values from some table, and get a sum of one of columns, divided by 100:
It looks like this:
$v = \app\models\Transactions::find()->select(’*,TRUNCATE(SUM(value)/100,2) as v’)->where([‘post_id’ => $model->id, ‘service_id’ => 14])->all();
But when i run it, i receive a error:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS `v` FROM `transactions` WHERE (`post_id`=1) AND (`service_id`=14)' at line 1
The SQL being executed was: SELECT *, TRUNCATE(SUM(value)/100, `2)` AS `v` FROM `transactions` WHERE (`post_id`=1) AND (`service_id`=14)
Obviously we have a quote at wrong place:
Query formatted by Yii:
TRUNCATE(SUM(value)/100, 2)
But, as i guess, it should be
TRUNCATE(SUM(value)/100, 2)
Am I doing something wrong of this is Yii’s failure?
Thank you.
P.S. WYSIWYG editor breaks quotes, so here a screenshot of a error
Are you using these ‘ and ’ instead of '? And if you are using model’s ActiveQuery you must have sumv property there. Isn’t it better to use just simple Query without model?
As Bizley correctly mentioned, by default ActiveQuery creates objects and tries to assign query results to respective defined attributes. There are several possible solutions if there is no defined attributes:
it will return first value of the very first result row. There are also ->column() and other handy methods that return raw results and ->asArray() query modifier.
Sometimes it’s fine to define additional fields in your models
class Transaction extends ActiveRecord {
public $lowerText;
}
Transaction::find()->select([’*’, ‘lowerText’ => ‘lower(text)’])->all();
In case Yii2 quotes wrong, you can help it using db agnostic quoting with double square brackets for column names like this: SUM(value) -> SUM([[value]]).