Cgridview Order By 'count(Product::model()->Findallbyattributes())'?

Yii 1.1 General Discussion
1

I have a CGridView in my Product model that displays page (Url model) clicks (saved in a related ‘Visit’ model), the page id, and the ‘HTTP_REFERER’.

This is an example of my Visit model’s table:




ip		|	productid	|	urlid	|	httpref

--------------------------------------------------------------------------------

111.1.1.1	|	10		|	1	|	http://site1.com	

222.2.2.2	|	10		|	1	|	http://site2.com

333.3.3.3	|	10		|	1	|	http://site1.com

This is my desired CGridView result:




urlid	|	httpref			|	clicks

---------------------------------------------------------

1	|	http://site1.com	|	2

1	|	http://site2.com	|	1

This is the CGRidView in my Product model:




$dataProvider=new CActiveDataProvider('Visit', array(

    'criteria'=>array(

		      'condition' => 'productid=' . $model->id,

		      'select' => 't.urlid, t.httpref',

		      'distinct' => true,

		      )));

		      


$this->widget('bootstrap.widgets.TbGridView',array(

	'id'=>'visit-grid',

	'dataProvider'=>$dataProvider,

	'columns'=>array(


		array(

		     'name'  => 'urlid',

		     'value' => 'CHtml::textField("short", $data->url->short, array("onclick"=>"$(this).select()"))',

		     'type'  => 'raw',

		),

		array(

		     'name'  => 'httpref',

		     'value' => 'CHtml::link($data->httpref)',

		     'type'  => 'raw',

		),

		array(

		     'name'  => 'clicks',

		     'value' => 'count(Visit::model()->findAllByAttributes(array("productid"=>'.$model->id.',"urlid"=>$data->urlid, "httpref"=>$data->httpref)))',




		     'type'  => 'raw',

		),


	),

));

	

	} ?>

So in the CGridView I have a ‘clicks’ column with the following code to display the amount of clicks a certain URL has received from a HTTP_REFERER:


count(Visit::model()->findAllByAttributes(array("urlid"=>$data->urlid, "httpref"=>$data->httpref)))

What I want to do is DESC order the ‘clicks’ (showing the urlid with the most clicks first etc) in the CGRidView. I’d also like to make the column sortable (ASC & DESC).

What’s the best way to do this?

2

You need to define a variable called visitors in the Visits model and then set sorting based on it.

3

I’m not quite sure what you mean. What should go in the variable and how do I go about basing the sorting on it?

4

please check http://www.yiiframework.com/forum/index.php/topic/8784-cgridview-sort-columns/

5

I don’t believe this helps for sorting values that don’t exist in a column ie count() etc.

I believe I should be working with STAT relations or a JOIN query, although I’m not sure how I can integrate that correctly for my ends.

6

I would change the approach by using different criteria for your dataprovider. Use the equivalant of GROUP BY and add a COUNT in the select.