使用关联的SQL查询到cakephp查询

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:

1. sender_id is 3
2. OR user_id is 40
3. OR offer_id is 1

So, 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)

• asterisk in sql query dump for more readability

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