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.

Actinomycete

It is possible to change "primary key" of model and force CakePHP to use that field in join condition. But i'm not happy with such solution. – Actinomycete

Why are you not happy with that answer. Change your question to be more specific about what you do want and what behaviour you don't. Maybe your problems comes from a bad design descision where better sollutions for exists – Fredela

Jeroen, thanks for you interest. I'm not happy because it's something like i try to cheat CakePHP and i dislike such solutions. There are nothing about design, i just want to join two tables by custom fields in both tables. And i want to write that condition once in my model file to use everywhere in my app. May be you right, i will try to rewrite my question. – Actinomycete

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)

Philine

I'm perfectly happy for dave to get the accept (and bounty if that's possible) if the answer is updated appropriately. – Philine

Answer 2

Update:

Just specify that you don't want to use a foreignKey, then specify the conditions (all within the association:

'foreignKey' => false and 'conditions' => 'Comment.username = User.username'

PS - probably a good idea moving forward to try not to be rude to the people helping you.

---

This is very clearly defined in the CakePHP book: http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#hasmany

class User extends AppModel {
    public $hasMany = array(
        'Comment' => array(
            'className' => 'Comment',
            'foreignKey' => 'user_id', // <---- THIS HERE you can define the foreign_key
            'conditions' => array('Comment.status' => '1'),
            'order' => 'Comment.created DESC',
            'limit' => '5',
            'dependent' => true
        )
    );
}

What is a Foreign Key?:

In the context of relational databases, a foreign key is a field in one table that uniquely identifies a row of another table.

Adallard

Please quote that part where my question is answered. – Actinomycete

Do you really want me to go to the link for you, and copy/paste out the answer into stackoverflow? – Adallard

I just read that part of manual again and still can't find answer. So if you can point me directly i will be grateful. Also please look my answer above. – Actinomycete

OK, foreign key. I know that already. And now: how to define field name not in ANOTHER table, but in current table to which i want to join to? Because my question is actually about that part. And as i say earlier if i do that it changes only foreign field and take id from current table. But i need to use another field, not an id. – Actinomycete

I sounds like you just need to spend a few minutes clarifying your question. – Adallard

Dave, it sounds like you need to spend few minutes to read question again. I have two tables with field username in both. And need to join for that field in BOTH tables. Not only in foreign table. That's it. – Actinomycete

Sorry, you got a down vote because not even try to read my question carefully and continue arguing. I fount a solution but i don't like it. See my answer if you interested. – Actinomycete

I'm not interested in a work-around, as I believe my answer solves the problem within normal CakePHP options. – Adallard

So please try your solution with my schema and debug 2 and see the query. I believe you're wrong. Because i have huge experience with CakePHP and know about foreign key. – Actinomycete

I will simplify task: please see my update to my own answer. There are sample CakePHP app with my schema and your solution. And that solution doesn't work. So please take your downvote back because you're totally wrong but continue arguing without even try to understand my question. – Actinomycete

@Actinomycete Dave is right and I've checked your answer, your DB is simply awkward. Besides that even this awkward design can be used the way Dave describes with CakePHP why do you create such a DB design at all? – Brookes

@burzum, Dave isn't right. See my screenshot above there you can see actual query produced by CakePHP. See that it use values 1 and 2 in 'username' condition but in need to be 'firstusername' instead. Please treat that "awkward design" just like example. It isn't actual schema of my application. I have complicated DB schema. But still it's question not about DB optimization. – Actinomycete

Answer 3

Just a workaround that should work for your hasMany relationship when you want to retrieve a User and all his Comments

class User extends AppModel {
    public $hasMany = array(
        'Comment' => array(
            'finderQuery' => 'SELECT * FROM comments Comment 
                              LEFT JOIN users User 
                              ON Comment.username = User.username 
                              WHERE User.id = {__CakeID__}'
        )
    );
}

Hope it helps

Teddytedeschi

Thanks, it will work for totally esoteric join conditions, but in my case it's enough 'foreignKey' => false, 'conditions' => array() solution. – Actinomycete

Answer 4

I struggled with this exact problem for days and the solution that I found (not clearly addressed above) is: The hasMany must have 'foreignKey' => false while the belongsTo MUST ALSO have the 'foreignKey' => false and have the 'conditions' => 'Comment.username = User.username'. With the conditions as a string not assoc array. Like so:

// Profile
public $hasMany = array(
    'Comment' => array(
        'foreignKey' => false,
    )
);

// Comment
public $belongsTo = array(
    'Profile' => array(
        'foreignKey' => false,
        'conditions' => array('Profile.username = Comment.username')
    )
);

Miracle

Answer 5

public $hasMany = array(
    'Comment' => array(
        'className' => 'T1',
        'foreignKey' => 'username',
        'dependent' => true
    )
);

Note that dependent true would delete all the T1's when T2's were deleted from within cake.

Jaine

Speaking about Cake manual: in this case query would be something like ON (Comment.username` = User.id)` but i need username for both tables. – Actinomycete

It is like i need to join comments not by user id but by login: Select * from users as User LEFT JOIN comment as Comment ON (User.login = Comment.login) I hope it is more clear explanation. – Actinomycete