How to write Sub Queries in Codeigniter Active Record ?

Last updated on December 2, 2022

Codeigniter didn’t provide an option to use subqueries with active record class. So today I would like to provide simple and possible comprehensive techniques on “creating Sub Queries with Codeigniter Active Record”.

Creating subquery in Codeigniter is as easy as creating the regular Active Record queries. The technique here is first we need to generate a query and the generated query we need to inject it into the main query as subQuery.

So first we need to generate a subquery and store that generated the query in a variable as a string. After generating the subquery we will generate the main query by using a previously generated query as a subquery.

For Example – I am going to generate a blow query with active record methods

SELECT * FROM employees WHERE id IN(SELECT id FROM employees_backup); 

// Sub Query
$this->db->select('id')->from('employees_backup');
$subQuery =  $this->db->get_compiled_select();

// Main Query
$this->db->select('*')
         ->from('employees')
         ->where("id IN ($subQuery)", NULL, FALSE)
         ->get()
         ->result();

The NULL, FALSE in the where() method tells CodeIgniter not to escape the query.