Hi,
I need to do the following query using the CakePHP find
method:
SELECT * FROM `messages` INNER JOIN users ON messages.from = users.id WHERE messages.to = 4 ORDER BY messages.datetime DESC
Basically I have:
messages
table with a Message
modelusers
table with User
modeland want to retrieve information from both tables in one query. The users.id
field is the same as the messages.from
field, so that's what the join is on.
I am doing it in my MessagesController
so it would need to be something like:
$this->Message->find();
Thanks
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.
It's worth pointing out that this advice is for CakePHP 2.x, not 3.x.
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' ));
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.
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' ));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With