Filtering on STAT relation CGridView

There are many topics pointing out how to filter on HAS_MANY relations and other related situations. There is information on joining to related tables, but I have been unable to find a real example of how to filter when you have a STAT relation to the same table. I’ve got sorting working but not searching / filtering.

Here’s my relation in model Company:


'editCount' => array(self::STAT, 'Company', 'reference_id'),

now in Company::search():




                $criteria = new CDbCriteria;


                // because we need the STAT editCount for sorting, we need to synthesize our own query.

                // if there is a more AR way to do this someone let me know!

                $criteria->select = 't.*, count(edits.id) AS editCount';

                $criteria->join = 'LEFT OUTER JOIN company edits ON edits.reference_id = t.id';

                $criteria->group = 't.id';


                // we also want to include the user record fields for the related user (last_updated_by_user_id)

                $criteria->with = array('user', 'editCount');



The grid itself loads fine. I am displaying editCount as one of the grid columns. I can sort on editCount properly as I have it defined as the following attribute of the sort associated with the CActiveDataProvider:


'editCount' => array('asc' => 'editCount', 'desc' => 'editCount desc'),

When I submit the filter textfield in the CGridView this search is associated with, I get the following SQL error:

[sql]2011/06/29 19:59:13 [error] [exception.CDbException] exception ‘CDbException’ with

message 'CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not

found: 1054 Unknown column ‘t.editCount’ in ‘where clause’. The SQL statement executed

was: SELECT COUNT(DISTINCT t.id) FROM company t LEFT OUTER JOIN company edits ON

edits.reference_id = t.id LEFT OUTER JOIN user user ON

(t.last_updated_user_id=user.id) WHERE (t.editCount LIKE :ycp0)’ in /var/www

/html/yii-1.1.7.r3135/framework/db/CDbCommand.php:518[/sql]

How do I get Yii to synthesize the editCount parameter for the filtering by the STAT relation?

I don’t need a super detailed explanation of how to do it, just something simple or a pointer in the right direction.

Or perhaps I can state this whole problem as a more straightforward question (yes/no): Does Yii support CDbCriteria comparisons against STAT relations? If so, what is the format?

Anyone? Is this possible with Yii 1.1.7/1.1.8?

IIRC (and at least for mySQL), column aliases cannot be used in the where clause. (Try it in phpMyAdmin.)

/Tommy

but couldn’t it use a HAVING clause?

[later]

Nope. That messes up the pager. Argh.

Okay, I ‘fixed’ this as follows (note this example uses different variable names):

In the model:

add attribute

public $tGroupCount

Mark tGroupCount as Safe on ‘search’

Add the following code in search():


    	if($this->tGroupCount)

    	{

        	$criteria->having = "groupCount = {$this->tGroupCount}";

    	}

but i run into the same pager problems as you… I added this extremely ugly and inefficient piece of code:




$itemcount = 0;

    	if ($this->tGroupCount)

    	{

        	$criteria->having = "groupCount = " . intval($this->tGroupCount);

        	

        	$itemcount = count(self::model()->findAll($criteria));

    	}



Then by appending this piece of configuration to the initalization of CActiveDataProvider




'totalItemCount' => $itemcount ? $itemcount : null,



i had the pager working again…

BUT, once when you use the pager, the filter is ‘set’. I have to do a full page reload before i can search for another value.

This is my first big disappointment in Yii since I started working with this framework. I found topics from over a year ago, which also did not offer an answer for this problem.

For MySQL databases this could also be fixed by making use of SQL_CALC_ROWS in CActiveDataProvider::calculateTotalItemCount()… Or just make it properly use the given criteria!

I gave up. While I started down the path of really trying to use as much ActiveRecord stuff in Yii as I could, I’m using the Query Builder stuff to basically build raw SQL queries for anything moderately complicated at the moment. Looks a lot nastier, but…I needed the additional flexibility. I agree that it would be a major win for the platform if this sort of activity were natively supported.

I’m very intrested to know more about this subject. Anyone still on it?

I didn’t follow this topic.

But just with a glance at the title of it, the following wiki will give you some hint, I hope.

http://www.yiiframework.com/wiki/319/searching-and-sorting-by-count-of-related-items-in-cgridview/

Dear Friends

Without any doubt, the solution given by softark is very elegant.

Before finding that one, I resolved this issue somehow.

I want to share that.

Let us assume that we have 2 models.

1.Brand.: id, name

2.Item: id, name, b_id(FK)

A single brand has many items.(HAS_MANY)

In Brand.php, we have to declare the virtual property ‘itemCount’.

Even there is no need to declare the STAT relation.

Brand.php




class Brand extends CActiveRecord

{   


    public $itemCount;

    public function rules()

    {

	//declare itemCount as safe on search

	return array(

		array('name', 'required'),

		array('name', 'length', 'max'=>256),

		array('id, name,itemCount', 'safe', 'on'=>'search')

                   );

    }


    public function relations()

	{

		

		return array(

			'items' => array(self::HAS_MANY, 'Item', 'b_id'),

			

		);

	}


      

    public function search()

{

           $criteria=new CDbCriteria;

           $criteria->join="LEFT JOIN item On t.id=item.b_id";

           $criteria->select=array('t.id','t.name','COUNT(item.name) AS itemCount');

	   $criteria->compare('t.id',$this->id);

	   $criteria->compare('t.name',$this->name,true);

	   $criteria->group='t.name';

		

		if($this->itemCount && preg_match('/^(>|<)?(=|>)?\d+$/',$this->itemCount))

			$criteria->having="itemCount".$this->itemCount;

		if($this->itemCount && is_numeric($this->itemCount))

			$criteria->having="itemCount = ".$this->itemCount;	


		return new CActiveDataProvider('Brand',array(

                  'criteria'=>$criteria,

		  'sort'=>array(

		  'attributes'=>array('id','name','itemCount'=>array('asc'=>'itemCount ASC','desc'=>'itemCount DESC'))),

		  'pagination'=>array('pageSize'=>10),

		          ));

	}




For CArrayDataProvider we can modify the search method.




public function search()

{

	


	$criteria=new CDbCriteria;

        $criteria->join="LEFT JOIN item On t.id=item.b_id";

        $criteria->select=array('t.id','t.name','COUNT(item.name) AS itemCount');

	$criteria->compare('t.id',$this->id);

	$criteria->compare('t.name',$this->name,true);

	$criteria->group='t.name';

		

	if($this->itemCount && preg_match('/^(>|<)?(=|>)?\d+$/',$this->itemCount))

		$criteria->having="itemCount".$this->itemCount;

	if($this->itemCount && is_numeric($this->itemCount))

		$criteria->having="itemCount = ".$this->itemCount;

		

	$brands=Brand::model()->findAll($criteria);

		


	return new CArrayDataProvider($brands,array(

		'sort'=>array(

		'attributes'=>array('id','name','itemCount'=>array('asc'=>'itemCount ASC','desc'=>'itemCount DESC'))),

		'pagination'=>array('pageSize'=>10),

		));

	}



In above approaches we can employ sorting as well as filtering with comparision operators(<, <=, >, >=, <> or =).

In controller you have to add one line to unset the values of itemCount.

BrandController.php




public function actionAdmin()

	{

		$model=new Brand('search');

		$model->unsetAttributes();  

		$model->itemCount=null; //This is the added line. the method unsetAttributes() is not going to unset virtual properties.

		if(isset($_GET['Brand']))

			$model->attributes=$_GET['Brand'];

			

		$this->render('admin',array(

			'model'=>$model,

		));

	}



In admin.php just add the property itemcount.




<?php $this->widget('zii.widgets.grid.CGridView', array(

	'id'=>'brand-grid',

	'dataProvider'=>$model->search(),

	'filter'=>$model,

	'columns'=>array(

		'id',

		'name',

		'itemCount',

		array(

			'class'=>'CButtonColumn',

		),

	

	),

)); ?>



We can also extend this approach to MANY_MANY relation for CGridView.

I have made an attempt on this for CListView in the following post.

Sorting on Statistical Relational Attributes