Show result gridView cbdCriteria join


(gabomda) #1

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:

enter image description here

code 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`

code 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']),
    )));
}

relationship 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'),
    );
}

View index tblRecibo model:

$this->widget('bootstrap.widgets.TbGridView', array(
    'type' => 'bordered striped',
    'id' => 'tbl-recibo-grid',
    'dataProvider' => $dataProvider,
    'htmlOptions' => array('style' => 'font-size: 16px;font-weight: normal'),
    'columns' => array(
        array(
            'name' => 'idRecibo',
            'header' => 'N°Recibo',
            'headerHtmlOptions' => array('style' => 'width: 10px'),
        ),
        array(
            'name' => 'idDomicilio',
            'header' => 'Matricula',
            'value' => 'isset($data->idDomicilio0->matricula)?$data->idDomicilio0->matricula:""',
            'headerHtmlOptions' => array('style' => 'width: 15%'),
        // 'filter' => CHtml::listData(TblDomicilio::model()->findAll('idDomicilio', 'nombre'),
        ),
));
?>

I need to show in the view the value of the ‘name’ field of the tblEntity table which is not directly related to tblRecibo. I hope you can help me and I do not know what else to do !. Thanks!