How to Write SubQuery in CodeIgniter Active Record

CodeIgniter Subquery: This tutorial will explain you how to write subquery in codeigniter using active records. SubQuery is a query within query. Sub query is commonly placed in WHERE clause and called as INNER query and the main query which contains the subquery is called as OUTER query. And a codeIgniter active record doesn't provide options to build sub queries but there are some possible workarounds other than firing direct sql queries. Here I'll show you an approach that makes use of active records class to write subqueries in codeignitor.

How to Write SubQuery in CodeIgniter?

The approach uses the similar technique employed in writing union query in codeigniter active records. Which is, first we should generate sql string for subquery without executing and then use it in the outer query and produce result.

For example here is a codeigniter subquery that returns the details of those books which have their orders placed.

$this->db->select('BookID');
$this->db->from('Orders');
$sub_query = $this->db->get_compiled_select();

$this->db->select('BookID, BookName, AuthorName');
$this->db->from('Books');
$this->db->where("BookID IN ($sub_query)");
$query = $this->db->get()->result();

// Produces SQL:
// SELECT BookID, BookName, AuthorName
// FROM Books
// WHERE BookID IN (SELECT BookID FROM Orders);

In our query we have used a function $this->db->get_compiled_select(); which will just compile the active records query without running it. Please make sure you use CodeIgniter 3 as this method is quite new and not found in older versions.

Next is another example of code igniter subquery which picks up all employee records with salary greater than that of average salary.

$this->db->select_avg('Salary');
$this->db->from('Employees');
$sub_query = $this->db->get_compiled_select();

$this->db->select('*');
$this->db->from('Employees');
$this->db->where("Salary > ($sub_query)");
$query = $this->db->get()->result();

// Produces SQL:
// SELECT * 
// FROM Employees 
// WHERE Salary > (SELECT AVG(Salary) FROM Employees);

SubQueries are widely used inside WHERE clause but they can also be injected within SELECT or FROM section of an SQL statement.

Read Also: How to Write JOIN Query in CodeIgniter

Don't Miss: How to Get Last Inserted Id in CodeIgniter

And that explains about writing subqueries in codeigniter active records. I hope you find this tutorial useful. Meet you in another interesting article.