Joins with 3 tables CDbCriteria


My problem is that i am not sure how to render a view and still be able to show columns from tables other than

related to the view being rendered.

Below is my controller code:

public function actionIndex()


       $ArenaCriteria = new CDbCriteria();

       $ArenaCriteria-> select = "t.Arena_ID, t.Arena_name, d.Fname, d.Lname ";

       $ArenaCriteria-> join =   " INNER JOIN tbl_Arena_Deputy AD ON AD.Arena_ID = t.Arena_ID

                                   INNER JOIN tbl_Deputy d ON d.Deputy_ID = AD.Deputy_ID";

$this->render(‘index’, array(‘Arenas’=> Arena::model() -> findAll($ArenaCriteria),

                                 //   'Deputies'=> Deputy::model() -> findAll($ArenaCriteria)



Below is my code in view being rendered:


foreach($Arenas as $Arena) {

echo $Arena-> Arena_name;

echo $Arena -> Fname;



Arena_name is displayed with no issues but i get below error for Fname

Property "Arena.Fname" is not defined.

Since the Fname comes from another table named Deputy and i am rendering the view using only Arena::model(), view cannot recognize Fname and i understand that.

My question is there something i need to do while rendering that will help my view to display the data related to Deputy table?

Have you setup relations() in your model?

Read the Guide section on relational AR.

You should be able to use ‘with’ option in your findAll() query to pull in each deputy that matches an arena. The deputy information will be available as $arena->deputy->lname and so on.

Edit: nothing wrong with using CDbCriteria either but you aren’t really using the power of AR.

hi waitforit,

thanks for quick reply.

While learning Yii, i did went through AR relations but i read in the book that AR is memory efficient but performs bad compared with CDbcriteria in terms of query execution. Also i found it more comfortable using CDbcriteria coming from SQL background and virtually zero experience in OOP.

Can you suggest any link or exactly how to do using CDbcriteria?

Don’t prematurely optimize!

I would recommend to learn and love AR, then rewrite only those AR queries that are a performance bottleneck.

Development time-wise, AR is so much faster and comes with so many benefits - events, validation, etc. If you write SQL you need to handle a lot of stuff yourself that AR would do for you automatically.