There are two main ways that you can do this. One of them
is the standard CakePHP way, and the other is using a custom join.
The CakePHP Way
You would create a relationship with your User model and
Messages Model, and use the containable behavior:
class
User extends AppModel {
public $actsAs = array('Containable');
public $hasMany = array('Message');
}
class
Message extends AppModel {
public $actsAs = array('Containable');
public $belongsTo = array('User');
}
You need to change the messages.from column to be messages.user_id so
that cake can automagically associate the records for you.
Then you can do this from the messages controller:
$this->Message->find('all',
array(
'contain' => array('User')
'conditions' => array(
'Message.to' => 4
),
'order' => 'Message.datetime DESC'
));
The (other)
CakePHP way
I recommend using the
first method, because it will save you a lot of time and work. The first method
also does the groundwork of setting up a relationship which can be used for any
number of other find calls and conditions besides the one you need now. However,
cakePHP does support a syntax for defining your own joins. It would be done
like this, from the MessagesController:
$this->Message->find('all',
array(
'joins' => array(
array(
'table' => 'users',
'alias' => 'UserJoin',
'type' => 'INNER',
'conditions' => array(
'UserJoin.id = Message.from'
)
)
),
'conditions' => array(
'Message.to' => 4
),
'fields' => array('UserJoin.*',
'Message.*'),
'order' => 'Message.datetime DESC'
));
Note, I've left the field
name messages.from the same as your current table in this example.
Using two
relationships to the same model
Here is how you can do the
first example using two relationships to the same model:
class
User extends AppModel {
public $actsAs = array('Containable');
public $hasMany = array(
'MessagesSent' => array(
'className' => 'Message',
'foreignKey' => 'from'
)
);
public $belongsTo = array(
'MessagesReceived' => array(
'className' => 'Message',
'foreignKey' => 'to'
)
);
}
class
Message extends AppModel {
public $actsAs = array('Containable');
public $belongsTo = array(
'UserFrom' => array(
'className' => 'User',
'foreignKey' => 'from'
)
);
public $hasMany = array(
'UserTo' => array(
'className' => 'User',
'foreignKey' => 'to'
)
);
}
Now you can do your find
call like this:
$this->Message->find('all',
array(
'contain' => array('UserFrom')
'conditions' => array(
'Message.to' => 4
),
'order' => 'Message.datetime DESC'
));