CODEWITHSUNDEEP
phpsqlcakephp
harryg
harryg

Reputation: 24107

CakePHP query - complex AND/OR conditions

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?

Upvotes: 9

Views: 39594

Answers (3)

AD7six
AD7six

Reputation: 66378

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.

Upvotes: 18

harryg
harryg

Reputation: 24107

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

Upvotes: 3

lukasz
lukasz

Reputation: 339

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

Upvotes: 1

Related Questions