Subquery in active codeigniter record

Question

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

How to write the above select statement in an active CodeIgniter record?

Answer 1

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

You can try:

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

,NULL,FALSE in where() tells CodeIgniter not to avoid the request, which can ruin it.

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

Answer 2

The functions _compile_select() and _reset_select() are deprecated.
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); 

Answer 3

CodeIgniter Active Records does not currently support subqueries. 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 methods (AFAIK) that compile the query and return sql (without starting it) and reset the query.

In the main FALSE request, the where clause tells codeigniter not to avoid the request (or add backticks, etc.), which would ruin the request. (NULL simply because the where clause has an optional second parameter, which we do not use)

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

Answer 4

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->db->select(); $this->db->from($sub_query_from); $query = $this->db->get() 

Note that in the sub_query_from line you should use spaces between ... product ) as...

Answer 5

It may be a little late for the original question, but for future queries this may help. The best way to achieve this - Get the result of an internal query into an array like this

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

And then use than an array in the next active record declaration

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

Hope this helps

Answer 6

 $this->db->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/

Answer 7

I think this code will work. I don't know if this is an acceptable type of request in CI, but it works fine in my previous problem. :)

 $subquery = 'SELECT id_cer FROM revokace'; $this->db->select('*'); $this->db->where_not_in(id, $subquery); $this->db->from('certs'); $query = $this->db->get(); 

Answer 8

  $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->db->select('*'); $this->db->from('admin_trek'); $this->db->join('admin_difficulty',admin_difficulty.difficulty_id = admin_trek.difficulty'); $this->db->where($where); $query = $this->db->get();