Yii CGridView and SQL Count

Question

I'm trying to load a cGridView with the results of a query between two tables (charity and votes).

Trying to show the number of votes in the vote table for a charity. The vote table has a FK to the charity table.

I could do this in SQL with a left join, but cGridView requires a CActiveDataProvider object to display the data and I am unsure how I can join the two tables to return a result that not only counts, but also doesn't show any results that are equal to 0 and ordered by the votes.

I currently am doing:

in the Vote Model:

public function relations()
{
    return array(
        'voteCount'=>array(self::STAT, 'Vote', 'charity_id'),
    );
}

charity_id being the FK for the charity table.

Then to build the CGridView widget:

$criteria=new CDbCriteria(array(
'with' => 'voteCount',
));

$dataProvider=new CActiveDataProvider('Charity', array(
    'pagination' => false,
    'criteria' => $criteria,
));

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider'=>$dataProvider,
    'ajaxUpdate'=>true,
    'columns'=>array(
        'Name',
        array(
            'name'=>'vote.voteCount',
            'value'=>'CHtml::encode($data->voteCount)',
        ),
    ),
));

Right now it's returning multiple results and I can't seem to figure out how to sort and add a where clause as well.

Any help?

For the record you don't have to use CActiveDataProvider, can use CSqlDataProvider too, but according to the documentation ofcourse CActiveDataProvider is preferred. — bool.dev
I am not so good with criteria but you can definitely add conditions to criteria like - $criteria->condition = 'voteCount > 0' — Uday Sawant

v.tsurka v.tsurka · Accepted Answer · 2011-10-27T23:40:53

Try add to Charity model

public function relations()
{
    return array(
        'vote'=>array(self::HAS_ONE, 'Vote', 'charity_id'),
    );
}

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider'=>$dataProvider,
    'ajaxUpdate'=>true,
    'columns'=>array(
        'Name',
        array(
            'name'=>'Vote Count',
            'value'=>'CHtml::encode($data->vote->voteCount)',
        ),
    ),
));

Yii CGridView and SQL Count

2 Answers