"SELECT ... IN (SELECT ...)" query in CodeIgniter

"SELECT ... IN (SELECT ...)" query in CodeIgniter

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

Stefan Bogdanescu

Founder & Senior Architect

Nested Queries and Substring Handling in CodeIgniter

Handling nested SQL queries, such as SELECT ... IN (SELECT ...), is a common requirement when dealing with hierarchical data or filtering by related entities. In your specific case, you are trying to retrieve usernames from a users table based on locations within a specific country provided by the end user. The core challenge here isn't just the SQL syntax itself, but how CodeIgniter's Active Record (Query Builder) handles string interpolation and dynamic subqueries safely.

The query structure you presented relies on a parameterized value ($) passed directly into the WHERE clause of the inner query. In a raw context, this is straightforward. However, when integrating this into CodeIgniter, the framework's security mechanisms—specifically its protection against SQL injection—require a slightly different approach than simple string concatenation.

To execute this in CodeIgniter without falling back to raw queries, you must leverage the where_in method combined with a custom subquery definition or, more commonly, constructing the subquery manually within the query builder chain. The most robust way to handle the outer condition is by using the select() and where() methods to build the inner logic before injecting it into the main selection.

Here is how you can structure this in a controller or model:

// Assuming $country_id contains the value from your end user
$country_id = $this->input->get('country_id'); // Or however you retrieve the input

// 1. Build the subquery first
$subquery = $this->db->select('locationid', false)
    ->from('locations')
    ->where('countryid', $country_id)
    ->get_compiled_select();

// 2. Run the main query using the compiled subquery string
$query = $this->db->select('username')
    ->from('users')
    ->where_in('locationid', '(' . $subquery . ')')
    ->get();

$users = $query->result();

The critical detail here is the use of get_compiled_select(). This method returns the raw SQL string generated by the query builder for the inner statement. By wrapping this string in parentheses and passing it to where_in, you create the exact nested structure required. While some might argue that using a raw string with where_in bypasses parameterization for the subquery portion, the actual variable $country_id is still bound safely by the builder before the string is compiled into the inner query object.

However, there are trade-offs to consider regarding maintainability and debugging. When you inject a compiled SQL string directly into another clause using string concatenation (as seen in where_in), you lose some of the visual clarity that the Query Builder provides for complex conditions. If your subquery logic becomes significantly more complex—adding multiple joins or additional filters—the compiled string can become difficult to read and modify later.

In such scenarios, switching to raw queries might actually be a cleaner solution than forcing the nested logic into the Active Record layer. You could execute the subquery separately, fetch the resulting IDs into an array, and then use that array directly in the main query's where_in. This approach separates concerns: the inner query is fully parameterized for safety, and the outer query receives a clean list of values rather than a stringified SQL block.

// Alternative approach separating concerns
$subquery_result = $this->db->select('locationid')
    ->from('locations')
    ->where('countryid', $country_id)
    ->get()
    ->result();

$location_ids = array_column($subquery_result, 'locationid');

if ($location_ids) {
    $query = $this->db->select('username')
        ->from('users')
        ->where_in('locationid', $location_ids)
        ->get();
} else {
    // Handle case where no locations match the country
}

This second pattern is often preferred in production environments because it ensures that every variable interacting with SQL is handled through the framework's safe binding mechanisms, eliminating any risk of injection even if the input changes format. It also makes debugging much easier since you can inspect $location_ids before executing the final query.

If you find yourself needing more advanced features like unions or complex joins within these nested structures, remember that CodeIgniter does not natively support subqueries inside where_in as a single atomic unit in older versions. In those cases, or if you are working on a legacy project, checking the version-specific documentation is essential. For instance, some newer database adapters handle string injection differently than others.

For developers seeking further insights into complex query patterns or migration strategies from other frameworks, resources like https://laravelcompany.com often provide detailed comparisons and best practices regarding ORM usage in modern PHP ecosystems. Understanding these nuances ensures that your application remains secure regardless of the underlying database complexity.

Our network