many to many relation problem

Yii 1.1 General Discussion
1

two tables: article_category, article

article_category(article_id, category_id)

article(id, title)

One article could have many categories

One category could have many articles

So the relation in Article model is

‘categories’=>array(self::MANY_MANY, ‘ArticleCategory’,

            'z_article_category(article_id, category_id)'),

My question is, if want to get the articles by category id, how to do it and need the pagination. I use $criteria = new CDbCriteria(); to generate pagination

Thanks

2

Try using a join:




$criteria = new CDbCriteria;

$criteria->join = 'LEFT JOIN article_category ac ON id = ac.article_id AND ac.category_id = :categoryId';

$criteria->params = array(':categoryId' => $categoryId);

or something along those lines. (You’ll still need to make the other relevant SQL statements and disambiguate the table names as required).

3

Is it possible to echo the raw sql to debug when use CDbCriteria?

4

Just use database profiling, stick




'enableProfiling'=>true,

in the ‘db’ section of your config. It’s really verbose so you’ll need to search for the output; also, some sort of logging needs to be on.

5

Need to put the where condition in the addCondition method. It solved

$criteria = new CDbCriteria();

$criteria->join = ‘LEFT JOIN article_category ac ON t.id = ac.article_id’;

$criteria->addCondition(‘ac.category_id = :categoryId’);

$criteria->params = array(’:categoryId’ => $categoryId);

This one doesn’t work

$criteria->join = ‘LEFT JOIN article_category ac ON id = ac.article_id AND ac.category_id = :categoryId’;

Another question

Is it necessary to use join for the requirement?

Could we use the orm relation to get the articles by category id

6

Sorry shouldn’t write code when tired, of course the condition needed to be in a where clause.

There is probably an ORM way but it’s not really the “right way” here because you don’t need to access the other record’s data. If you really wanted to you could use ::with()

http://www.yiiframework.com/doc/api/1.1/CActiveRecord#with-detail