how to query relational data in yii

Question

I have created a controller for retrieving data in JSON format for which I need all relational data in json format

my database is like

TABLE_1
_id    category1
 1      fruit
 2      vegetable

TABLE_2
_id    type
 1      winter
 2      summer

TABLE_3
_id     name    cata_table1    cata_table2    
1       apple   1               2

Here cata_table1 is foreign key to TABLE_1 and cata_table2 foreign key to TABLE_2

$sql="SELECT * from  TABLE_3";
$command=$connection->createCommand($sql);
$row=$command->queryAll();

How should I query relational data to output value of cata_table1 field from table1 and cata_table2 from table2 so that my result query output has `_id, name, TABLE_1.category1 , TABLE_2.type

model TABLE_3

class TABLE_3 extends CActiveRecord
{
public static function model($className=__CLASS__)
{
    return parent::model($className);
}

public function tableName()
{
    return 'TABLE_3';
}
public function rules()
{
    return array(
        array('_id,name,cata_table1,cata_table2', 'required'),
        array('_id, name, cata_table1, cata_table2', 'safe', 'on'=>'search'),
    );
}
public function relations()
{

}

Can u post your model for table_3 using relation we can do it — Ninad, Jun 20 '13 at 12:12

score 0 · Answer 1 · answered Jun 20 '13 at 12:26

0

Try this

You may be having relation in your TABLE_3 model as shown

public function relations()
    {
       'relation1' => array(self::BELONGS_TO, 'TABLE_1', '_id'),
       'relation2' => array(self::BELONGS_TO, 'TABLE_2', '_id'),
      }

Do the same as u have done above then do something like this

$sql="SELECT * from  TABLE_3";
$command=$connection->createCommand($sql);
$row=$command->queryAll();
$sample=array();
foreach($row as $key=>$value){
   $sample=array('id'=>$value->id,'name'=>$value->name,'name1'=>$value->relation1->category1,'name2'=>$value->relation2->type)
}

After that encode to json

echo CJSON::encode($sample);

Check some syntax may be this is not perfect answer

answered Jun 20 '13 at 12:26

Ninad

1,871
3
15
23

thanks for this but relation is not accessible inside foreach loop for $value – Bipin Chandra Tripathi Jun 20 '13 at 13:00
Generate the model once again for your TABLE_3 model and try the relation names generated by gii tool – Ninad Jun 20 '13 at 13:14
The Relation should be accessible if the $value is an object of specific record – Ninad Jun 20 '13 at 13:24

score 0 · Answer 2 · answered Jun 20 '13 at 13:13

Create the relations like Ninad said in TABLE_3 model:

public function relations(){
   'relation1' => array(self::BELONGS_TO, 'TABLE_1', '_id'),
   'relation2' => array(self::BELONGS_TO, 'TABLE_2', '_id'),
}

In the action:

$results = array();
$models = TABLE_3::model()->with(array('table1','table2'))->findAll();
foreach($models as $model){
   $results[] = array($model->name, $model->relation1->category1, $model->relation2->type);
}
echo json_encode($results);

how to query relational data in yii

2 Answers2