CODEWITHSUNDEEP
yii
Avanche
Avanche

Reputation: 1810

Yii CActiveDataProvider Join and select from joined table

I have the following example code:

                $dataProvider = new CActiveDataProvider('firstTable', 
                    array('criteria' => array(
                        'select' => 't.firstfield,secondTable.secondfield',
                        'join' => 'join secondTable on secondTable.id=t.secondTable_id',
                    ),
                    'pagination' => array(
                        'pageSize' => 10,
                    ),
                ));
                $results=$dataProvider->getData();

After running the code above, firstField (from the model table - firstTable) is available in the object, but secondField (from the joined table - secondTable) is not.

Can anyone provide assistance on what is wrong with the code or why the "select" option is not picking up the secondField?

Upvotes: 0

Views: 10985

Answers (2)

bool.dev
bool.dev

Reputation: 17478

Can anyone provide assistance on what is wrong with the code or why the "select" option is not picking up the secondField?

Answer:

That is happening because you have not selected the field which relates the two tables, i.e the foreign key in firstTable : secondTable_id. So if you do:

'select' => 't.firstfield,t.secondTable_id,secondTable.secondfield',

you will be able to access the secondField of secondTable:

$singleresultrow->secondTableRelationName['secondField'];// a single result row can be obtained by foreach iteration over $results

However there will still be another query (Lazy Loading) when you access the secondField. And the initial(before lazy) object returned will not have the secondTable object filled.

The problem i think lies in the fact that by default yii accesses the related fields by lazy loading, and for that to happen the related foreign_key should be present in the model you are trying to make a dataprovider of, here it is firstTable, and the foreign_key secondTable_id.

Upvotes: 0

Onkar Janwa
Onkar Janwa

Reputation: 3950

it would be better if you use CDbCriteria, that has a better solution to join table with the help of relations. I can show the example with CDbCriteria.

$criteria = new CDbCriteria;
$criteria->select = 'firstfield';
$criteria->with = array('secondTable_relation'=>array('select'=>'secondfield'));
$dataProvider = new CActiveDataProvider('firstTable', 
                    array('criteria' => $criteria,
                    'pagination' => array(
                        'pageSize' => 10,
                    ),
                ));
                $results=$dataProvider->getData();

secondTable_relation is a relation name with secondTable.

Upvotes: 6

Related Questions