I have a problem to relate tables in JOIN with CDbCriteria
. I got it to work with a single JOIN between two tables that are related but I have the following case:
structure of tables with fields to relate:
table Tbl_recibo table tbl_domicilio table tbl_entidad
Idrecibo Iddomicilio Identidad
nombre nombre nombre
Iddomicilio Identidad
Codigo SQL:
SELECT
`rec`.`idrecibo` AS 'Num_Recibo',
`dom`.`matricula`,
`dom`.`federado`,
`ent`.`nombre` AS 'profesional',
`dom`.`calle` AS 'domicilio'
FROM
`tbl_recibo` AS rec
LEFT JOIN `tbl_domicilio` AS dom ON `rec`.`iddomicilioapertura` = `dom`.`iddomicilio`
LEFT JOIN `tbl_entidad` AS ent ON `dom`.`identidad` = `ent`.`identidad`
Codigo yii:
public function traerRecibos() {
$r = new CDbCriteria();
$dx = TblDomicilio::model()->tableName();
$ex = TblEntidad::model()->tableName();
$r->select='t.idRecibo,DX.idDomicilio';
$r->join = 'left join ' . $dx . ' DX on DX.idDomicilio = t.idDomicilio';
$r->join = 'left join ' . $ex . ' EX on EX.idEntidad=DX.idEntidad';
return new CActiveDataProvider($this, array(
'criteria' => $r,
'pagination' => array('pageSize' => Yii::app()->user->getState('pageSize', Yii::app()->params['defaultPageSize']),
)));
}
Relaciones de table tblRecibo:
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'tblEstadoreciboHasTblRecibos' => array(self::HAS_MANY, 'TblEstadoreciboHasTblRecibo', 'idRecibo'),
'tblItemrecibowebs' => array(self::HAS_MANY, 'TblItemreciboweb', 'idRecibo'),
'idCalendario0' => array(self::BELONGS_TO, 'TblCalendario', 'idCalendario'),
'idDomicilioApertura0' => array(self::BELONGS_TO, 'TblDomicilio', 'idDomicilioApertura'),
'idDomicilio0' => array(self::BELONGS_TO, 'TblDomicilio', 'idDomicilio'),
'idPaquete0' => array(self::BELONGS_TO, 'TblPaquete', 'idPaquete'),
);
}
I need to visualize the same SQL data but with CDbCriteria
, I can not access the data from the tblEntidad
table from tblRecibo
.
You need to combine JOIN clause in this way:
$r->join = 'left join ' . $dx . ' DX on DX.idDomicilio = t.idDomicilio ';
. 'left join ' . $ex . ' EX on EX.idEntidad=DX.idEntidad';
In your code you just overwriting first assignment to $r->join
.
You may also consider using with()
, so you could reuse relations definitions to build JOIN conditions. See this guide article about relations.