Relation with MD5 value in field

Yii 1.1 General Discussion
1

Hi all,

I’ve a question on relations, this is the DB structure

TABLE_A (id, code1, code2, code3, …)

TABLE_B (code1, code2, md5(code3), …)

In the old website to retrive joined data between table a and b the sql statement is




SELECT * FROM 

TABLE_A INNER JOIN TABLE_B ON MD5(TABLE_A.code3) = TABLE_B.code3 

        AND TABLE_A.code2 = TABLE_B.code2

        AND TABLE_A.code1 = TABLE_B.code1

I’m trying to create a relation in modelA but is not working…

NOT WORKING:





		return array(

                    'tableB'=>array(self::HAS_ONE, 'ModelB', 'code1, code2, md5(code3)'),

		);


OR


		return array(

                    'tableB'=>array(self::HAS_ONE, 'ModelB', 'code1, code2',

                         'condition'=>'code3 = :code3',

                         'params'=>array(':code3'=>md5($this->code3)),

		);

Any idea?

2

try to use the Additional options of http://www.yiiframework.com/doc/api/CActiveRecord-detail relations

by example option "where"

3

Thanks for reply, in the example above I’ve already used the where option (whith ‘condition’ and ‘params’ ) and also the ‘ON’ option but is not working yet…

4

No other suggestions?!?!?! :(

5

enable CWebLogRoute in the config and see the generated query, (or in the runtime/application.log)

maybe after this, you can see what the error

[font="arial, sans-serif"][size="6"][size=6]

[/size][/size][/font]

6

with this relation : ‘tableb’=>array(self::HAS_ONE, ‘TableB’, ‘code1, code2, md5(code3)’) In error log I have:

[exception.CDbException] exception ‘CDbException’ with message ‘CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘tableb.md5(code3)’ in ‘where clause’’

Maybe is not possible to make a relation with a foreign key that is a combination of the original key…