Recently I need it to make a LEFT JOIN using Yii. I try with $criteria, but it was impossible (for me). So I came across with a “pure” solution. That was:
public function actionGetListBeneficioSegunAnioProcesoInstancia($anio, $proceso, $instancia) {
$consulta = Yii::app()->db->createCommand("
SELECT
B.BEN_ID_NUM,
B.BEN_NOMBRE_TXT,
CASE WHEN LB.BEN_ID_NUM IS NULL THEN 'false' ELSE 'true' END AS MARCA
FROM
PRM_BENEFICIO_BEN B
LEFT JOIN
(
SELECT
BEN_ID_NUM
FROM
ALG_RELACION_PROCESO_REL
WHERE
ANI_ID_NUM = ".$anio."
AND PAS_ID_NUM = ".$proceso."
AND IAS_ID_NUM = ".$instancia."
GROUP BY
BEN_ID_NUM
) LB
ON
B.BEN_ID_NUM = LB.BEN_ID_NUM
WHERE
B.BEN_ESTADO_NUM <> 0
ORDER BY
B.BEN_NOMBRE_TXT ASC"
)->queryAll();
$tupla = array(
'data' => CJSON::encode($consulta)
);
echo CJSON::encode($tupla);
}
So, then I can call this service like this:
http://sbyc.byc.local/nmod/M00ALGRELACIONPROCESOREL/getListBeneficioSegunAnioProcesoInstancia?anio=2018&proceso=428&instancia=273