Codeigniter query to correctly encapsulate AND and OR conditions in a WHERE clause

Codeigniter query to correctly encapsulate AND and OR conditions in a WHERE clause

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Handling Mixed AND/OR Logic in CodeIgniter Queries

Building complex database queries in CodeIgniter often requires juggling multiple conditions where some are grouped by AND and others within those groups by OR. A common stumbling block occurs when you need to enforce specific filters like status or ID, while allowing a flexible choice for delivery states. Consider a scenario where you must retrieve records matching three distinct criteria:

  1. wrk_fld_exc = 140
  2. wrk_cs_sts = 'Open'
  3. wrk_dlvrd_sts = 'Delivered' OR wrk_cl_sts = 'Success'

The logical structure here is (condition_1) AND (condition_2) AND ((condition_3a) OR (condition_3b)). Getting the parentheses right in your raw SQL string is easy to mess up, especially when using ORM helpers. In CodeIgniter's Query Builder, this logic translates directly to building groups of conditions and merging them with explicit operators.

You can start by initializing the query object within your controller or model:

$this->db->select('*');
$this->db->from('your_table_name');

The core challenge lies in constructing the where clause without hardcoding a massive string that risks SQL injection or syntax errors. CodeIgniter's Query Builder allows you to chain conditions, but it does not automatically group OR statements inside an outer AND block unless you explicitly tell it to. To achieve the desired (A) AND (B) AND ((C) OR (D)) structure, you must use the where_group() method or manually construct groups using array syntax.

Here is how you can assemble this safely:

$query = $this->db->get();

// Define the first condition
$this->db->where('wrk_fld_exc', 140);

// Define the second condition
$this->db->where('wrk_cs_sts', 'Open');

// Create a group for the third condition containing an OR
$this->db->where_group('wrk_dlvrd_sts', 'wrk_cl_sts', [
    ['field' => 'wrk_dlvrd_sts', 'value' => 'Delivered'],
    ['field' => 'wrk_cl_sts', 'value' => 'Success']
], 'OR');

$query = $this->db->get();

Alternatively, if you prefer a more explicit approach using array syntax which is often easier to debug, you can pass the grouped conditions directly:

$this->db->where('wrk_fld_exc', 140);
$this->db->where('wrk_cs_sts', 'Open');

// This creates: (wrk_dlvrd_sts = 'Delivered' OR wrk_cl_sts = 'Success')
$this->db->where('(wrk_dlvrd_sts = \'Delivered\' OR wrk_cl_sts = \'Success\')', NULL, FALSE);

$query = $this->db->get();

The second example uses the third parameter of the where() method to pass a boolean flag. Setting it to FALSE tells CodeIgniter not to add outer parentheses around this specific condition, assuming you have already wrapped the logic in SQL parentheses yourself within the string. However, relying on raw strings inside where() is generally less safe than utilizing the builder's grouping features if you are mixing dynamic inputs.

A critical pitfall to avoid is assuming that chaining where() calls automatically groups them correctly. If you were to write:

$this->db->where('wrk_fld_exc', 140);
$this->db->where('wrk_cs_sts', 'Open');
$this->db->or_where('wrk_dlvrd_sts', 'Delivered');
$this->db->or_where('wrk_cl_sts', 'Success');

The resulting SQL would be WHERE wrk_fld_exc = 140 AND wrk_cs_sts = 'Open' OR wrk_dlvrd_sts = 'Delivered' OR wrk_cl_sts = 'Success'. Due to operator precedence, this effectively becomes (A AND B) OR C OR D, which returns incorrect results. The OR conditions must be strictly contained within their own logical group before being combined with the main AND filters.

When working with legacy CodeIgniter versions or specific custom database classes, ensure you are using the active query builder methods rather than raw SQL injection unless absolutely necessary. If you need to migrate this logic to a modern stack that offers even more expressive syntax for complex boolean logic, resources from LaravelCompany can be helpful for understanding how other frameworks handle similar grouping needs with their respective Eloquent or Query Builder implementations.

Ultimately, the key to success is explicitly defining the scope of your OR operations so they never leak out to affect the main AND conditions unintentionally. Always test your generated SQL using a debugger or logging feature to verify the parenthesis placement matches your logical intent.

Our network