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

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

Barilee
  • 57
  • 6
mardon
  • 1,065
  • 1
  • 11
  • 29

9 Answers9

93

->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);
gen_Eric
  • 223,194
  • 41
  • 299
  • 337
48

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);
nalply
  • 26,770
  • 15
  • 78
  • 101
user2336400
  • 481
  • 4
  • 3
  • 2
    I personally almost prefer this as I'm able to segment each subquery & keep them safely escaped & prepared once complied. – MackieeE Oct 27 '15 at 14:51
  • They are not depreciated in codeigniter 2.7 which is still in use. The Op asked this question in 2011 which is before Codeigniter 3. If you are using codeigniter 2.7 then the answer by @mattumotu is the correct one. – PrestonDocks Jan 31 '18 at 08:00
18

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.

mattumotu
  • 1,436
  • 2
  • 14
  • 35
  • 5
    $this->db->_compile_select(); is deprecated and I believe that _reset_select() is also deprecated. This answer is absolute. – Babak Bandpay Jan 14 '12 at 15:36
  • @Bandpay yes sadly realized after tested queries. – mirza Feb 10 '12 at 17:25
  • 1
    `_compile_select()` is not deprecated. For unknown reasons, as of CI 2.1.0, it's a protected function (in system/database/DB_active_rec.php), which means you can't use it unless you remove the "protected" clause from the function declaration (but always be wary of modifying the core). – pbarney Nov 07 '13 at 00:32
  • Check out [this answer](http://stackoverflow.com/a/10413855/12570) to a related question for an addition to CI to get this back (rather than modding). – Peter K. Dec 10 '13 at 15:25
  • In ExpressionEngine 2.7.3 the core of EE is using `_compile_select`. Its CI is apparently version 2.0.1. The method is used in features introduced in EE 2.7 itself, so I would like to think it won't be going anywhere for a while or they'd come up with a workaround (e.g. the `get_compiled_select` method in another answer). – notacouch Jan 08 '14 at 19:28
2

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

Abhijit Mazumder
  • 8,641
  • 7
  • 36
  • 44
  • 7
    I think the problem with this solution is that it requires two calls to the database, whereas a subquery only requires one. – eelkedev Nov 24 '12 at 12:48
  • I agree. But I find this much more readable. If There is not a significant performance hit due to the extra query being fired I would prefer this approach instead of writing subquery which may involve its own pitfalls – Abhijit Mazumder Nov 27 '12 at 13:09
  • I would not use this in one of my projects. It is unnecessary to make 2 trips to the DB when the work can be done in one. This technique should not be promoted to researchers looking for an elegant/professional solution. – mickmackusa May 09 '20 at 09:43
0

Like this in simple way .

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

    return $this->db->get()->result();
Abd Abughazaleh
  • 4,615
  • 3
  • 44
  • 53
-1

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

Please notice, that in sub_query_from string you must use spaces between ... product ) as...

Lorenz Meyer
  • 19,166
  • 22
  • 75
  • 121
  • This doesnt looks much like a subquery but more as a string query? – killstreet Jan 15 '16 at 14:19
  • This answer is not fully appreciating CI's query building toolkit. Earlier answers demonstrate superior techniques. No new value to this page. – mickmackusa May 09 '20 at 09:41
  • This query will not working because you dons't have correct query and also in sub query you must add table and you don't have a sub query anyway . – Abd Abughazaleh Jul 17 '20 at 22:33
-1

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->db->select('*');
$this->db->where_not_in(id, $subquery);
$this->db->from('certs');
$query = $this->db->get();
  • This is only "half active record". Earlier posts demonstrate how to fully implement the active record syntax. This late answer does not bring new value to this page. – mickmackusa May 09 '20 at 09:32
-2
$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/

Mohit Bumb
  • 2,466
  • 5
  • 33
  • 52
  • This answer does not fully implement CI's available methods to build a query. Earlier posted answers provide better advice than this post. No new value to this page. Furthermore, the hyperlink did not load so this is a code-only answer. No value to this page or researchers. – mickmackusa May 09 '20 at 09:36
-2
    $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();
Shashank Saxena
  • 2,014
  • 17
  • 11
  • This code-only answer has absolutely no affiliation to the question asked. At best, this is the correct answer to a different question. Honestly though, this snippet is not fully appreciating the available methods that CI's active record offers. No value to the page. – mickmackusa May 09 '20 at 09:34