How to associate model in CakePHP by fields not named by convention?

Question

I have two tables with field username in both. How i can specify field name for both local and foreign table?

I want CakePHP will do something like

ON (`T1`.`username` = `T2`.`username`)`

in result. Without any changes tables will be joined with following condition:

ON (`T1`.`id` = `T2`.`t1_id`)`

Setting 'foreign_key' = 'username' property is not enough because it will produce query like this:

ON (`t1`.`id` = `t2`.`username`)`

I have two solutions. First one is use 'join' property and join table on the fly. In such case i can set both local and foreign field. But if i need to join more tables to that one, joined manually, i can't use contain anymore i need to write following joins manually even if that associations was set correctly. So i need to write long join definitions every time instead just use 'contain' => array('T1', 'T2', 'T3')

Second is to set 'primary_key' of table to corresponding field. It can be done in model file or in runtime. In my case it can not be done in model because that table also have "correct" association by its 'id' field. Setup it runtime is the case but i dislike it because it's not obvious and looks like a hack.

When i ask this question i thought i missing something obvious but now i understand that CakePHP just can't do that. So i started a bounty hoping that somebody share solution. If not i will try to read cake sources and redefine model some of method to add ability to define local field near the 'foreign_key' in association definition.

Answer 1

ForeignKey false

To have an association which does not use the primary key of the related model in join conditions - the standard way to do that would be to use 'foreignKey' => false.

I.e. whereas this association:

class Comment extends AppModel {
    public $belongsTo = array(
        'Profile' => array(
        )
    );
}

Will generate this sql:

SELECT ... LEFT JOIN profiles on ON (Profile.id = Comment.profile_id)

Specifying that a foreignKey isn't to be used like so:

class Comment extends AppModel {
    public $belongsTo = array(
        'Profile' => array(
            'foreignKey' => false
        )
    );
}

Will produce this (invalid) sql:

SELECT ... LEFT JOIN profiles on ON ()

From this point, the desired conditions can be specified using the conditions array key:

class Comment extends AppModel {
    public $belongsTo = array(
        'Profile' => array(
            'foreignKey' => false,
             'conditions' => array(
                 'Comment.username = Profile.username'
             ),
        )
    );
}

(Note that the conditions are defined as a string) resulting in:

 SELECT ... LEFT JOIN profiles on ON (Comment.username = Profile.username)