Yii Framework 1.1 CdbCriteria在GridView中有多个JOIN

Question

I'm starting in the world Yii framework in version 1.1 and it's impossible for me to visualize the records brought from a pure query. It's been days since I can't solve it, it's probably something simple but with my poor knowledge I haven't been able to solve it yet. I show you my code to see if you can help me.

The relationships of the TblRecibo model, which is where I want to make the query are:

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

My model:

public function search()
{      
    $criteria = new CDbCriteria;
    $criteria->alias = 'r';
    $criteria->select = 'r.idRecibo,d.matricula,d.federado,e.nombre,d.calle,r.cantidad,cal.periodo,est.ult_modif,os.nombre ';
    $criteria->join ='LEFT JOIN tbl_calendario as cal on r.idcalendario=cal.idcalendario '
            .'LEFT JOIN tbl_estadorecibo_has_tbl_recibo as est on r.idrecibo=est.idrecibo '
            .'LEFT JOIN tbl_domicilio as d ON r.iddomicilioapertura=d.idDomicilio '
            .'LEFT JOIN tbl_entidad as e ON d.idEntidad=e.idEntidad '
            .'LEFT JOIN tbl_itemreciboweb as rweb on r.idrecibo=rweb.idrecibo '
            .'LEFT JOIN tbl_ooss as os on rweb.idooss=os.idooss';
    return new CActiveDataProvider($this, array(
        'criteria' => $criteria,
        'pagination' => array('pageSize' => Yii::app()->user->getState('pageSize', Yii::app()->params['defaultPageSize']),
        ),
    ));
}

My controller:

public function actionIndex()
{
    $model = new TblRecibo('search');
    $model->unsetAttributes();  // clear any default values
    if (isset($_GET['TblRecibo']))
        $model->attributes = $_GET['TblRecibo'];
    $this->render('admin', array(
        'model' => $model,
    ));
}

My view:

<?php
 $this->widget('bootstrap.widgets.TbGridView', array(
   'type'=>'bordered striped',
    'id' => 'tbl-recibo-grid',
    'dataProvider' => $model->search(),
    'htmlOptions'   =>array('style' => 'font-size: 16px;font-weight: normal'),
    'columns' => array(
        'idrecibo',
        'dom.matricula',
        'dom.federado',
        'ent.nombre',
        'dom.calle',
        'rec.cantidad',
        'cal.periodo',
        'est.ult_modif',
        'os.nombre',
    ),
));
?>

When executing I have no error just that the GridView does not show the results of the queries. I know it should not be anything complicated because, debugging the values ​​are the issue is that I do not understand how to pass them to the Vista.

Answer 1

With select like r.idRecibo,d.matricula,d.federado, ... result will be returned with keys like idRecibo, matricula, federado etc. Tho handle such query you need to add all fields from joined tables to your model:

TblRecibo extends CActiveRecord {

    public $matricula;
    public $federado;
    // ...
}

And then in grid view access them by:

'columns' => array(
    'idRecibo',
    'matricula',
    'federado',
    // ...
),

---

But for such custom queries it may be easier to use CSqlDataProvider instead of CActiveDataProvider - you will not need to add properties to model only for this search - result will be returned as array:

public function search() {      
    $from = 'FROM tbl_recibo as r '
            . 'LEFT JOIN tbl_calendario as cal on r.idcalendario=cal.idcalendario '
            . 'LEFT JOIN tbl_estadorecibo_has_tbl_recibo as est on r.idrecibo=est.idrecibo '
            . 'LEFT JOIN tbl_domicilio as d ON r.iddomicilioapertura=d.idDomicilio '
            . 'LEFT JOIN tbl_entidad as e ON d.idEntidad=e.idEntidad '
            . 'LEFT JOIN tbl_itemreciboweb as rweb on r.idrecibo=rweb.idrecibo '
            . 'LEFT JOIN tbl_ooss as os on rweb.idooss=os.idooss';
    $count = Yii::app()->db->createCommand('SELECT COUNT(*) ' . $from)->queryScalar();
    $sql = 'SELECT r.idRecibo,d.matricula,d.federado,e.nombre,d.calle,r.cantidad,cal.periodo,est.ult_modif,os.nombre ' . $from; 

    return new CSqlDataProvider($sql, array(
        'totalItemCount' => $count,
        'pagination' => array(
             'pageSize' => Yii::app()->user->getState('pageSize', Yii::app()->params['defaultPageSize']),
        ),
    ));
}