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

User · Answer

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

User · Answer

The functions  compile select   and  reset select   are deprecated  Instead use get compiled select      Create where clause  this- gt db- gt select  id cer     this- gt db- gt from  revokace     where clause    this- gt db- gt get compiled select      Create main query  this- gt db- gt select        this- gt db- gt from  certs     this- gt db- gt where   id  NOT IN   where clause    NULL  FALSE

User · Answer

For query  SELECT   FROM  SELECT id  product FROM product  as product you can use    sub query from     SELECT id  product FROM product   as product    this- gt db- gt select     this- gt db- gt from  sub query from    query    this- gt db- gt get     Please notice  that in sub query from string you must use spaces between     product   as

User · Answer

CodeIgniter Active Records do not currently support sub-queries  However I use the following approach    Create where clause  this- gt db- gt select  id cer     this- gt db- gt from  revokace     where clause    this- gt db- gt  compile select     this- gt db- gt  reset select      Create main query  this- gt db- gt select        this- gt db- gt from  certs     this- gt db- gt 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

User · Answer

Like this in simple way        this- gt db- gt select            this- gt db- gt from  certs         this- gt db- gt where  certs id NOT IN  SELECT id cer FROM revokace          return  this- gt db- gt get  - gt result

User · Answer

I think this code will work  I dont know if this is acceptable query style in CI but it works perfectly in my previous problem       subquery    SELECT id cer FROM revokace     this- gt db- gt select        this- gt db- gt where not in id   subquery    this- gt db- gt from  certs     query    this- gt db- gt get

User · Answer

this- gt db- gt where   id  IN  SELECT  someId  FROM  anotherTable  WHERE  someCondition   condition     NULL  FALSE     Source   http   www 247techblog com use-write-sub-queries-codeigniter-active-records-condition-full-explaination

User · Answer

where             where    admin trek trek      search      AND         where    admin trek state id     search     OR         where    admin trek difficulty     search    OR         where    admin trek month     search     AND         where    admin trek status   1          this- gt db- gt select            this- gt db- gt from  admin trek         this- gt db- gt join  admin difficulty  admin difficulty difficulty id   admin trek difficulty         this- gt db- gt where  where         query    this- gt db- gt get

User · Answer

- gt where   support passing any string to it and it will use it in the query   You can try using this    this- gt db- gt select     - gt from  certs     this- gt db- gt 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- gt db- gt select     - gt from  certs     sub    this- gt subquery- gt start subquery  where in     sub- gt select  id cer  - gt from  revokace     this- gt subquery- gt end subquery  id   FALSE

[codeigniter] subquery in codeigniter active record

Examples related to codeigniter