CODEWITHSUNDEEP
sqlcakephpcakephp-2.0cakephp-2.1
hellosheikh
hellosheikh

Reputation: 3015

CakePHP use JOIN in find method

i am newbie in cakephp and trying to implement this query

 SELECT DISTINCT textmessage.mobileNo FROM textmessage 
 JOIN contacts
  ON textmessage.User_id=contacts.User_id AND textmessage.mobileNo = Contacts.mobileNo

i am expecting only one result here .. want to implement this query in textMessage Controller ... i have never use join query before in CAKEPHP... i have actually a mobileNo field in both the tables and i want to retrieve the mobileNo if the mobileNo of textmessage table is also in Contacts table

here is what i have modified your query according to my requirments ..

  $this->bindModel(array('belongsTo' => array('Contact' => array('className' => 'Contact',
        'foreignKey' => false,
        'conditions' => array('Message.user_id = Contact.user_id','Message.mobileNo =  Contact.mobileNo')))), true);

  return  $message_details = $this->find('all', array('conditions' => array(),
        'fields' => array('DISTINCT mobileNo')));

Upvotes: 0

Views: 490

Answers (1)

Arun Jain
Arun Jain

Reputation: 5464

Put the following code in your controller's code:

If you need only single record:

function test1()
{
$this->TextMessage->bindModel(array('belongsTo' => array('Contact' => array('className' => 'Contact',
                                        'foreignKey' => false,
                                        'conditions' => array('TextMessage.user_id = Contact.user_id')))), false);
$message_details = $this->TextMessage->find('all', array('conditions' => array(),
                                             'fields' => array('DISTINCT mobileNo')));

}

If you have multiple text messages corresponding to each contact then try the following:

function test2()
{
$this->Contact->bindModel(array('hasMany' => array('TextMessage' => array('className' => 'TextMessage',
                                      'foreignKey' => false,
                                      'conditions' => array('TextMessage.user_id = Contact.user_id'),
                                      'fields' => array('DISTINCT mobileNo')))
                    ), false);
$message_details = $this->Contact->find('all', array('conditions' => array()));                   
}

You can also write the association in your model. I gave you an example to dynamically join any table on the fly.

According to your edited question, if you don't need two arrays. Use query() function.

Hope this will fulfill your requirement.

Upvotes: 1

Related Questions