Naming convention and joins in CakePHP
Just a few days ago I found out about this miracle called CakePHP so I am pretty gree开发者_StackOverflow中文版n to it. I need to build a mail application, so I have followed the convention and created:
Database description:
Table of users <user_id (primary key), fname, lname>.
Table of mails <mail_id(primary key), from (foreign key to user_id), to (foreign key to user_id), content, opened>.
My questions:
1) According to the convention, a foreign key should be called related table+'_id'. How should I call the columns if there are two foreign keys that relate to the same table. Like from and to in the mails table.
2) I would like to do an inner JOIN the between the two tables. Something like:
SELECT user_id, mail_id
FROM users
INNER JOIN mails
ON users.user_id =mails.to AND mails.opened=false.
But I have no clue how to do it.
When you need to do two relations to the same table, you will need to override the default convention. In your example, I would make 2 foreign keys. One named sender_id and one named recipient_id. Then you would join them in the Model like so:
<?php
class Mail extends AppModel {
//The Associations below have been created with all possible keys, those that are not needed can be removed
var $belongsTo = array(
'UserSender' => array(
'className' => 'User',
'foreignKey' => 'sender_id',
'conditions' => '',
'fields' => '',
'order' => ''
),
'UserRecipient' => array(
'className' => 'User',
'foreignKey' => 'recipient_id',
'conditions' => '',
'fields' => '',
'order' => ''
),
);
}
?>
Then to do your conditions, you would reference them like so:
<?php
$this->Mail->find(array('conditions'=>array('Mail.opened'=>false)));
?>
...and to filter on the sender and receiver, your conditions would look like:
<?php
$this->Mail->find(array('conditions'=>array('UserSender.some_field'=>$someValue,
'UserRecipient.some_field'=>$someValue)));
?>
I'm not an expert myself, but following info on the CakePHP site will help you further: Multiple-relations-to-the-same-model
精彩评论