Franchy
Franchy

Reputation: 152

Write sql query in Cakephp find method

I have the following SQL query:

SELECT * FROM pm_management.system_tasks 
  WHERE eq_model = 'D9 L' and pm_type = 'H' and 
     ((equipment = '5164' and main_systems_id = 1) or 
      (equipment = 'TODOS' and main_systems_id <> 1));

I want write that query in a Cakephp find() method. I tried the following but the result was not the expected.

$sys_content = $this->SystemTask->find('all', array(
     'conditions' => array('AND' => array(
           'eq_model' => 'D9 L',
           'pm_type' => 'H',
              'OR' => array(
                  'AND' => array(
                     'equipment' => '5164',
                     'main_systems_id' => '1'
                   ),
                  'AND' => array(
                     'equipment' => 'TODOS',
                     'main_systems_id' => '<> 1'
                   )
                )
           ))
        ));

Thanks in advance for your help.

Upvotes: 1

Views: 384

Answers (2)

skiedr
skiedr

Reputation: 101

Use next condition. When you write conditions be aware that you cant have same key twice inside single hash(=array). So Second AND was overwrite your condition. As AND is default you can remove it like i did in code below,

$sys_content = $this->SystemTask->find('all', array(
 'conditions' => array(
       'eq_model' => 'D9 L',
       'pm_type' => 'H',
          'OR' => array(
              array(
                 'equipment' => '5164',
                 'main_systems_id' => '1'
               ),
              array(
                 'equipment' => 'TODOS',
                 'main_systems_id' => '<> 1'
               )
            )
       )
    ));

or wrap block with ['AND' => ... ] with additional array().

$sys_content = $this->SystemTask->find('all', array(
 'conditions' => array('AND' => array(
       'eq_model' => 'D9 L',
       'pm_type' => 'H',
          'OR' => array(
              array(
                'AND' => array(
                   'equipment' => '5164',
                   'main_systems_id' => '1'
                )),
              array(
                'AND' => array(
                   'equipment' => 'TODOS',
                   'main_systems_id' => '<> 1'
               ))
            )
       ))
    ));

Upvotes: 1

Nizam
Nizam

Reputation: 505

actually you can use $this->Model->query($yourQuery) for that purpose. Even if it is not recommended. But its the best without headache to convert it to CakePHP find.

Upvotes: 0

Related Questions