Sql query does not work (phpMyAdmin ok)


(Nan Do9) #1

I have the following query, if it works for me when I run it in phpMyAdmin

select concat (ev.eve_estado,count(seg_codigo)) AS '0' ,COUNT(seg_codigo) AS '1'
from tbl_seguimiento se 
INNER JOIN tbl_evento ev ON ev.eve_codigo = se.eve_codigo
INNER JOIN tbl_despachos1 de ON de.des1_codigo=se.des1_codigo
WHERE (de.des1_ubi_fecha=se.seg_fecha_hora) and de.des1_fecha='2019-10-16'
group by se.eve_codigo

but in yii 2 I have the following and it doesn’t work for me

 $estado = (new \yii\db\Query())
      ->select(["CONCAT(ev.eve_estado, ' (', COUNT(seg_codigo ), ')') AS '0'", 'COUNT(seg_codigo) AS "1"'])
      ->from('tbl_seguimiento se')
      ->innerJoin('tbl_evento ev',' ev.eve_codigo = se.eve_codigo ')  
      ->innerJoin('tbl_despachos1 de',' de.des1_codigo = se.des1_codigo ')
     ->where(['de.des1_ubi_fecha' => 'se.seg_fecha_hora'])
      ->andwhere(['de.des1_fecha'=>'2019-10-16']) 
      ->groupBy('se.eve_codigo')
      ->all();

#2

Check debug toolbar and inspect what query statement Is generated.


(Nan Do9) #3

this generates me

SELECT CONCAT(ev.eve_estado, ' (', COUNT(seg_codigo ), ')') AS '0', 
COUNT(seg_codigo) AS "1" 
FROM `tbl_seguimiento` `se` 
INNER JOIN `tbl_evento` `ev` ON  ev.eve_codigo = se.eve_codigo  
INNER JOIN `tbl_despachos1` ON  tbl_despachos1.des1_codigo = se.des1_codigo  
WHERE (`tbl_despachos1`.`des1_ubi_fecha`='se.seg_fecha_hora') 
AND (`tbl_despachos1`.`des1_fecha`='2019-10-16') 
GROUP BY `se`.`eve_codigo`

(Softark) #4

se.seg_fecha_hora is treated as a string, not as a column.


(Nan Do9) #5

And how do I place it to consider it as a column?


(Softark) #6
where('de.des1_ubi_fecha = se.seg_fecha_hora')
// or
where(['de.des1_ubi_fecha' => new yii\db\Expression('se.seg_fecha_hora')])

(Nan Do9) #7

thanks it worked