CakePHP complex find conditions

Question

For example I have a Store model with fields fromH and toH, I want to retrieve stores from database that are open in given hour (@t).

SQL query would look like this

select *
from store 
where 
( fromH < toH and @t between fromH and toH ) or
( fromH > toH and 
     (@t between fromH and 24 OR
     (@t between 1 and toH )
)

How do I implement this query in cakephp, how would the conditions array look? I want to do it cake style.

Answer 1

Answer given by @Arun Jain is very close to the solution but as the rules for the OR states must each be their own array, because their key values are identical. If you

print_r($conditions);

you'll find some of your rules are missing.

I think following solution will work for you.

$conditions = array(
    'conditions' => array(
        'OR' => array(
            array(
                'AND' => array(
                    'fromH <' => 'toH',
                    @t.' BETWEEN ? AND ?' => array('fromH','toH')
                 )
            ),
            array(
                'AND' => array(
                    'fromH >' => 'toH',
                    'OR' => array(
                        @t.' BETWEEN ? AND ?' => array('fromH','24'),
                        @t.' BETWEEN ? AND ?' => array('1','toH')
                    )
                )
            )
        )
    )
);

$this->Strore->find('all',$conditions);

Answer 2

You can try it with the following CakePHP equivalent select query:

<?php $this->Store->find('all', array(
    'conditions' => array(
        'OR' => array(
            'AND' => array(
                'fromH < ' => 'toH',
                $t.' BETWEEN ? AND ?' => array('fromH', 'toH')
            )
        ),
    'AND' => array(
            'fromH > ' => 'toH',
            'OR' => array(
                $t.' BETWEEN ? AND ?' => array('fromH', '24'),
                $t.' BETWEEN ? AND ?' => array('1', 'toH')
            )
        ),
    )
));