Defining Relationship Between Models - Create Intermediate Table (New To Yii)

Yii 1.1 General Discussion
1

Hello,

My name is Tanya and I am new to Yii. I would like to create an intermediate table using a join in order to link two models, however they do not share a primary key. I would like to do an inner join of the ID from table 1 and ID from table 2, and have a 3rd table linking the two (acting as an intermediate).

What I am unsure of (being a complete newbie) is how to link these two ids into the third table ..ex. [b]how to alter my relations in the models, and how tosee` this new table.[/b]

Here`s a bit of info on my tables;

Table 1;

Primary key => uniqueID (autoincrement)

name_ID

name

Table 2;

Primary key => uniqueID (autoincrement)

name2_ID

name2

What I would like to do is create a table where I can create a relationship between name_ID (table 1) and name2_ID (table 2), so that when I retrieve name_ID info from table 1, I can also get a hold of name2_ID’s associated with name_ID. It is a has_many relationship => name_ID has many name2_ID. The two tables DO NOT share a common entry found in both (for ex. name1 (table 1) and name1(table2)).

I’m sorry if what I am asking doesn’t make sense, I am having trouble vocalizing this. I have been reading lots of forum examples and online tutorials, but unfortunately I haven`t found a solution to my problem.

Thank you in advance for your help!

2

Hi Tanya, welcome to the forum.

So you want to create an intermediate table like this?




Table 3:

Primary key => uniqueID (autoincrement) ... this may be unnecessary, though

name_ID => FK to Table1.name_ID

name2_ID => FK to Table2.name2_ID

If so, then I think the relations are:




Table_1 HAS_MANY Table_3

and

Table_3 BELONGS_TO Table_2

Or




Table_1 MANY_MANY Table_2 (using Table_3 as an intermediate table)

By default, Yii’s Relational AR assumes that a FK points to the primary key of the related table. But you can specify the custom PK->FK association.

http://www.yiiframework.com/doc/guide/1.1/en/database.arr#declaring-relationship

So, when the relation between Table_1 and Table_3 is FK(Table_3.name_ID)->PK(Table_1.PrimaryKey), then the relation should be defined as:




// Table_1.php

public function relations()

{

    return array(

        'table_3' => array(self::HAS_MANY, 'Table_3', 'name_ID'),

    );

}

But as you want the relation to be custom one, i.e., FK(Table_3.name_ID)->PK(Table_1.name_ID), the relation should be defined as:




// Table_1.php

public function relations()

{

    return array(

        'table_3' => array(self::HAS_MANY, 'Table_3', array('name_ID' => 'name_ID')),

    );

}

The first ‘name_ID’ is FK(Table_3.name_ID), and the second is the custom PK(Table_1.name_ID).

Likewise, the BELONGS_TO relation is defined as:




// Table_3.php

public function relations()

{

    return array(

        'table_2' => array(self::BELONGS_TO, 'Table_2', array('name2_ID' => 'name2_ID')),

    );

}

The first ‘name_ID’ is FK(Table_3.name_ID), and the second is the custom PK(Table_2.name_ID).

Or, you may define a MANY_MANY relation between Table_1 and Table_2 directly:




// Table_1.php

public function relations()

{

    return array(

        'table_2' => array(self::MANY_MANY, 'Table_2', 'table_3(array('name_ID' => 'name_ID'), array('name2_ID' => 'name2_ID'))),

    );

}

Usually a MANY_MANY relation should be defined as:




link_table('fk_to_main_table', 'fk_to_related_table')

and when the PK->FK is custom:




link_table(array('fk_to_main_table'=>'pk_of_main_table'), array('fk_to_related_table'=>'pk_of_related_table'))
3

Hello softark,

Thank you for the quick reply and taking the time to help me.

You were correct in that my goal is to use Table_3 as an intermediate table.

I would just like to confirm my understanding of what you have presented. What I understand is that the script links name_ID and name2_ID in a 3rd intermediate table by using foreign keys.

A second question I would have is (if my understanding is correct) if in table_1 name_ID and name are associated, does this now make name (from table 1) and name2_ID now associated as well (through the name_ID and name2_ID relationship).

Finally a 3rd (newbie!) question I would have is how do I create the Table_3.php file which you mentioned (as Gii had generated all of my other pages for me).

My tables are in MyISAM format, will this cause any issues?

Thank you again for your help!!

4

You have to create those tables manually in the db level at first. Then gii will create the models for them. And, if the tables are InnoDB, gii will also create the proper relations between them at the same time by reading the foreign key constraints.

Even when you have to use MyISAM, you can still define those relations manually by editing model files by yourself. They should work as fine as the gii-generated ones as long as the reading is concerned.

Once the relations are set up correctly, you can access the related models by "->" notation.




// MANY_MANY version

$models = Table_1::model()->findAll();

foreach ($models as $model)

{

    echo "id = " . $model->ID;

    echo "name_ID = " . $model->name_ID;

    echo "name = " . $model->name;

    foreach ($model->table_2s as $rel)

    {

        echo "id = " . $rel->ID;

        echo "name2_ID = " . $rel->name2_ID;

        echo "name = " . $rel->name;

    }

}


// HAS_MANY + BELONGS_TO version

$models = Table_1::model()->findAll();

foreach ($models as $model)

{

    echo "id = " . $model->ID;

    echo "name_ID = " . $model->name_ID;

    echo "name = " . $model->name;

    foreach ($model->table_3s as $link)

    {

        echo "id = " . $link->table_2->ID;

        echo "name2_ID = " . $link->table_2->name2_ID;

        echo "name = " . $link->table_2->name;

    }

}

P.S.

If you don’t have any particular reason to use MyISAM, I would recommend you to switch to InnoDB. It will make your life easier.

5

I have changed the databases to InnoDB. Yii didn’t make the relations though. I am going to try your suggestions now.

Thank you again for your help,

Tanya

Just wondering if the following goes in the controller file (for each loop you suggested earlier).




// MANY_MANY version

$models = Table_1::model()->findAll();

foreach ($models as $model)

{

    echo "id = " . $model->ID;

    echo "name_ID = " . $model->name_ID;

    echo "name = " . $model->name;

    foreach ($model->table_2s as $rel)

    {

        echo "id = " . $rel->ID;

        echo "name2_ID = " . $rel->name2_ID;

        echo "name = " . $rel->name;

    }

}


// HAS_MANY + BELONGS_TO version

$models = Table_1::model()->findAll();

foreach ($models as $model)

{

    echo "id = " . $model->ID;

    echo "name_ID = " . $model->name_ID;

    echo "name = " . $model->name;

    foreach ($model->table_3s as $link)

    {

        echo "id = " . $link->table_2->ID;

        echo "name2_ID = " . $link->table_2->name2_ID;

        echo "name = " . $link->table_2->name;

    }

}