ORM query builder for complex condition in CakePHP

Question

I have user_messages table with columns id, sender_id, receiver_id, message, deleted

I have to retrieve all messages like this

SELECT *
FROM 
    user_messages UserMessages 
WHERE (
    UserMessages.deleted = false
    AND (
        (sender_id = $loggedin_user_id AND receiver_id = $user_id)
        OR 
        (sender_id = $user_id AND receiver_id = $loggedin_user_id)
    ) 
ORDER BY 
    created DESC

Currently, I'm using this query builder

$message_by_list = $this->UserMessages->find()
    ->where(['UserMessages.deleted' => false])
    ->andWhere(function ($exp) {
        return $exp->or_([
            'sender_id' => $this->Auth->user('id'),
            'receiver_id' => $this->Auth->user('id')
        ]);
    })

which is generating sql query as

FROM 
    user_messages UserMessages  
WHERE 
    (UserMessages.deleted = false AND (sender_id = $loggedin_user_id OR receiver_id = $loggedin_user_id)) 
ORDER BY 
    created DESC

How to write optimized ORM Query to retrieve data as above?

Edit 2 : Updated query for arilia's answer

WHERE (
        (
            UserMessages.deleted = false
        ) 
        AND 
        (
            (
                UserMessages.sender_id = $loggedin_user_id
                AND 
                UserMessages.receiver_id = $user_id
            )
            OR 
            UserMessages.sender_id = $user_id 
            OR                         /// doubt here
            UserMessages.receiver_id = $loggedin_user_id
        )
    )

is edit2 the query you want to obtain or the query generated by my code? Because I get an AND where you put 'doubt here', not an OR — arilia, Feb 18 '17 at 09:50

arilia · Answer 1 · 2017-02-20T10:06:52.980

2

try this

$message_by_list = $this->UserMessages->find()
    ->where(['sender_id' => $user_id, 'receiver_id' => $logged_user_id])
    ->orWhere(['sender_id' => $logged_user_id, 'receiver_id' => $user_id])
    ->andWhere(['UserMessages.deleted' => false]);

edited Feb 20 '17 at 10:06

answered Feb 17 '17 at 13:32

arilia

9,373
2
20
44

I have a doubt in generated query. Check check for `/// doubt here` in updated code. I think it will fetch records matching only one column – Gaurav Feb 18 '17 at 05:39

ORM query builder for complex condition in CakePHP

1 Answers1