Order by count using STAT relation?

I might be overlooking the obvious here, but I can’t for the life of me work out how to do this.

Tag is a model with the following relations




public function relations()

{

	return array(

		'count'=>array(self::STAT,'Question','TagQuestion(tagId,questionId)'),

		'questions'=>array(self::MANY_MANY,'Question','TagQuestion(tagId,questionId)'),

	);

}



I want to get a list of all tags, ordered by the number of times they have been used (the ‘count’ relation). Can anyone suggest how I do this? I’ve variously tried:




$tags = Tag::model()->with('count')->findAll(array('order'=>'count'); // No such column error

$tags = Tag::model()->with('count')->findAll(array('order'=>'s'); // Works but doesn't sort.  (s is the alias used for the count(*) expression produced by the SQL query.



The count relation works fine, I just can’t work out how to sort by it.

I’ve tried including together() with no success. If using AR isn’t going to work, can anyone suggest a good method to do this?

I’m not one for bumping but I’m completely stuck on this still.

I’d like to know the answer to this too.

bump

same for me …

cant find a solution

about stat relationship I cannot help you, anyway using CDbCriteria select, join and orderby is possible to create the query you need.

Is not a pretty solution, maybe is a bad workaround, but it works

Nothing here yet?!

I’m also stuck with this :(

I was actually banging my head on this one the other day. It seems like it should be so simple, but I kept running into column not defined errors in my queries.

I agree to zaccaria: I also hardly ever use statistical relations in my projects, because all they do is to perform some more queries to find the number of related items. At least that’s how i understood them. From the guide:

So i can’t find the advantage of using statistical queries at all.

I too tryed a few options… but seems that the STAT relation always makes a separate query to get the value…

Yii cannot join in the stat relations because COUNT and SUM in MySQL come out wrong if their are other joins which can make MySQL artificially double count some rows. (I’ve had it happen before in a different project where I had to split out the stat queries.)

Using with for stat queries is extremely useful when selecting a list of things because if you select 10 members Yii will then run one stat query for each one rather than running a stat query for each member.

Anyway, so since the stat queries are joined by Yii after the select, you cannot sort on the MySQL side, but you can use usort to sort in PHP.

Eg: (Start Time for us is a stat relation foreign key because it is the min(time) from another table.)


    	$calls= Call::model()->with(

        	array('submitTo0','submitter0','approver0','callStatus','startTime',

            	'supervisor0'

        	)

    	)->findAll($criteria);


    	usort($calls, "PayrollController::sortCalls");

Then, you also need the static function sortCalls in your controller.


	/**

 	* Used by usort to sort the calls

 	*

 	* @param <Call> $a First Call

 	* @param <Call> $b Second Call

 	*

 	* @return <int> Sort

 	*/

	public static function sortCalls($a, $<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' /> {

    	if ($a->startTime == $b->startTime) {

        	return 0;

    	} elseif ($a->startTime < $b->startTime) {

        	return -1;

    	} else {

        	return 1;

    	}

	}

For those looking to use this with gridView, I’m not sure how that will work, but I’m working on a custom grid view for our project that I intend to contribute back to Yii so you can watch for that as it will be able to sort on stat queries.

It’s possible to build a csort clause that mimics the STAT query which you can then use in CDBCriteria.

Example assuming we are linking to a table called posts:




      $sort = new CSort();

      $sort->attributes = array(

           'postCountSort' => array(

           'asc' => '(SELECT count(posts.id) FROM posts

                     WHERE posts.author_id = t.author_id) ASC',

           'desc' => '(SELECT count(posts.id) FROM posts

                     WHERE posts.author_id = t.author_id) DESC',

           ));

@Mike H You rock! Nice solution.

I too struggle for the same! Could you please share the complete code how you used CSort in the context and also about how this should be rendered in view.

@Core Dev Team: It’s quite natural for an app to sort the rows based on STAT relations. Add this functionality at core so we don’t need to write custom queries, please.

This is what I have written for one of my models.




...

public $child_count;

...

public function search($page_size = 20)

{

        $criteria=new CDbCriteria;


        $child_table = NnsChild::model()->tableName();

        $child_count_sql = "(select count(*) from $child_table where $child_table.parent_id = t.id)";

        $criteria->select = array(

                '*',

                $child_count_sql . " as child_count",

        );


        ...

        $criteria->compare($child_count_sql, $this->child_count);

        ...


        return new CActiveDataProvider(get_class($this), array(

                'criteria' => $criteria,

                'sort' => array(

                        'defaultOrder' => 't.disp_order',

                        'attributes' => array(

				...

                                'child_count' => array(

                                        'asc' => 'child_count ASC',

                                        'desc' => 'child_count DESC',

                                ),

                                '*',

                        ),

                ),

                'pagination' => array(

                        'pageSize' => $page_size,

                ),

        ));

}




The key points are the "$child_count" attribute of the model and the "$child_count_sql" variable in search() function.

In this way, I could filter and sort by "child_count".

The property name of "child_count" can be the same as the STAT relation.

If the property name of "child_count" is the same as the STAT relation, then property name will take precedence and the STAT relation will not work.

I’ve just needed this too - self::STAT relation type with named scope. NoGo, as already noticed in this threads and others.

My solution was simple and I thought I can share since its not detailed above:

I have ‘Comment’ and ‘CommentVote’ in a module I’m developing. A comment vote can be ‘up’ or ‘down’. Also, a CommentVote can be published or not.

I needed to get the number of ‘published votes’ so STAT was my initial thought, to use an already defined relation in the ‘Comment’ class named ‘votesCount’ but I couldn’t add the named scope on to its definition… .

So, my solution is as follows:

Instead of this:




$published_votes_count = $this->votesCount; // assuming this relation is having some named scope in it - this isn't possible...



I use this:




// the next is a method that contains a simple CDbCommand object that ends with queryScalar() to return just the figure, counter

$published_votes_count = $this->publishedVotesCount();

// also, I have 'upVotes' relation which return all CommentVotes that are 'up'. In order to get the number of those up votes I just used:

$up_votes_count = count($this->upVotes);

That’s all folks! :)

hi you can use

‘commentCount’ => array(self::STAT, ‘Comment’, ‘post_id’, ‘condition’=>’model_type="’.get_class($this) .’"’),