How To Write Subquery In Codeigniter Active Record

CodeIgniter Active Records do not currently support sub-queries, However I use the following approach:

$this->db->select('id');
$this->db->from('table');
$where_clause = $this->db->_compile_select(false,true);
$this->db->_reset_select();

#Create main query

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

$this->db->from('table1');

$this->db->where("`id` NOT IN ($where_clause)", NULL, FALSE);

return $this->db->get('',null,null,true);

_compile_select() and _reset_select() are two undocumented (AFAIK) methods which compile the query and return the sql (without running it) and reset the query.

On the main query the FALSE in the where clause tells codeigniter not to escape the query (or add backticks etc) which would mess up the query. (The NULL is simply because the where clause has an optional second parameter we are not using)

However you should be aware as _compile_select() and _reset_select() are not documented methods it is possible that there functionality (or existence) could change in future releases.

The functions _compile_select() and _reset_select() are deprecated.
Instead use get_compiled_select():