Yii Joined table but not all selected data is retrieved

Question

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.

Can anyone tell me what I am doing wrong?

score 1 · Accepted Answer · answered Jan 04 '13 at 12:38

Joins are best used in Yii by creating Relations , that way you won't need to write complex queries

Start by adding foreign keys in your sql tables (eg add a foreignkey patientId in chair)

Next if you regenerate your model you can see the relations auto-added (or you can manually add the relations)

public function relations()
{

    return array(
        'chairs' => array(self::HAS_MANY, 'chair', 'patientId'),
    );
}

And in the chair model you will see the relation

'patient' => array(self::BELONGS_TO, 'patient', 'patientId'),

Defining the relation alone allows you access to the values in queried model as $model->relationName, if you want to use a column in 'where' condition, use the following query in your model function(s)

$patients=Patient::model()->findAll(array(
                'condition' => "$field like '%$value%'",
                'with'=>array('chairs'),
                'select'=> "*",
                ));

The "with" keyword is important and can take an array of list of relations to include in the query. The query condition will apply to all the tables included, You can skip the "with" keyword here if you do not want to query a field from another table and only need the output data linked.

And you can reach the chairs assigned to the patient by

foreach($patients as $patient)print_r($patient->chairs);

There are other approaches too , like one discussed here

To get more in touch with relations, go here

Thanks for your reply. Supplied database is MyISAM which doesn't take FK. That is the reason I am using CDbCriteria. — Sahil, Jan 04 '13 at 12:43
That still does not stop you from manually defining relations and using them. Only you will not have the liberty of auto-generation of relations. Here is a small discussion related to MyISAM and FK in Yii : http://www.yiiframework.com/forum/index.php/topic/36998-new-to-yii-and-oo-myisam-leave-relations-empty/ — arkoak, Jan 04 '13 at 12:45

Yii Joined table but not all selected data is retrieved

1 Answers1

Linked