Sorting with CStatRelation

Archives Yii 1.0.x Discussions
1

Hey!

I have trouble to sort my data by a CStatRelation.

For example, in the blog-tutorial, if i want to sort the posts by the number of its comments:




//Post model

public function relations() {

	return array(

		//Statistical Query

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

	...







//PostController

public function actionList() {

	...

	$sort=new CSort('Link');

	$sort->applyOrder($criteria);


	//eager loading...

	$models=Post::model()->with(array('author','commentCount'))->findAll($criteria);

	...

The problem is that the statistical query results in an extra query, like:




SELECT `postId` AS `c`, COUNT(*) AS `s` FROM `Comment` HAVING `Comment`.`postId` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

Because the count is not in the "main query" for the post, it cannot be used to sort the posts by it.

Any ideas how to solve this?

2

Maybe you should enforce relational query with calling together() before findAll(). I believe this way you can sort by commentCount’s alias.

Welcome to the forums!

3

Unfortunately that’s not working.

And according to the docs, CStatRelation has no property together.

4

No, I meant $models=Post::model()->with(array(‘author’,‘commentCount’))->together()->findAll($criteria);

If this doesn’t work, then I fear you have to add an extra column to store the stats.

5

Sorry I made it a bit short.

I got you in the first place and unfortunately it is not working.

I meant compared to CBelongsToRelation and CManyToManyRelation, CStatsRelation has no property together. That might indicate that together() is not working with CstatsRelation.

Maybe you are right and I have to make another column, but that would be quite annoying in my case. I have one table which belongs to three other tables and each of them has the StatRelation to it. So for creating one entry I would have to update 4 tables…

6

Yes, after looking into API, I came to this conclusion as well.

I agree that is unnecessarily redundant to store count.

I created a ticket, maybe future releases will support sorting by item count.

7

I hope so. Thanks for your support :)

8

I just realized that there are even more drawbacks. You also cannot sort by avg and sum columns.

So if you have a movie collection and want to sort by average rating - no way.

Or another application of mine is a management tool for URLs similar to tinyurl. Every URL is organized in different categories and every visit is tracked. So if i want to see which category has the most visits, i cannot do this right now.

9

If you have so many categories that you implemented pagination, then it’s true this is not available.

Although, if you select all rows from database, you can still sort them by item count php-side.

10

Thats true, but I wanted to avoid this. Also the performance wouldn’t be as good as with the MySQL sorting.

But it seems that there is no better way at the moment…

11

you can use "group by" in the AR criteria and order by a cdbexpression

12

That’s true, but that also means manually crafting relations, which is a total waste with our existing AR functionalities.