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