CakePHP query - complex AND/OR conditions

Question

I'm trying to get my head around the complex find conditions of CakePHP and have read the docs but am struggling with this one query.

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 produce the above conditions what would my conditions array look like?

Solution 1

CakePHP conditions and sql expressions

While the conditions in the question are not that complex, they touch on a few points which mean they can be tricky to define correctly. Some of the things to know when defining cakephp conditions:

• Conditions are defined as an array of key => value pairs, as such the same key cannot be defined twice on the same level
• an array element which has a numeric key is interpreted as an sql expression
• The default join mode is "AND" - it's not necessary to specify "AND" => ... in conditions
• An OR conditions must have more than one elements. There's no error if it has only one but otherwise: OR what?

Bearing in mind the above notes, 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'
        )
    )
));

Which results in:

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: whitespace is quite important =) I misread the question originally solely because of the inconsistent whitespace in the question's sql.

Solution 2

OK I have solved it:

$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)
                        )
                    )
                )
            );

Solution 3

Try this:

$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')
        )
    )
);

Comments

[harryg]

I can tell without trying that this isn't right; you have grouped one of the 1st date conditions in the same array as the OR condition. From what I can tell this will always require the first condition to be satisfied when this isn't what I want.

[harryg]

I english my condition is "record must fall on the last day of the quarter [the first 2 conditions] OR be on a date that I specify [the last condition]"

[Reactgular]

+1 good answer, but I would advise placing OR rules in their own arrays. PHP will not warn if keys are duplicated and if you have two rules on hold_date only the last will be used.

[AD7six]

@MathewFoscarini unnecessary nesting makes things hard to read though - so it's IMO only a good idea to do that when it's required, or conditions are being built itteratively (The same can be said for any array key, OR isn't a special case). If holding_date were in two OR comparisons it'd be more appropriate to use IN (i.e. 'holding_date' => array('first', 'second')).

[harryg]

Yes, good answer. While I did get there on my own your exclusion of the unnecessary "AND"s makes it more concise. Apologies about the whitespace - wasn't quite sure how to apply it to SQL