Thursday, June 24, 2010

Adding Foreign Keys Manually

Using MyISAM tables (in MySQL), I'm unable to set foreign keys in the database. However, I'm working with several tables which utilize foreign key type relationships. The problem was how to get Yii to recognize those relationships properly.

This one took me a while to figure out. With the help of the experts over at the Yii Forums, I was able to put together this solution.

(Not going to debate the wisdom of the setup of the scenario, it's just an example :p)

Scenario:
Table 1- Messages (items displayed to users) - identified publicly by "sid"
Table 2- Clicks (tracks clicked messages) - records the message "sid" on receipt, not it's id.

In the clicks table, override the getTableSchema() method:

public function getTableSchema()
{
  $table = parent::getTableSchema();
   
  $table->columns['m_sid']->isForeignKey = true;
  $table->foreignKeys['m_sid'] = array('Message', 'sid');

  return $table;  
}


Now the standard relation definitions work directly!

This is the relation in the Message model:
'clicks'=>array( self::HAS_MANY, 'Click', 'm_sid' ),

as well as this relation in the Click model:
'message'=>array( self::BELONGS_TO, 'Message', 'sid'),

2 comments:

  1. I was using MyISAM for the longest time (being that it is the default when creating tables in phpmyadmin). After using Yii for awhile I switched to InnoDB.

    Check out this comparison:
    http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/

    ReplyDelete
  2. "
    as well as this relation in the Click model:

    'message'=>array( self::BELONGS_TO, 'Message', 'sid'),
    "
    you need here to change "sid" to "m_sid" or the error will be thrown.

    try it.

    ReplyDelete