select backtick on IFNULL function

hi,

I have this select:

$query = ViewVendite::find()->select('vw_vendite.nome_capo_zona, vw_vendite.nome_isf, vw_vendite.nome_prodotto, sum(vw_vendite.unita_indiretta) AS unita_indiretta,

    IFNULL((


SELECT


sum(tvend.unita_indiretta)


FROM `vw_vendite` as `tvend`


WHERE tvend.data_inizio >= DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, "%Y-%m-01") - INTERVAL 2 MONTH AND


    tvend.data_inizio < DATE_FORMAT(CURDATE(), "%Y-%m-01") AND


`tvend`.`nome_isf` = `vw_vendite`.`nome_isf` AND


`tvend`.`nome_prodotto` = `vw_vendite`.`nome_prodotto`


), 0) AS tre_mesi,


IFNULL((


    SELECT


sum(svend.unita_indiretta)


FROM `vw_vendite` as `svend`


WHERE svend.data_inizio >= DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, "%Y-%m-01") - INTERVAL 5 MONTH AND


    svend.data_inizio < DATE_FORMAT(CURDATE(), "%Y-%m-01") AND


`svend`.`nome_isf` = `vw_vendite`.`nome_isf` AND


`svend`.`nome_prodotto` = `vw_vendite`.`nome_prodotto`


), 0) AS sei_mesi,


    IFNULL((


SELECT


sum(yvend.unita_indiretta)


FROM `vw_vendite` as `yvend`


WHERE yvend.data_inizio >= DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, "%Y-%m-01") - INTERVAL 1 YEAR AND


    yvend.data_inizio < DATE_FORMAT(CURDATE(), "%Y-%m-01") AND


`yvend`.`nome_isf` = `vw_vendite`.`nome_isf` AND


`yvend`.`nome_prodotto` = `vw_vendite`.`nome_prodotto`


), 0) AS dodici_mesi')->from(ViewVendite::tableName())->where('vw_vendite.data_inizio = DATE_FORMAT(CURDATE(), "%Y-%m-01" ')->groupBy('vw_vendite.nome_isf, vw_vendite.nome_prodotto');

if I test the same query on SQL it works but using YII2 I don’t understand why I get this warning about sintax.

I only see two backtick generated and I think the problem is just there.

from the Database Exception:

tvend.nome_prodotto = vw_vendite.nome_prodotto

), 0) AS tre_mesi, /* here the problem: backtick before the 0 and AS at every IFNULL function */

IFNULL((

SELECT…

is there another way to get this query? Iuse it in the model search for a gridview

if you want to pass raw sql into the select method, you need to use add it as a db expression

eg,




ViewVendite::find()

->select('vw_vendite.nome_capo_zona, vw_vendite.nome_isf, vw_vendite.nome_prodotto')

->addSelect(new Expression('IFNULL((SELECT .....');




Alternatively, create the raw sql yourself and use a db command like $connection->createCommand(‘SELECT * FROM user’)->queryAll();

http://www.yiiframework.com/doc-2.0/yii-db-command.html

I tryied both but always the same problem. I solved adding backtick everywhere…

$query = ViewVendite::find()->select(’vw_vendite.nome_capo_zona, vw_vendite.nome_isf, vw_vendite.nome_prodotto, sum(vw_vendite.unita_indiretta) AS unita_indiretta')

            ->addSelect(new \yii\db\Expression('IFNULL((SELECT


sum(`tvend`.`unita_indiretta`)


FROM `vw_vendite` as `tvend`......

It also works with my initial query without "->addSelect(new \yii\db\Expression"…

thanks for your help, I’d like understanding why for this case I had to add these backticks…