CODEWITHSUNDEEP
phpcodeigniteractiverecordquery-builderin-subquery
mardon
mardon

Reputation: 1125

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?

Upvotes: 70

Views: 124877

Answers (9)

mickmackusa
mickmackusa

Reputation: 47894

There isn't yet a single answer that shows the most modern and most refined query builder script.

  • Declare a secure subquery with `get_compiled_select().
  • Pass the subquery into where_not_in() in the parent query and turn off escaping (the default escaping behavior will ruin the query).
  • When selecting all columns, there is never any need to explicitly chain ->select('*') -- that is the default behaviour.

Model Method:

public function getNonRevokedCertificates(): array
{
    $sub = $this->db->select('id_cer')->get_compiled_select('revokace');
    return $this->db->where_not_in('id', $sub, false)->get('certs')->result();
}

Rendered SQL:

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

For anyone who doesn't want to use a subquery, a LEFT JOIN can be used.

public function getNonRevokedCertificates(): array
{
    return $this->db
        ->select('certs.*')
        ->join('revokace', 'revokace.id_cer = certs.id', 'LEFT')
        ->get_where('certs', 'revokace.id_cer IS NULL')
        ->result();
}

Rendered SQL:

SELECT `certs`.*
FROM `certs`
LEFT JOIN `revokace` ON `revokace`.`id_cer` = `certs`.`id`
WHERE `revokace`.`id_cer` IS NULL

Upvotes: 0

Abd Abughazaleh
Abd Abughazaleh

Reputation: 5525

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

Upvotes: 0

x3mCharles
x3mCharles

Reputation: 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();

Upvotes: -1

user3644751
user3644751

Reputation: 15

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...

Upvotes: -1

Mohit Bumb
Mohit Bumb

Reputation: 2493

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

Upvotes: -2

user2336400
user2336400

Reputation: 491

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

Upvotes: 49

gen_Eric
gen_Eric

Reputation: 227220

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

Upvotes: 93

Abhijit Mazumder
Abhijit Mazumder

Reputation: 9444

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

Upvotes: 2

mattumotu
mattumotu

Reputation: 1504

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.

Upvotes: 18

Related Questions