Unobvious behavior in the query builder

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

As stated in docs - in case you are selecting more that one thing in query it’s usually better to use array syntax for this. Try

->select(['*', 'TRUNCATE(SUM(value)/100,2) as v'])

Thank you for reply.
Your command

$v = \app\models\Transactions::find()->select([’*’,‘TRUNCATE(SUM(value)/100,2) as sumv’])->all();

return single row without sumv;

Command

$v = \app\models\Transactions::find()->select([‘TRUNCATE(SUM(value)/100,2) as sumv’)->all();

returns nothing.
Command

$v = \app\models\Transactions::find()->select(‘TRUNCATE(SUM(value)/100,2) as sumv’)->all();

gets same error as in topic.
Currently I receive data i want as

$v = \app\models\Transactions::find()->sum(‘value’);

But I want to know, is it possible to get exactly [value/100] without any further operations in php.

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:

  1. Better suited for your case

\app\models\Transactions::find()->…->select([‘TRUNCATE(SUM([[value]])/100,2)’)->scalar();

  • 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.
  1. 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]]).

1 Like

\app\models\Transactions::find()->select([‘TRUNCATE(SUM([[value]])/100,2)’])->scalar();

Worked fine, thank you.