order columns by STAT relation

Hi, I got a problem when I tried to order by a column from STAT relation.

I have a Post model and a Comment model, and the relation in Post is

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

I tried to order by the commentCount in my Post CDbCriteria

$criteria = new CDbCriteria(array(

			'with'=>array('commentCount'),

			'order'=>'commentCount DESC',

			'limit'=>20,

		)); 

But it showed

 Column not found: 1054 Unknown column 'commentCount' in 'order clause'

How can I solve this? thanks!

Try it like this:

$criteria = new CDbCriteria(array(

                        'with'=>array('commentCount'),

                        'order'=>'commentCount.post_id DESC',

                        'limit'=>20,

));

Thanks for helping, but this didn’t work as well.

And what I need is to order by the count of comment, so order by commentCount.post_id seems not right.

Still thanks for trying!

do you have a column "commentCount" in the table "Comment"?

no, the commentCount is the STAT relation in Post.

I have Post and Comment table, and I use commentCount(self::STAT, post_id) to count the number of comments of a post.

Hope I didn’t misunderstand what STAT menas in yii.

Thanks!

I’m not sure that a relation could be used in criteria’s order. Maybe it would be better if you turn on your query logging, and see what query was generated.

Hopefully this thread can shed some light to your problem. I didn’t find an actual answers, but there are realizations

Thanks for your reply, and I think that article explained clearly - the STAT relation is issued with two sql query, so it is not possible to order by STAT relation.

The only thing I can do is to redesign my relations, or wait for someone good at yii to solve this problem.

Thanks anyway!

Hi,

I have similar problem, try to use ‘with’ clause and ordered it by ‘comments.id’ like the code below:

$posts=Post::model()->with(‘comments’)->findAll(array(

'select'=>'t.title, t.content, comments.id',


'order' => 'count(comments.id) desc',


'group' => 't.title, t.content',

));

Don’t forget to add ‘select’ and ‘group’ clause as well.

I hope it can helps.

Cheers,

AR