Yii relations trouble when trying to display CGridView from 2 models

Image describing the tables -> https://i.sstatic.net/ki2YP.jpg

Each of the tables is a model.

Main model which shows through CGridView is "RegularTask".

Now I need it to display fields from "YearlyTask" in the same row.

"hp_id" and "up_id" are FK (foreign keys) in both tables.

I tried to set the relations() in the RegularTask model like this:

'arp' => array(self::BELONGS_TO, 'YearlyTask', 'hp_id, up_id'),

Then I try to display the "is_sent" and "is_reported" fields from YearlyTask by using "arp.is_sent" and "arp.is_reported", but nothing shows up (not even error). While data from RegularTask displays normally.

What am I doing wrong?

Here is a snippet from the dataprovider..

<?php
$dataProvider=new CActiveDataProvider('RegularTask', array(
    'criteria'=>array(
        'condition'=>'t.id_id=' . $model->id,
        'order'=>'t.created DESC',
        'with'=>array('arp'),
    ),
    'pagination'=>array(
        'pageSize'=>10,
    ),
));

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider'=>$dataProvider,
    'columns'=>array(
        'comment',
        'arp.is_sent'
    ),
));
?>

Solution

Foreign key in one table must be primary key in linked table. If you look into SQL statement created by Yii with your structure, you will see something like this (simplified):

SELECT `t`.*, `arp`.* FROM `RegularTask` `t` LEFT OUTER JOIN `YearlyTask` `arp` ON (`t`.`hp_id`=`arp`.`id`) AND (`t`.`up_id`=`arp`.`id`)

As you can see, what it looks for is that both RegularTask.hp_id and RegularTask.up_id have same value as YearlyTask.id which isn't correct. You have several solutions. One of them is create new column in RegularTask like yt_id which will be foreign key for YearlyTask.id and in my opinion it's the best solution.

Otherwise you can delete your primary key column id in RegularTask table and make hp_id and up_id as complex primary key. In this case you can use the way you tried before, but as i don't know your full db structure i can't guarantee that it's good solution.