Buenas, soy practicamente nuevo en Yii. y estoy tratando de hacer una llevar una gran consulta SQL a Yii, para generar una vista pero tengo muchas dudas acerca de; si me podrian guiar seria de gran ayuda:
esta es la consulta que tengo:
$consulta=Nphojint::model()->findBySql(
'SELECT A.*,to_char(m.fecinienc,\'05/10/2016\') as fechaenc
FROM (
SELECT "SIMA010".fc_ultimo_cargo(a.codemp,\'05/10/2016\') AS nomcar,
a.nacemp AS nacemp,
a.codemp AS codemp,
a.cedemp AS cedemp,
a.nomemp AS nomemp,
c.codnom AS codnom,
f.tipnom AS tipnom,
f.conemp AS conemp,
c.nomnom AS nomnom,
e.suecar AS sueldocargo,
(e.suecar*12) AS sueldocargoanual,
coalesce((SELECT b.suecar AS suecarenc
FROM "SIMA010".nphojint a, "SIMA010".npcargos b
WHERE trim(a.codcarenc) = trim(b.codcar) AND trim(a.codemp) = trim(\'0381\')),0) AS suecarenc,
coalesce((SELECT (b.suecar*12) AS suecarencanual
FROM "SIMA010".nphojint a, "SIMA010".npcargos b
WHERE trim(a.codcarenc) = trim(b.codcar) AND trim(a.codemp) = trim(\'0381\')),0) AS suecarencanual,
(SELECT coalesce((unitrib*3),0) AS primahogar
FROM "SIMA010".npdefgen ) AS primahogar,
coalesce((SELECT coalesce((a.unitrib*4),0) AS primamerito
FROM "SIMA010".npdefgen a, "SIMA010".npasiconemp b
WHERE b.codcon = \'005\' and trim(b.codemp) = trim(\'0381\')),0) AS primamerito,
coalesce((SELECT (b.monto*2) AS primarespon
FROM "SIMA010".npasiconemp b
WHERE b.codcon = \'072\' and trim(b.codemp) = trim(\'0381\')),0) AS primarespon,
(SELECT coalesce((a.unitrib*9),0) AS primatrans
FROM "SIMA010".npdefgen a, "SIMA010".npasiconemp b
WHERE b.codcon = \'064\' and trim(b.codemp) = trim(\'0381\')) AS primatrans,
(SELECT coalesce(sum((a.unitrib*1.5)),0) AS primahijos
FROM "SIMA010".npdefgen a, "SIMA010".npasiconemp b, "SIMA010".npinffam c
WHERE trim(b.codemp) = trim(c.codemp) and b.codcon = \'004\' and trim(b.codemp) = trim(\'0381\') and EXTRACT(YEAR FROM(age(c.fecnac))) < \'19\' ) AS primahijos,
(SELECT coalesce(sum(monto),0) AS primasantyprof
FROM "SIMA010".npasiconemp
WHERE trim(codemp) = trim(\'0381\') AND codcon in (\'062\',\'063\')) AS primasantyprof,
(SELECT coalesce(sum(monto),0) AS compensacionsalarial
FROM "SIMA010".npasiconemp
WHERE trim(codemp) = trim(\'0381\') AND codcon in (\'084\',\'086\')) AS compensacionsalarial,
(SELECT ((unitrib * 1.5)*30) AS cestaticket
FROM "SIMA010".npdefgen) AS cestaticket,
(SELECT (((unitrib * 1.5)*30)*12) AS cestaticketanual
FROM "SIMA010".npdefgen) AS cestaticketanual,
(SELECT valor1 AS subvencion
FROM "SIMA010".npdefvar
WHERE TRIM(codvar) = \'SVA\') AS subvencion,
(SELECT (valor1*12) AS subvencionanual
FROM "SIMA010".npdefvar
WHERE TRIM(codvar) = \'SVA\') AS subvencionanual,
(e.suecar*2) AS bonincahoanual,
coalesce((SELECT (b.suecar*2) AS bonincahoanualenc
FROM "SIMA010".nphojint a, "SIMA010".npcargos b
WHERE trim(a.codcarenc) = trim(b.codcar) AND trim(a.codemp) = trim(\'0381\')),0) AS bonincahoanualenc,
((e.suecar/30)*45) AS bonoperma,
coalesce((SELECT ((b.suecar/30)*45) AS bonopermaenc
FROM "SIMA010".nphojint a, "SIMA010".npcargos b
WHERE trim(a.codcarenc) = trim(b.codcar) AND trim(a.codemp) = trim(\'0381\')),0) AS bonopermaenc,
(((e.suecar/30)*45)*2) AS bonopermaanual,
coalesce((SELECT (((b.suecar/30)*45)*2) AS bonopermaanualenc
FROM "SIMA010".nphojint a, "SIMA010".npcargos b
WHERE trim(a.codcarenc) = trim(b.codcar) AND trim(a.codemp) = trim(\'0381\')),0) AS bonopermaanualenc,
(to_char(a.fecing,\'05/10/2016\')) AS fecing,
a.fecing AS fecing2,
"SIMA010".fc_cargo_encargado(trim(\'0381\'),\'05/10/2016\') AS nomcarenc,
a.staenc AS staenc,
b.desniv AS desniv
FROM "SIMA010".nphojint a, "SIMA010".npestorg b , "SIMA010".npasicaremp c, "SIMA010".npcargos e, "SIMA010".npnomina f
WHERE trim(a.codemp) = trim(\'0381\')
and trim(a.codemp) = trim(c.codemp)
and trim(a.codniv) = trim(b.codniv)
and trim(c.codcar) = trim(e.codcar)
and trim(c.codnom) = trim(f.codnom)
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35 ) AS A
LEFT JOIN "SIMA010".nphisenc m ON trim(A.codemp) = trim(m.codemp)
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36'
);
tengo una relacion en el modelo Nphojint:
‘relacion’=>array(self::HAS_ONE,‘Npasicaremp’,‘codemp’),
cuando voy a mostrarlo en la vista:
echo $consulta->codemp;
echo $consulta->relacion->codemp;
Me arroja el siguiente error:
Invalid argument supplied for foreach()
{
// determine the primary key value
if(is_string($this->_pkAlias)) // single key
{
if(isset($row[$this->_pkAlias]))
$pk=$row[$this->_pkAlias];
else // no matching related objects
return null;
}
else // is_array, composite key
{
$pk=array();
foreach($this->_pkAlias as $name=>$alias)
{
if(isset($row[$alias]))
$pk[$name]=$row[$alias];
else // no matching related objects
return null;
}
$pk=serialize($pk);
}
// retrieve or populate the record according to the primary key value
if(isset($this->records[$pk]))
$record=$this->records[$pk];