How to use OR Condition in Paginate function in Cakephp ??

Question

Hi! I am facing a problem while fetching values using paginate function in cakephp. In the "to" field of message I have CSV fields of userid. To search messages for a single user. I am using the code below...

$this->set('message', $this->paginate('Message', array(
    'or'=> array(
        "Message.to LIKE" => "".$this->Session->read('Auth.User.id').",",
        "Message.to LIKE" => ",".$this->Session->read('Auth.User.id').","
    )
)));

But the query is formed in this manner which is not what I want.. I want to two conditions with OR condition.

SELECT `Message`.`id`, `Message`.`timestamp`, `Message`.`to`, `Message`.`from`,
       `Message`.`message`, `Message`.`subject`, `Message`.`urgent`, `Message`.`read`,
       `Message`.`tag`, `Message`.`open`, `Message`.`reply_id`, `User`.`id`,
       `User`.`fname`, `User`.`lname`, `User`.`user`, `User`.`password`,
       `User`.`photo`, `User`.`created`, `User`.`access`, `User`.`login`,
       `User`.`status`, `User`.`role`
FROM `messages` AS `Message`
LEFT JOIN `users` AS `User` ON (`Message`.`to` = `User`.`id`)
WHERE `Message`.`to` LIKE ',1,'
ORDER BY `Message`.`timestamp` desc
LIMIT 5

Answer 1

The problem is that your conditions array stumbles across a PHP limitation: an array can not have two keys with the same name.

'or'=> array(
    "Message.to LIKE" => "".$this->Session->read('Auth.User.id').",",
    "Message.to LIKE" => ",".$this->Session->read('Auth.User.id').","
)

You are using the key "Message.to LIKE" twice, so only the last one gets used.

The workaround looks like this:

'or'=> array(
    array("Message.to LIKE" => $this->Session->read('Auth.User.id') . ","),
    array("Message.to LIKE" => "," . $this->Session->read('Auth.User.id') . ",")
)

Oh, and you should seriously think about a) database normalization and b) code readability.

Answer 2

I think part of the problem might be that he is using LIKE with only the literal exact match syntax.

If any of the following assumptions are incorrect then ignore this..

But it seems like you are searching for [email protected] in the to string and having to attach an 'or' condition to match the case where the recipient is listed as a single email address in a string of comma separated email addresses...

I don't think cake adds the '%' character to LIKE queries automagically so perhaps you could try adding it to the query. This should make it match both cases with a single condition in the find call.

<?php
    ... snip ...
    'conditions' => array(
        "Message.to LIKE" => "%" . $this->Session->read( 'Auth.User.id' ) . "%"
    ),
    ... snip ...
?>