CakePHP query - AND / OR complex conditions

Question

I am trying to understand CakePHP’s difficult search conditions and read documents , but I am struggling with this single request.

SELECT field1, field2 WHERE id = 123456 AND ((holding_date = Last_day(holding_date) AND Month(holding_date) IN(3, 6, 9, 12)) OR (holding_date = '2013-09-15')) 

To create the conditions described above, what does my conditions array look like?

Answer 1

CakePHP Terms and Sql Expressions

Although the conditions in the question are not so complicated, they relate to several points, which mean that they are difficult to determine correctly. Some of the things you need to know when determining the conditions of a trick are:

• Conditions are defined as an array of key => value pairs, since the same key cannot be defined twice at the same level
• an array element that has a numeric key is interpreted as an sql expression
• The default connection mode is “AND” - there is no need to specify "AND" => ... in conditions
• OR conditions must have more than one element. There is no mistake if it has only one, but otherwise: OR what?

Given the above comments, the conditions in the question can be expressed as:

 $foo->find('all', array( 'fields' => array( 'field1', 'field2' ), 'conditions' => array( 'id' => 123456, 'OR' => array( array( 'holding_date = LAST_DAY(holding_date)', 'MONTH(holding_date)' => array(3,6,9,12) ), 'holding_date' => '2013-09-15' ) ) )); 

Result:

 WHERE `id` = 123456 AND ( ( (holding_date = LAST_DAY(holding_date)) AND (MONTH(holding_date) IN (3, 6, 9, 12))) ) OR (`holding_date` = '2013-09-15') ) 

Note: spaces are pretty important =) I incorrectly ask the question initially solely because of an inconsistent space in the sql question.

Answer 2

OK I solved this:

 $findParams['conditions'] = array( 'Account.client_id' => '12345', 'AND' => array( 'OR' => array( 'Holding.holding_date' => '2013-09-15', 'AND' => array( 'Holding.holding_date = LAST_DAY(Holding.holding_date)', 'MONTH(Holding.holding_date)' => array(3,6,9,12) ) ) ) ); 

Answer 3

Try the following:

 $params['conditions'] = array( '`id`' => 123456, 'AND' => array( '`holding_date`' => 'LAST_DAY(`holding_date`)', 'AND' => array( 'MONTH(holding_date)' => array(3, 6, 9, 12), 'OR' => array(`holding_date` => '2013-09-15') ) ) );