Sunday, 26 August 2012

CakePHP Complex Find Conditions


Today we are going to discuss over complex find conditions using multiple 'AND' and 'OR' operators.  I was to write a CakePHP find condition for following query :

Select * from Quarter where Quarter.status = 1 AND ((Quarter.start_date >= '2012-04-01' AND Quarter.end_date <= '2012-06-30') OR (Quarter.start_date >= '2012-07-01' AND Quarter.end_date <= '2012-09-30')) ;

It's not that typical but typical enough to understand the concept.

First I wrote this query :

$conditions = array(
                'conditions' => array(
                                'AND' => array(
                                                'Quarter.status' => '1',
                                                array(
                                                                'OR' => array(
                                                                                'AND' => array(
                                                                                                'Quarter.start_date >=' => '2012-04-01',
                                                                                                'Quarter.end_date <=' => '2012-06-30'
                                                                                ),
                                                                                'AND' => array(
                                                                                                'Quarter.start_date >=' => '2012-10-01',
                                                                                                'Quarter.end_date <=' => '2012-12-31'
                                                                                )
                                                                )                                                             
                                                )
                                )
                )
);

But it produced wrong result. Why? One minute!!

To find where things went wrong. I used 

print "<pre>";
print_r($conditions);

It produced following result :

Array
(
    [conditions] => Array
        (
            [AND] => Array
                (
                    [Quarter.status] => 1
                    [0] => Array
                        (
                            [OR] => Array
                                (
                                    [AND] => Array
                                        (
                                            [Quarter.start_date >=] => 2012-10-01
                                            [Quarter.end_date <=] => 2012-12-31
                                        )
                                )
                        )
                )
        )
)

Yes, got my answer. Reason is array is having same keys 'AND' so it would overwrite first 'AND' means :

'AND' => array(
                                 'Quarter.start_date >=' => '2012-10-01',
                                 'Quarter.end_date <=' => '2012-12-31'
                 )

would overwrite :

'AND' => array(
                                   'Quarter.start_date >=' => '2012-04-01',
                                   'Quarter.end_date <=' => '2012-06-30'
                 )

So the solution is to keep both the entries in different arrays.

$conditions = array(
      'conditions' => array(
                      'AND' => array(
                                      'Quarter.status' => '1',
                                       array(
                                                     'OR' => array(
                                                                array(
                                                                                 'AND' => array(
                                                                                            'Quarter.start_date >=' => '2012-04-01',
                                                                                            'Quarter.end_date <=' => '2012-06-30'
                                                                                  )
                                                                  ),
                                                                  array(
                                                                                   'AND' => array(
                                                                                                 'Quarter.start_date >=' => '2012-10-01',
                                                                                                 'Quarter.end_date <=' => '2012-12-31'
                                                                                   )
                                                                    )
                                                     )                                                             
                                     )
                   )
        )
);

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

That's it. If you have still any doubt please comment against the post.
Thanks!!!!!!!!!!!!!! Enjoy Programming :)



1 comment:

  1. This certainly is a helpful example. I would like to give a more complex query I am trying to write with cakephp's find(all) function and if possible the paginate function too. it involves 2 models.


    SELECT `users`.`phone`,`calls`.`id`, `calls`.`date`, `calls`.`from`, `calls`.`to`
    FROM `users`, `calls`
    WHERE (`users`.`phone`!='+XXX' and `users`.`phone`!='+XXX'
    AND (`users`.`phone`=`calls`.`from` OR `users`.`phone`=`calls`.`to`)
    AND (`calls`.`date` BETWEEN '".$start_date."' AND '".$end_date."')
    ORDER BY `calls`.`id` DESC
    LIMIT 200;

    Is this possible?

    Thanks.

    ReplyDelete

Thanks for your valuable comments.

Odoo 10: Close wizard and open standard form

Hi, Today we are going to learn how to open standard form after saving data in wizard. Let's say I have created an wizard to fill bas...