Relation And Count

I have two tables:

1- article(id , title, lead, abstract ,…)

2- article_views(id, article_id, ip, …)

relation beetween and article_view.article_id

I need show popular articles,

So, I do this:

in article model class i have:

public function relations()



	'articleViews' => array(self::HAS_MANY,'ArticleViews', 'article_id'),

	'viewCount' => array(self::STAT, 'ArticleViews','article_id'),





public function getPopularArticles($limit)




in getPopularArticles I try this code:

    $articles = $this->with('viewCount')->findAll(array('limit'=>$limit));

So, how can I add order by for viewCount?

Also in think my code is bad(maybe wrong) because in log i see this code:

SELECT `article_id` AS `c`, COUNT(*) AS `s` FROM

`wom_article_views` `t` WHERE (`t`.`article_id` IN (1, 2, 3, 5, 6, 7, 8, 9,

10, 11,

But my query must be:

SELECT wom_article.title, COUNT( ) AS c

FROM wom_article

INNER JOIN wom_article_views ON ( = wom_article_views.article_id )



Whats wrong?

always You can try createCommand() like for example


    ->select('id, username, profile')

    ->from('tbl_user u')

    ->join('tbl_profile p', '')

    ->where('id=:id', array(':id'=>$id))




Hi reza, welcome to the forum.

Unfortunately we can not use STAT relation for filtering or sorting, because it is always loaded lazily. The table for a STAT relation is not joined in the main query.

Please take a look at this wiki article.

Dear mirunho, it’s good, but when i’m in model why i must create another sqlCommand?

Tnx, softark,

thast good idea, and worked perfect for me!.