CODEWITHSUNDEEP
cakephp
Nehal
Nehal

Reputation: 1022

Cakephp issue with "OR" and "AND" array with find function

As Cakephp's manual suggest, I have a column on which I want to put "OR" condition for matching multiple values and AND condition with other columns.

SQL :

SELECT * FROM site_logs 
WHERE action LIKE "Facebook%" OR action LIKE "Twitter%" AND 
created >= "2013-06-29" 
GROUP BY "ip address"

CakePHP :

$conditions = array(
        'OR' => array(
            'SiteLogs.action like' => 'Facebook%'
            'SiteLogs.action like' => 'Twitter%'
        ),
        'SiteLogs.created >=' => '2013-06-29'
    );
 $this->SiteLogs->find('all',
    'conditions' => $conditions,
    'group'     => 'ip address'
 )

Generated SQL have records only for "twitter" data, because "OR" array have all the elements with same key which will overwrite each other.

GENERATED SQL;

SELECT `site_logs`.* FROM `site_logs` 
WHERE `site_logs`.`action` LIKE "Twitter%" AND  
    site_logs`.`created` >= "2013-06-29" 
GROUP BY `site_logs`.`ip address`

What should I do to get all the values from "OR" array? Am I missing something?

Upvotes: 1

Views: 109

Answers (1)

Steven Moseley
Steven Moseley

Reputation: 16325

What you're looking for is this. Enclose the similar OR clauses each into their own arrays to prevent overlap of keys.

$conditions = array(
    'OR' => array(
        array('SiteLogs.action like' => 'Facebook%'),
        array('SiteLogs.action like' => 'Twitter%')
    ),
    'SiteLogs.created >=' => '2013-06-29'
);
$this->SiteLogs->find('all',
    'conditions' => $conditions,
    'group'     => 'ip address'
);

This should produce SQL like this. There's an extraneous set of parentheses around each of the LIKE conditions, but this is unavoidable with Cake, and it won't affect the execution of the query at all.

SELECT * 
FROM site_logs 
WHERE 
    ((action LIKE "Facebook%") OR (action LIKE "Twitter%"))
    AND created >= "2013-06-29" 
GROUP BY "ip address"

Upvotes: 1

Related Questions