+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: SQL query with 1 fixed and 2 OR conditions (/showthread.php?tid=88855)
SQL query with 1 fixed and 2 OR conditions - sjender - 11-21-2023
Hi all,
I am trying to find a clean way (without using raw SQL) to do this:
SELECT * FROM table WHERE user_id = 123 AND (role_id IN (1,2,3,4) OR role_id IS NULL)
So I always need to check the user_id, but the role_id may be in the list or NULL.
PHP Code:
$this->usersModel
->where('user_id', $userId)
->whereIn('role_id', [1,2,3,4])
->orWHere('role_id', null)
->findAll();
But this results in: SELECT * FROM table WHERE user_id = 123 AND role_id IN (1,2,3,4) OR role_id IS NULL.
So it will show also the records where role_id is null, regardless of the user_id.
Any ideas how to do this?
RE: SQL query with 1 fixed and 2 OR conditions - sclubricants - 11-21-2023
You need to use Query Grouping. See https://www.codeigniter.com/user_guide/database/query_builder.html#group
Code:
<?php
$builder->select('*')->from('my_table')
->groupStart()
->where('a', 'a')
->orGroupStart()
->where('b', 'b')
->where('c', 'c')
->groupEnd()
->groupEnd()
->where('d', 'd')
->get();
/*
* Generates:
* SELECT * FROM (`my_table`) WHERE ( `a` = 'a' OR ( `b` = 'b' AND `c` = 'c' ) ) AND `d` = 'd'
*/