Re: convention clarifications, and joins with cakephp

On Mon, Dec 28, 2009 at 10:20 AM, Jaimon <kal.lizkor@gmail.com> wrote:
> (..)
> 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.

Did you already know something about models and associations
in Cake?

You can have multiple associations to the same model normally.
In your Mail model you'll have something like this:

<?php
class Mail extends AppModel {
var $name = 'Mail';
var $useTable = 'table_of_mails';
var $primaryKey = 'mail_id';
var $belongsTo = array(
'From' => array(
'className' => 'User',
'foreignKey' => 'from'
),
'To' => array(
'className' => 'User',
'foreignKey' => 'to'
)
);
}

By the way, the Mail/User association is the example listed at cookbook.

http://book.cakephp.org/view/851/Multiple-relations-to-the-same-model

> 2) i would like to do an inner JOIN the between the two table.
> 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.

A plain query find can get what you want.

// eg., in a MailsController

$results = $this->Mail->find('all', array(
'conditions'=> array('Mail.opened'=> false)
, 'fields'=> array('User.user_id', 'Mail.mail_id')
));

As your User and Mail models are properly associated,
you don't have to complain about the join. The find
method abstracts it for you. Your $results will be likely

array(
[0] => array(
[User] => array(
[user_id] = 321321
)
[Mail] => array(
[mail_id] = 987989
)
)
[1] ...
)

Best regards.

--
MARCELO F ANDRADE
Belem, Amazonia, Brazil

"I took the red pill"

Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions.

You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en