Order by using a statistical relation

Archives Yii 1.0.x Discussions
1

Hi,

I've just started using Yii and I seem to be hitting a bit of a wall with the statistical relations.

Here's my scenario:

I have two objects, Posts and Comments.

Post has many Comments.

I would like to get all Posts ordered by the number of Comments they have.

I've been able to get a count of the comments for Posts using a statistical relation like so:

 // relations() specification in Post model


public function relations()


	{


		return array(


			'creator'=>array(self::BELONGS_TO, 'User', 'userId'),


			'comment'=>array(self::HAS_MANY, 'Comment', 'postId',


				'with'=>'User')


			,'commentCount'=>array(self::STAT, 'Comment', 'postId'),


		);


	}

and then using the following code to retrieve the list:

 // from Post controller


$postList=Post::model()->with('creator', 'commentCount')->findAll($criteria);

but I can't seem to find how to sort the Post object using the count from the Comments.

I currently get around this by using findAllBySql but it really doesn't seem like the most elegant or extensible solution. Any ideas?

2

try:



'commentCount'=>array(self::STAT, 'Comment', 'postId','order'=>'count(*) DESC'),
3

Quote

try: 


'commentCount'=>array(self::STAT, 'Comment', 'postId','order'=>'count(*) DESC'),

Thanks for the reply, will. I'd tried this, but it didn't seem to have any effect on the list of Posts returned. From my understanding, that would order the Comment table but not the Post table.

4

what is the $criteria like?

5

Quote

what is the $criteria like?

$criteria is empty. I tried:



$criteria->order='count(Comment.id) DESC';

but got no love. is there any way to see what SQL yii is generating? I have the feeling it's performing the count query separately.

6

Have you tried creating a database view? This would allow you to order any columns.

7

To see the SQL commands executed, just add the log component to your main.php.

'components'=>array(


....


      'log'=>array(


         'class'=>'CLogRouter',


         'routes'=>array(


                array(


                    'class'=>'CWebLogRoute',


                ),





      ),


),
8

I'm interested in this too, as i'm currently trying to use CSort to sort a listing using a STAT relation as well.

Any one figure this out yet?

9

hmm this is a bit confusing isn't it?

Why you don't try something like this:

because on http://www.yiiframew…l-query-options page i found this:

Or do I understand something absolutly wrong?

greetings

phil

10

Just read the Definitive Guide a little more:

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.

And yes, it seems that the counts are separately executed SQLs according to the above statement. So it becomes a little difficult to order our results by the STAT count.

Does the Yii dev team have any suggestions to help?

11

The implementation of STAT doesn't allow you to do this ordering, as you already found that STAT are  queried separately.

12

Thanks Qiang.

Any guidance you may be able to provide in implementing the ordering via other methods (e.g. CDbExp​ression?) will be much appreciated, as i feel this might be widely used.

The ability for example to sort a listing based on the number of views on an item (whereby the views are stored in a ViewLog table), or things like sorting items with the most number of reviews/comments is probably pretty commonplace.

13

Any chance of the above being provided?

14

We are not going to support this because it is overly complicated in order to provide a generic enough implementation.