[SOLVED] CActiveRelation HAS_MANY based on secondary key?

Disclaimer: This relational setup isn’t the best, but I’m trying to make do with an existing system which I cannot change … so how to make it work??

I’m trying to set up an active relation between two tables based off a secondary key ( sid ) rather than the primary key.

I feel like there aught to be a simple way to do this, but the only way I’ve found is convoluted.

This is how I’ve finally got it working:

Relation:




   'alt_clicks'=>array( self::HAS_MANY, 'Click', 'm_sid' );



Alternate Get function:




   public function getClicks()

   {

      $this->primaryKey = $this->sid;

      $x = $this->alt_clicks;

      $this->primaryKey = $this->oldPrimaryKey;

      return $x;

   }



It would be SO much simpler to be able to pass an alternate primary key to the CActiveRelation for it to use on the join. Is there a way to do this that I’m missing??

The big drawback with this solution is that I can’t use eager loading.

Altering the ‘on’ condition doesn’t seem to do the trick - I end up with it trying to compare both keys.

Any advice would be greatly appreciated, thanks!

Your question inspired me to dive into the code of AR a little - which it has plenty ;). From what i see, the JOIN condition is built in CActiveFinder.php:995 (joinOneMany()). It uses this code, to find the name of the primary key column:


            if(isset($fke->_table->foreignKeys[$fk]))

                $pk=$fke->_table->foreignKeys[$fk][1];

            else  // FK constraints undefined

            {

                if(is_array($pke->_table->primaryKey)) // composite PK

                    $pk=$pke->_table->primaryKey[$i];

                else

                    $pk=$pke->_table->primaryKey;

            }



So if i get this right, it should work, if you have defined the right foreign key constraints in your DB. Did you?

It’s a MyISAM table w/o built-in foreign key capabilities :(

So, I either have to come up with a programmatic way to handle it or go without.

You could try something: ActiveFinder uses $model->getTableSchema() to retrieve all meta information for a table. It returns a CDbTableSchema object with all meta informations read from DB. You could try to override that method, fetch the parent::getTableSchema() and manually add the foreign keys to its foreignKey property. You’ll have to fiddle around with how to do this right. You could use another MySQL with InnoDB to inspect how that property must look.

Good luck ;).

Thanks, I’ll try that!

Please post your results, if you get it working (and how you’ve done it).

GOT IT! Works like a charm :D

In the Model class, I overrode the getTableSchema and manually defined the foreign keys like so:




public function getTableSchema()

{ 

  $table = parent::getTableSchema();

   

  $table->columns['sid']->isForeignKey = true;

  $table->foreignKeys['sid'] = array('Click', 'm_sid');


  return $table;

}



And in the corresponding table:




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!




   'clicks'=>array( self::HAS_MANY, 'Click', 'm_sid' ),



as well as




   'message'=>array( self::BELONGS_TO, 'Message', 'sid'),



I haven’t tested it with eager loading yet, but so far so good. – Works there beautifully as well.

Note: Only need the second foreign key definition that I listed - the one in the Click model. The other isn’t used.

Coool! :)

Hello!

Fascinating, i am looking just today for such a solution. But it isn’t working for me ;(

Before adjusting getTableSchema i got the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘track.f_TRACK_ID’ in ‘where clause’

Afterwards the exception changes to:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘track.f_TRACK_ID’ in ‘field list’

I am also using MySQL MyISAM tables.

I have a model Playlist (id is PK) and a model Track (id is PK).

Track.id (FK) should relate to Playlist.TRACK_ID (as PK).

Regards

.nowrap

Can you post your modified getTableSchema? And which model are you adding it to?

I notice that you say the Playlist foreign key is TRACK_ID, but the error is calling f_TRACK_ID

The override should be on the Playlist model - like so:





public function getTableSchema()

{ 

  $table = parent::getTableSchema();

   

  $table->columns['TRACK_ID']->isForeignKey = true;

  $table->foreignKeys['TRACK_ID'] = array('Track', 'id');


  return $table;

}



Then, how are you defining the relationship?

Hi Dana,

i put the getTableSchema in the Track model. First error, i guess ;)

I changed it right to your advise and added this relation to the Playlist model:

“track” => array(self::HAS_ONE, ‘Track’, ‘id’),

Now i am not getting any errors anymore. But the values are empty:

$playlist->track->artist;

Regards

.nowrap

Change your relation to:




   'track'=>array( self::BELONGS_TO, 'Track', 'TRACK_ID'),



It’s trying to find the wrong id for reference =)

(Oops - should be BELONGS_TO not HAS_ONE)

That’s working … weird stuff indeed ;)

Just a parameter for the PK field name would be my favorite solution …

Thanx alot Dana

.nowrap

Yeah no kidding – that would be much simpler!!

You’re welcome! =)

Dana, you are a saint! Thank you for figuring this out!