Yii Framework Forum: Filtering on STAT relation CGridView - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Filtering on STAT relation CGridView Rate Topic: ***** 1 Votes

#1 User is offline   Preston Brown 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 31
  • Joined: 23-February 11

Posted 29 June 2011 - 08:03 PM

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:

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


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

#2 User is offline   Preston Brown 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 31
  • Joined: 23-February 11

Posted 30 June 2011 - 11:42 AM

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?
0

#3 User is offline   Preston Brown 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 31
  • Joined: 23-February 11

Posted 14 July 2011 - 03:29 PM

View PostPreston Brown, on 30 June 2011 - 11:42 AM, said:

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?
0

#4 User is online   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,654
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 14 July 2011 - 03:58 PM

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

/Tommy
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#5 User is offline   Preston Brown 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 31
  • Joined: 23-February 11

Posted 14 July 2011 - 04:43 PM

View Posttri, on 14 July 2011 - 03:58 PM, said:

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.
0

#6 User is offline   ChessSpider 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 37
  • Joined: 20-July 11
  • Location:Netherlands

Posted 15 August 2011 - 06:31 AM

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!
0

#7 User is offline   Preston Brown 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 31
  • Joined: 23-February 11

Posted 15 August 2011 - 08:18 AM

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.
0

#8 User is offline   Romain 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 02-October 12

Posted 26 October 2012 - 07:42 AM

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

#9 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,997
  • Joined: 16-February 11
  • Location:Japan

Posted 26 October 2012 - 08:24 AM

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.yiiframew...s-in-cgridview/
0

#10 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 26 October 2012 - 10:28 AM

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
1

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users