Relation And Count

Yii 1.1 General Discussion
1

I have two tables:

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

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

relation beetween article.id 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( wom_article_views.id ) AS c

FROM wom_article

INNER JOIN wom_article_views ON ( wom_article.id = wom_article_views.article_id )

GROUP BY wom_article.id

ORDER BY c DESC

Whats wrong?

2

always You can try createCommand() like for example




Yii::app()->db->createCommand()

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

    ->from('tbl_user u')

    ->join('tbl_profile p', 'u.id=p.user_id')

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

    ->order('')

    ->group('')

    ->queryAll();
3

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.

http://www.yiiframework.com/wiki/319/searching-and-sorting-by-count-of-related-items-in-cgridview/

4

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