Yii2 math in \yii\db\Query()

Question

I want to do some math (divide, minus, plus...), and two or more fields' values are using a Yii database query like below. Can I do it like this?

 $data = (new \yii\db\Query())
 ->select([
  'date',
  'SUM( IF(status = "Passed", 1, 0) ) AS passed',
  'SUM( IF(status = "Failed", 1, 0) ) AS failed',
  'SUM( IF(status = "On Hold", 1, 0) ) AS onhold',
  'passed/onhold as selfsubmit',
  'COUNT(*) AS total'
 ])
 ->from('qa3d')
 ->groupBy('date')
 ->all();

score 2 · Accepted Answer · edited Jan 19 '19 at 08:38

First of all, you cannot do arithmetics (math) with aliases in the same query with MySQL. That is mentioned here as well.

But you can calculate using the yii\db\Expression class, because with that, the framework won't try to escape the expression as MySQL columns.

So basically you have to select the expression without the aliases and with the Expression class.

For example:

$data = (new \yii\db\Query())
    ->select([
        'date',
        'SUM( IF(status = "Passed", 1, 0) ) AS passed',
        'SUM( IF(status = "Failed", 1, 0) ) AS failed',
        'SUM( IF(status = "On Hold", 1, 0) ) AS onhold',
        new \yii\db\Expression('SUM( IF(status = "Passed", 1, 0) ) / SUM( IF(status = "On Hold", 1, 0) ) AS selfsubmit'),
        'COUNT(*) AS total'
    ])
    ->from('qa3d')
    ->groupBy('date')
    ->all();

Yii2 math in \yii\db\Query()

1 Answers1