Using CodeIgniter's Active Record Class to Create Subqueries

A good friend of mine recently asked me how he would create a subquery using Code Igniter's Active Record class.  This does present a challenge as Code Igniter's Active Record class does not natively support subqueries.

However, if you look into the code for the Database class you'll see that CI uses some handy utility methods to compile the SQL for processing.  These methods exist in all Code Igniter drivers (providing the cross platform abstraction that makes Active Record so valuable in the first place).

This algorithm is definitely far from perfect, but it DOES provide some level of abstraction that manually typing your subquery would not.  If you have any questions, concerns, or suggestions feel free to leave them as comments.

// Generate the subquery
$this->db->select('count(*)');
$this->db->from('users');

// Render the subquery to a string
$subQuery = $this->db->_compile_select();

// Reset active record
$this->db->_reset_select();

// Generate the primary query and include the subquery
$this->db->select('users.id as userId, users.fullname as userName');
$this->db->select("($subQuery) as userCount");
$this->db->where('users.status', 'active');

To break this down into smaller parts:

// Generate the subquery
$this->db->select('count(*)');
$this->db->from('users');

This code generates the query: select count(*) from users

// Render the subquery to a string
$subQuery = $this->db->_compile_select();

This code uses Code Igniter's Active Record class to generate an abstracted select statement. Whether we're using Oracle, MySQL, or PostgreSQL the statement will be rendered appropriately by Active Record.

// Reset active record
$this->db->_reset_select();

This method is usually called automatically by the Active Record class after a select statement is completed. This will clear the Active Record cache so that our "select"(count(*)) and "from"(users) commands will not appear in the NEXT active record statement that we create.

// Generate the primary query and include the subquery
$this->db->select('users.id as userId, users.fullname as userName');
$this->db->select("($subQuery) as userCount");
$this->db->where('users.status', 'active');

This code generates a select statement calling back the userID and the userName as well as aliasing the subquery variable string as 'userCount'. Using the MySQL driver the SQL that is output is as follows:

SELECT `users`.`id` as userId, `users`.`fullname` as userName, (SELECT count(*) FROM (`users`)) as userCount FROM (`users`) WHERE `users`.`status` = 'active'

Keep in mind that the developers of Code Igniter did not intend for you to use the _compile_select method. Therefore, it's possible that they may change the functionality in future versions and not bother letting anyone know.