subquery in codeigniter active record

SELECT * FROM certs WHERE id NOT IN (SELECT id_cer FROM revokace);

How do I write the above select statement in CodeIgniter active record?

Solution 1:

->where() support passing any string to it and it will use it in the query.

You can try using this:

$this->db->select('*')->from('certs');
$this->db->where('`id` NOT IN (SELECT `id_cer` FROM `revokace`)', NULL, FALSE);

The ,NULL,FALSE in the where() tells CodeIgniter not to escape the query, which may mess it up.

UPDATE: You can also check out the subquery library I wrote.

$this->db->select('*')->from('certs');
$sub = $this->subquery->start_subquery('where_in');
$sub->select('id_cer')->from('revokace');
$this->subquery->end_subquery('id', FALSE);

Solution 2:

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

#Create where clause
$this->db->select('id_cer');
$this->db->from('revokace');
$where_clause = $this->db->get_compiled_select();

#Create main query
$this->db->select('*');
$this->db->from('certs');
$this->db->where("`id` NOT IN ($where_clause)", NULL, FALSE);

Solution 3:

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

#Create where clause
$this->db->select('id_cer');
$this->db->from('revokace');
$where_clause = $this->db->_compile_select();
$this->db->_reset_select();

#Create main query
$this->db->select('*');
$this->db->from('certs');
$this->db->where("`id` NOT IN ($where_clause)", NULL, FALSE);

_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.

Solution 4:

It may be a little late for the original question but for future queries this might help. Best way to achieve this is Get the result of the inner query to an array like this

$this->db->select('id');
$result = $this->db->get('your_table');
return  $result->result_array();

And then use than array in the following active record clause

$this->db->where_not_in('id_of_another_table', 'previously_returned_array');

Hope this helps