Relations and find_in_set

Yii 1.1 General Discussion
1

Hi there,

first of all, Yii is a great framework and I really enjoy every day working with it. But now I am on a point where I am stucked. I have two tables with the following relations

Table jobs

Field id

Field location_id

Table location

Field id

Field address

Sample data - jobs

[id] [location_id]

[1] [2]

[2] [1,2,3]

Sample data - location

[id] [address]

[1] [address1]

[2] [address2]

[3] [address3]

I am able to join both tables (in MySQL) by doing so:




SELECT * FROM jobs j

LEFT JOIN location l ON FIND_IN_SET(l.id, j.location_id)

Does anybody have a clue on how to implement this in a Yii-Model? I tried this:




public function relations()

{

  return array(

    'location' => array(self::HAS_MANY, 'Jobs', 'location_id',

      'on' => 'FIND_IN_SET(location.id, jobs.location_id)'

    )

  );

}

But it does not work since Yii always uses the foreign key in the ON clause, which is in this case wrong.

Any suggestions?

Thanks a lot in advance.

Cheers

WP

2

I got the same problem. hopefully there is someone who can answer this. :)

3

Hi,

we can add condition to the job model relations as below,

public function actionCheckJobRelations()

{

if((Job::model()->findAll(array('condition'=>'id=:jid','params'=>array('jid'=>$_REQUEST['Id']))


{


}


else


{





}

}

4

Anyone who has solved this?

5

Yeah, the solution is very simple, normalize your database.

6

‘a’=>array(self::HAS_MANY,‘ModelName’,’’,‘foreignKey’=>array(),‘on’=>‘find_in_set(a.id,t.column)’),