蛋糕php自定义查询

Question

I have use custom query in cakephp but I dont understand how to run custom join query. I am using this code

$arrayTemp1 = $this->User->query('SELECT DISTINCT
                u.id,u.hunting_association FROM ht_users as u LEFT JOIN 
                `ht_user_animal_prices` as uap ON uap.user_id=u.id  WHERE
                uap.animal_type_id='.$this->request->data['User']['animal'].' ');

User is the model for ht_users and UserAnimalPrice is the model for ht_user_animal_prices. How to combine the query?

Please help.

Answer 1

If you want to use custom queries and you want the data of UserAnimalPrice model, you just have to put the fields in the query. Something like:

$arrayTemp1 = $this->User->query('SELECT DISTINCT u.id,u.hunting_association, uap.* FROM ht_users as u LEFT JOIN  ht_user_animal_prices as uap ON uap.user_id=u.id WHERE uap.animal_type_id='.$this->request->data['User']['animal'].' ');

If you prefer not to use custom queries:

$fields = array('User.id','User.hunting_association','UserAnimalPrice.*');
$join = array(
   array(
      'table' => 'ht_user_animal_prices',
      'alias' => 'UserAnimalPrice',
      'type'  => 'LEFT',
      'conditions' => array('UserAnimalPrice.user_id = User.id')
   )
);
$conditions = array('UserAnimalPrice.animal_type_id' => $this->request->data['User']['animal']);
$group = array('User.id');

arrayTemp1=arrayTemp1->find('all',array('fields'=>$fields,'joins'=>$join,'conditions'=>$conditions,'group'=>$group));