SQL query with 1 fixed and 2 OR conditions

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?

You need to use Query Grouping. See https://www.codeigniter.com/user_guide/d...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'
*/