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.

Reference Links

http://ask.cakephp.org/questions/view/or_conditions_array_in_find_cakephp_sql_query



Thanks!!!!!!!!!!!!!! Enjoy Programming :)



Labels:
Location: Bangalore, Karnataka, India

Comments

  1. Anonymous21 June 2013 at 02:35

    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/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 = {             ...
13 comments
Read more

How to draw Dynamic Line or Timeseries Chart in Java using jfreechart library?

Today we are going to write a code to draw a dynamic timeseries-cum-line chart in java.   The only difference between simple and dynamic chart is that a dynamic event is used to create a new series and update the graph. In out example we are using timer which automatically calls a funtion after every 1/4 th second and graph is updated with random data. Let's try with the code : Note : I had tried my best to provide complete documentation along with code. If at any time anyone have any doubt or question please post in comments section. DynamicLineAndTimeSeriesChart.java import java.awt.BorderLayout; import java.awt.Color; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import javax.swing.Timer; import javax.swing.JPanel; import org.jfree.chart.ChartFactory; import org.jfree.chart.ChartPanel; import org.jfree.chart.JFreeChart; import org.jfree.chart.axis.ValueAxis; import org.jfree.chart.plot.XYPlot; import...
37 comments
Read more

pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]

Recently, I faced this error in our Docker-Container environment. All the necessary packages were already installed but still, I was facing this clueless error. I search a bit and after an hour and so I found the exact reason and solution for this error. To know more about this error in detail. Please follow this Github thread. https://github.com/mkleehammer/pyodbc/issues/610 https://github.com/mkleehammer/pyodbc/issues/610#issuecomment-587523802 Solution: It's because the   server's certificate has too weak a key. In case you are using Linux env directly/not the Docker one.  Just edited /etc/ssl/openssl.cnf and change these 2 lines. MinProtocol = TLSv1.0 CipherString = DEFAULT@SECLEVEL=1 In case you are also using a container, please add these three lines to your Docker file. RUN chmod +rwx /etc/ssl/openssl.cnf RUN sed -i ' s/TLSv1.2/TLSv1/g ' /etc/ssl/openssl.cnf RUN sed -i ' s/SECLEVEL=2/SECLEVEL=1/g ' /etc/ssl/openssl.cnf Thanks!! Enjoy Programming! Refer...
1 comment
Read more