I have a sql query where I want to left join one table on another and count the joined tables rows. The actual query looks like this and it works fine:
SELECT Topic.topicId, description, startDate, count(rantId) FROM Topic left join Rant on Rant.topicId = Topic.topicId
My Topic model has the following to allow a left join with the Rant table:
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(
'rant' => array(self::HAS_MANY, 'Rant', 'topicId'),
);
}
My controller has the following code to execute the sql query and build a dataProvider:
$criteria=new CDbCriteria(array(
'select'=>'description, startDate, count(rantId) as rantCount',
'with'=>array('rant'),
'together'=>true,
'order'=>'createDate desc',
));
$dataProvider=new CActiveDataProvider('Topic', array(
'pagination'=>array(
'pageSize'=>15,
),
'criteria'=>$criteria,
));
$this->render('view',array(
'dataProvider'=>$dataProvider,
));
In the view I have the following:
<?php $this->widget('zii.widgets.grid.CGridView', array(
'dataProvider'=>$dataProvider,
//'filter'=>$model,
'columns'=>array(
array(
'name' => 'Topic',
'value' => '$data->description',
),
array(
'name'=> 'Date listed',
'value'=> '$data->startDate',
),
array(
'name'=> 'Rants',
'value'=> '$data->rant->rantCount',
),
),
));
?>
The problem is that $data->rant->rantCount has no value. The sql runs fine, and I’ve run the sql output from yii and it also brings back the data fine. How come ‘rantCount’ has no data in the $dataProvider object? I am at my wits end on how am I supposed to do this?
