Yii cgrid view sorting

Question

I am working on Visitors Report Section in my site which build in yii. My cgrid view record is show below.

HostName        Visitors Counts      Detail
 facbook.com     40                   click here   
 google.com      150                  click here  
 yahoo.com       90                   click here 

I just want to sort this data as higher visitors move up on the cgrid view. Just like google.com has provide me highest visitor so It move up the CGridView. How It's Possible? Any suggestion please. I am Count visitor at run time when data show in CGridview I create a function and pass it to hostname as parameter. How can I sort this ??

In CGridView,

array( 'name'=>'id',
        'value'=>'visits::count_VisitorsByHostName($data->hostname)',
        'header'=>'Visits'
   ),

In Model visits.php

public function count_VisitorsByHostName($hostname){            

    $connection = Yii::app()->db;               
    $query = "SELECT COUNT(id) as Visitors FROM visits WHERE hostname = '$hostname'";
    $connection->createCommand($query)->queryAll();             
    $count_visitors =  $count_record[0]['Visitors'];                
    return $count_visitors;                     
}

Answer 1

You can do this by adding the visitor count to your initial query, and then your visitor count can become a sortable column in your CGridView.

So your controller can look as normal;

class VisitsController extends Controller
{
    ...

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

    ...
}

In your model, you'll need to declare a variable to hold the count search, and then add this into your search() method like so;

class Visits extends CActiveRecord
{
    /**
     * @var array Allows model filtering by visit count
     */
    public $visit_count;

    ...

    // Remember to declare it as safe
    public function rules()
    {
        return array(
            ...
            array(... ,'visit_count', ..., 'safe', 'on'=>'search'),
            ...
        );
    }

    ...

    // Give it a label if you wish
    public function attributeLabels()
    {
        return array(
            ...
            'visit_count' => 'Visitors Counts',
            ...
        );
    }

    ...

    // Now add to your search method
    public function search()
    {
        $criteria=new CDbCriteria;
        $visit_table = Visits::model()->tableName();
        $visit_count_sql = "(select count(*) from `$visit_table` where `$visit_table`.`hostname` = `t`.`hostname`)";
        $criteria->select = array(
            '*',
            $visit_count_sql . " as `visit_count`",
        );

        $criteria->compare($visit_count_sql, $this->visit_count);

        // Set the CActiveDataProvider to order by the visitor count
        $criteria->order = 'visit_count DESC';

        ...
        // All your other criteria code
        ...

        // And add to the CActiveDataProvider
        return new CActiveDataProvider($this, array(
            'criteria'=>$criteria,
            'sort'=>array(
                'attributes'=>array(
                    'visit_count'=>array(
                        'asc'=>'visit_count',
                        'desc'=>'visit_count DESC',
                    ),
                '*',
                ),
            ),
            'pagination'=>array(
                'pageSize'=>100,
            ),
        ));
    }

    ...
}

You can then add this to your CGridView in your view;

$this->widget('zii.widgets.grid.CGridView', array(
    'id'=>'visit-grid',
    'dataProvider'=>$model->search(),
    'filter'=>$model,
    'columns'=>array(
        'hostname',
        'visit_count',
        array(
            // Whatever your detail column looks like!
        ),
        array(
            'class'=>'CButtonColumn',
        ),
    ),
));

I've not tested that, but I've stripped it from a more complicated model from one of my own projects, so it may well need some editing and variable names will most likely need changing for your app, but the basic process flow is there.

Answer 2

Just define the function in the controller and call in the gridview value.

array('name'=>'id','value'=>array($this,'functionName')),

And in the controller add this action:

public function functionName($data,$row){

//Your logic//
Return value;
}