Relations On Non-Key Fields

Yii 1.1 General Discussion
1

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.

2

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.

3

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

4

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.

5

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!

6

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.

7

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