Relations On Non-Key Fields

Hi,

I am having trouble creating relations on non-key fields. My issue is that all records from the ‘HAS_MANY’ table are being returned, rather than just the matching values. I’ve tried several variations but having no success.

My code is:





MySQL Table: customer

- id (PK)

- name

- reference_key


MySQL Table: visit

- id

- system_id

- reference_key


Model: Customer relations function()

	'visits'=>array(self::HAS_MANY, 'Visit', '', 'on'=>'reference_key=visits.reference_key'),


Model: Visit relations function

	'customer'=>array(self::BELONGS_TO, 'Customer', 'reference_key'),


$arr = $data->visits(); // this returns all records from visit table instead of records on the reference key. 

foreach($arr as $visit){

	echo 'visit key: '.$visit->reference_key.'; ';

}



Without the ‘alias’ I am receiving ambiguous column errors from MySQL. Any assistance is appreciated. Thank you.

as far as I know, ralational model must have foreign key relation(no need to constraint in db). so maybe this kind of tables not able to use the relational model approach.

I met the same kind of problem, and figured it out using


 Yii::app()->db->createCommand($sql)->query()/execute()

hope u luck, if u figure out a ralational approach, please let me know.

This post has really good explanation that might help your issue. I was not able to solve my issue with it.

It’s totally possible. Don’t mess around with the ‘on’ param, just define the FK as an array.

Currently, as of 1.1.14, it is not possible to specify a custom PK for MANY_MANY relations but I’ve submitted a patch for that. It will hopefully be available in 1.1.15.

Thanks. I’ve setup as FK arrays:




    Model: Customer relations function()

        'visits'=>array(self::HAS_MANY, 'Visit', array('visitor_key','visitor_key'), ),


    Model: Visit relations function

        'customer'=>array(self::BELONGS_TO, 'Customer', array('visitor_key','visitor_key'), ), 



Now I get zero results. In my test data I should be getting 2 records. 2 questions: 1) is there a way to see the generated SQL, and 2) do you have any other suggestions?

Thanks!

I’ve opted to leave the relations behind and instead simply use a method to retrieve the desired results.


return Visit::model()->findAllByAttributes( array('visitor_key'=>$this->visitor_key) );

Thank you for the assistance. It was helpful information that’s good to have.

You must define FK as an associative array, PK=>FK not PK,FK (i don’t know if the order is right).