Naming convention and joins in CakePHP

Question

Just a few days ago I found out about this miracle called CakePHP so I am pretty green 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.

Answer 1

I'm not an expert myself, but following info on the CakePHP site will help you further: Multiple-relations-to-the-same-model

Answer 2

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)));
?>