phpcodeigniteractiverecordquery-builderin-subquery

SELECT query WHERE NOT IN a subquery using CodeIgniter's query builder methods

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

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