Here is my correct sql query:
SELECT * FROM `messages` WHERE ( (sender_id=3 AND user_id=40) OR (sender_id=40 AND user_id=3)) AND offer_id=1
I want to use this in Cakephp syntax:
$this->Message->find('all',array('conditions'=>array(
'AND'=>array(
'OR'=>array(
'Message.offer_id'=>$offer_id,
'Message.sender_id'=>$sender_id,
'Message.user_id'=>$this->Auth->user('id'),
),
'OR'=>array(
'Message.offer_id'=>$offer_id,
'Message.user_id'=>$sender_id,
'Message.sender_id'=>$this->Auth->user('id')
)
)
),
'recursive'=>2
));
Is there anyone who can help me to figure out the issue. Basically I want to get all the messages whether I sent or received for an particular offer.
You should move $offer_id
out of or
conditions and move it to and
conditions.
Why? Lets look at your first or
array: That conditions will return true if:
sender_id
is 3user_id
is 40offer_id
is 1So, that condition may return true event if offer_id != 1
That should be written this way (as precisely as possible according to original query):
$query = $this
->Messages
->find('all' , [
'conditions' => [
'or' => [
[
'sender_id' => $sender_id,
'user_id' => $this->Auth->user('id')
], [
'sender_id' => $this->Auth->user('id'),
'user_id' => $sender_id
]
],
'offer_id' => $offer_id,
]
]);
dump($query);
In dump we can see something like this:
"SELECT * FROM messages Messages WHERE (((sender_id = :c0 AND user_id = :c1) OR (sender_id = :c2 AND user_id = :c3)) AND offer_id = :c4)
You have the AND
and OR
operators reversed.
'OR'=>array(
'AND'=>array(
'Message.offer_id'=>$offer_id,
'Message.sender_id'=>$sender_id,
'Message.user_id'=>$this->Auth->user('id'),
),
'AND'=>array(
'Message.offer_id'=>$offer_id,
'Message.user_id'=>$sender_id,
'Message.sender_id'=>$this->Auth->user('id')
)
)