subquery in codeigniter active record

Question

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

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

Answer 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);