Skip to main content

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 :)



Comments

  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

Post a comment

Thanks for your valuable comments.

Popular posts from this blog

Odoo: Qweb Templates Cache Issue

Hi Everyone, Today I was working on a hotel booking template in Odoo and changes made in the Qweb template(.xml) were not reflecting on updating module with odoo service restart. Tried few times without any success :(. Finally, I decided to delete the old template from Odoo backend and then update module again and it worked like a charm!! :) Steps to delete template from backend: Settings --> User Interface --> Views --> Search by template name Delete the used template. Although it's a small post, but I'm sure it will meet the purpose and help someone in the future. Thanks!!! Enjoy Programming!! :) Reference Links: =================================== https://www.odoo.com/forum/help-1/question/odoo-cache-and-updating-qweb-templates-84492

Odoo12/13: Add an extra action in the tree view action button

This post will help you to add an action window in Odoo. Odoo13: <act_window id="action_make_bank_payment"              name="Do Bank Payment"              res_model="account.ob.payment"              binding_model="account.move"              view_mode="form"              target="new"              binding_views="list,form"              domain="[('type','=', 'in_invoice')]"              groups="account.group_account_user" /> Odoo12: <act_window id="action_make_bank_payment"              name="Do Bank Payment"              res_model="account.ob.payment"             view_mode="tree,form"              domain="[('type','=', 'in_invoice')]"              groups="account.group_account_user" /> Thanks!!! Enjoy Programming!! :) Reference: https://www.odoo.com/es_

Odoo/OpenERP: one2one relational field example

one2one relational field is deprecated in OpenERP version>5 but you can achieve the same using many2one relational field. You can achieve it in following two ways : 1) using many2one field in both the objects ( http://tutorialopenerp.wordpress.com/2014/04/23/one2one/ ) 2)  using inheritance by deligation You can easily find the first solution with little search over internet so let's start with 2nd solution. Scenario :  I want to create a one2one relation between two objects of openerp hr.employee and hr.employee.medical.details What I should do  i. Add _inherits section in hr_employee class ii. Add field medical_detail_id in hr_employee class class hr_employee(osv.osv):     _name = 'hr.employee'     _inherits = {' hr.employee.medical.details ': "medical_detail_id"}     _inherit = 'hr.employee'         _columns = {              'emp_code':fields.char('Employee Code', si