CakePHP 3.0 : How to make multiple MySQL OR Condition in Query builder

Question

I'm using cakePHP 3.0 for REST API. This is MySQL Query.

SELECT group_chat.message_parents
FROM group_chat    
WHERE group_chat.sender_groups = 1 AND group_chat.`status` = 1 AND group_chat.is_blocked = 0
OR group_chat.recever_groups = 1 AND group_chat.`status` = 1 AND group_chat.is_blocked = 0
OR group_chat.sender_groups = 1 AND group_chat.`status` = 1 AND group_chat.is_blocked = 1 AND group_chat.blocked_by = 1
OR group_chat.recever_groups = 1 AND group_chat.`status` = 1 AND group_chat.is_blocked = 1 AND group_chat.blocked_by = 1
GROUP BY group_chat.message_parents
ORDER BY group_chat.modified DESC;

I converted it in to CakePHP 3.0 Query builder function.

$all_parents = $this->GroupChat->find()->select(['message_parents',])
            ->where(['sender_groups' => $params['group_id'], 'status' => ACTIVE, 'is_blocked' => DEACTIVE, 'blocked_by' => DEACTIVE])
            ->orWhere(['recever_groups' => $params['group_id'], 'status' => ACTIVE, 'is_blocked' => DEACTIVE, 'blocked_by' => DEACTIVE])
            ->orWhere(['sender_groups' => $params['group_id'], 'status' => ACTIVE, 'blocked_by' => $params['group_id']])
            ->orWhere(['recever_groups' => $params['group_id'], 'status' => ACTIVE, 'blocked_by' => $params['group_id']])
            ->order(['modified' => 'DESC'])
            ->group('message_parents');

But it is not producin expected results as Query before. Any help will highly appreciated.

Update:

debug($all_parents);

Debug will output this.

'sql' => '
SELECT GroupChat.message_parents AS `GroupChat__message_parents` 
FROM group_chat GroupChat 
WHERE ((recever_groups = :c0 AND status = :c1 AND is_blocked = :c2 AND blocked_by = :c3) 
  OR (sender_groups = :c4 AND status = :c5 AND is_blocked = :c6 AND blocked_by = :c7) 
  OR sender_groups = :c8 
  OR status = :c9 
  OR blocked_by = :c10 
  OR recever_groups = :c11 
  OR status = :c12 OR blocked_by = :c13) 
GROUP BY message_parents  
ORDER BY modified DESC',

What is the query that gets created? Can you debug and post the query? — Arno, Dec 03 '15 at 08:57
If you switch on debugging, there should be a little cake icon on the bottom right of your browser. Clicking it will show debugging information. Try that? — Arno, Dec 03 '15 at 09:02
@Mayura you should read this http://book.cakephp.org/3.0/en/orm/query-builder.html#advanced-conditions — justrohu, Dec 03 '15 at 09:03
@Arno Yes I have already switched on debugging but since I'm developing a REST API application I can't see any icon. — Mayura Wijewickrama, Dec 03 '15 at 09:47
@justrohu yes I read that book, but it is not helping for my task. — Mayura Wijewickrama, Dec 03 '15 at 09:48
What's the problem with the results? Is it which rows are returned, the order or both? — AgRizzo, Dec 03 '15 at 12:41

score 0 · Answer 1 · answered Dec 03 '15 at 09:45

Your raw SQL query hasn't got any grouping. So it effectively translates to each condition being ORed or ANDed:

WHERE group_chat.sender_groups = 1 
    AND group_chat.`status` = 1
    AND group_chat.is_blocked = 0
    OR group_chat.recever_groups = 1
    AND group_chat.`status` = 1
    AND group_chat.is_blocked = 0
    OR group_chat.sender_groups = 1
    AND group_chat.`status` = 1
    AND group_chat.is_blocked = 1
    AND group_chat.blocked_by = 1
    OR group_chat.recever_groups = 1
    AND group_chat.`status` = 1
    AND group_chat.is_blocked = 1
    AND group_chat.blocked_by = 1

Since A AND A = A and A OR A = A (basic boolean algebra) the above is reduced to:

WHERE group_chat.sender_groups = 1
    OR group_chat.recever_groups = 1
    AND group_chat.`status` = 1
    AND group_chat.is_blocked = 0
    AND group_chat.is_blocked = 1
    AND group_chat.blocked_by = 1

Which is most probably not what you intended.

The CakePHP query you wrote is grouping the ANDs and ORs and that explains why it produces different results.

Actually It should be some thing like this SELECT group_chat.message_parents FROM group_chat WHERE group_chat.`status` = 1 AND ( ( group_chat.sender_groups = 1 OR group_chat.recever_groups = 1 ) AND group_chat.is_blocked = 0 OR (group_chat.is_blocked = 1 AND group_chat.blocked_by = 1)) GROUP BY group_chat.message_parents ORDER BY group_chat.modified DESC; — Mayura Wijewickrama, Dec 03 '15 at 09:57
Ok, so now you know what you want to do, go on and fix it :) I'm glad this answer helped! — user221931, Dec 07 '15 at 13:26

CakePHP 3.0 : How to make multiple MySQL OR Condition in Query builder

1 Answers1