Relations and find_in_set

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(, 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(, 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.



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


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

public function actionCheckJobRelations()









Anyone who has solved this?

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