Yii Table Joined But How To Retrive Joined Table Fields

Yii 1.1 General Discussion
1

I have the following criteria in my controller.




    $criteria = new CDbCriteria;

    $criteria->select = 't.*,b.*,c.*';

    $criteria->join = "INNER JOIN tbl_patient_chair b ON b.patient = '0005'

                        INNER JOIN tbl_chair c ON b.chair = c.Serial_No";

    $criteria->condition = "t.client_id = '0005'";


    $model = Patient::model()->findAll($criteria);


    $this->render('view', array(

        'model' => $model

    ));

Then in my view I am trying to retrieve all the selected data by doing:


foreach ($model as $models)

  print_r($model);

But in the result I can see that there is no data from second and third table. The data of the first table is successfully retrieved however I cannot display the other table’s data. The database is MyISAM with no foreign keys defined.

Can anyone tell me what I am doing wrong?

2

how many tables do you get?




$criteria->select = 't.*,b.*,c.*';//?
3

I am only getting the all the columns of first table (t.*). Table b and c’s columns are not accessible.

4

Hi john, welcome to the forum.

The AR object of Patient has the attributes to hold the column data of tbl_patient, but has none for the data of tbl_chair or tbl_patient_chair by default.

You can declare the relations for them to hold their data.

Consider using "Relational Active Record". It will make your developer life with Yii much more easy and productive.

It looks like that you have:

  1. tbl_patient

  2. tbl_chair

  3. tbl_patient_chair … link table between tbl_patient and tbl_chair

The relation above can be defined as a MANY_MANY relation.

And once you have established a proper relation, then it will be much easier to retrieve the patients’ chairs, for example.

Please take some time to read "Working with Database" section of the guide.

http://www.yiiframework.com/doc/guide/1.1/en/database.overview

5

yes better read http://www.yiiframework.com/doc/guide/1.1/en/database.overview before …

6

Look into using the AR relations and "with" methods to join tables …very easy.