Yii Framework Forum: Order by count using STAT relation? - Yii Framework Forum

Jump to content

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

Order by count using STAT relation? Rate Topic: ***** 1 Votes

#1 User is offline   Alex Muir 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 96
  • Joined: 25-April 09

Posted 18 March 2010 - 12:14 PM

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?
My sites: The Big Eat and The Big Space
0

#2 User is offline   Alex Muir 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 96
  • Joined: 25-April 09

Posted 19 March 2010 - 07:26 PM

I'm not one for bumping but I'm completely stuck on this still.
My sites: The Big Eat and The Big Space
0

#3 User is offline   Madbreaks 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 19
  • Joined: 09-September 10

Posted 18 October 2010 - 01:31 PM

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

*bump*
0

#4 User is offline   Boudewijn 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 20-October 10

Posted 20 October 2010 - 05:34 AM

same for me ....
cant find a solution
0

#5 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 20 October 2010 - 05:37 AM

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
0

#6 User is offline   mschmidt 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 11-October 10

Posted 16 December 2010 - 11:14 AM

Nothing here yet?!
I'm also stuck with this :(
0

#7 User is offline   Dana 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 203
  • Joined: 18-February 10
  • Location:Florida, USA

Posted 16 December 2010 - 12:21 PM

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

#8 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 17 December 2010 - 03:45 AM

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:

Quote

$posts=Post::model()->with('commentCount', 'categoryCount')->findAll();

The above statement will execute three SQLs to bring back all posts together with their comment counts and category counts. Using the lazy loading approach, we would end up with 2*N+1 SQL queries if there are N posts.


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

#9 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,340
  • Joined: 12-October 09
  • Location:Croatia

Posted 17 December 2010 - 04:42 AM

I too tryed a few options... but seems that the STAT relation always makes a separate query to get the value...
Find more about me.... btw. Do you know your WAN IP?
0

#10 User is offline   Loren 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 29-December 10
  • Location:SC, USA

Posted 01 June 2011 - 09:12 AM

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

#11 User is offline   Mike H 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 17-July 11
  • Location:Manchester

Posted 17 January 2012 - 08:32 AM

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',
           ));

2

#12 User is offline   story 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 20
  • Joined: 14-February 11
  • Location:San Antonio

Posted 17 March 2012 - 03:15 AM

View PostMike H, on 17 January 2012 - 08:32 AM, said:

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.
Posted Image
1

#13 User is offline   seng 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 41
  • Joined: 02-July 10
  • Location:Chennai, India

Posted 17 March 2012 - 02:13 PM

View Poststory, on 17 March 2012 - 03:15 AM, said:

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

#14 User is offline   softark 

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

Posted 17 March 2012 - 06:36 PM

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

#15 User is offline   Boaz 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 363
  • Joined: 23-January 11

Posted 11 October 2012 - 05:39 AM

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! :)
Therapeutic PHP sessions My LinkedIn Profile
0

#16 User is offline   rajesh chaurasia 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 42
  • Joined: 12-January 13
  • Location:Mohali

Posted 27 May 2013 - 12:37 AM

View PostAlex Muir, on 18 March 2010 - 12:14 PM, said:

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?


hi you can use
'commentCount' => array(self::STAT, 'Comment', 'post_id', 'condition'=>'`model_type`="'.get_class($this) .'"'),
0

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