Relational selects in YII

How do you do relational selects in YII? The kind where you have a many to many relationship on a table. The YII page on relational queries didn't have many good examples on how to do it, so I tried it out and found it to be actually quite easy, its quite like how you would do for single table query.

The above figure was taken from the YII page on relational queries (I was too lazy to do up my own example).

We will just concentrate on the tbl_post , tbl_post_category and the tbl_category tables.

I will assume you know how to overwrite the relations function in CActiveRecord to setup the relations correctly.

So the scenario is, I would like to retrieve posts that are in a certain category. 

The idea is to use the with() method that is part of CActiveRecord, we would like to retrieve the categories together with the Posts.

So the query would look like this:

$posts=Post::model()->with(array('categories'=>array('condition'=>'name="FunStuff"')))->findAll();

In the with portion you specify the relations that you want to load in this case categories, and with it you can specify the different relation query options like select, condition and so on. In this case we use the "condition" option to specify what from categories that we want to retrieve. For this we want to retrieve all the Post with categories name = "FunStuff".

Simple rite!