CodeIgniter Forums

Full Version: $this->db->where_not_in($this->db->select()) will it blend?
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]mjsilva[/eluser]
Hi fellows,

Just wondering if anyone try to neesting this functions, is it possible to do something like this:

I have this query:

Code:
SELECT
  tab_products.product_id,
  tab_products.cod,
  tab_products.description,
  tab_prod_family.description AS Family,
  (IF(tab_products.composite!=0, 'True', 'False')) AS 'Composite'
FROM tab_products
JOIN tab_prod_family ON tab_prod_family.prod_family_id = tab_products.prod_family_id
WHERE tab_products.product_id NOT IN(SELECT
                                       tab_products.product_id
                                     FROM tab_products
                                       JOIN tab_prod_composite
                                         ON tab_products.product_id = tab_prod_composite.product_id
                                     WHERE tab_prod_composite.child_prod_id = 16)
    AND tab_products.product_id != 16


Can I use active record in the where_not_in part?

El Forum

[eluser]mjsilva[/eluser]
This didn't work either:

Code:
$this->db->select("tab_products.product_id,
                  tab_products.cod,
                  tab_products.description,
                  tab_prod_family.description AS 'family',
                  (IF(tab_products.composite!=0, '".lang('yapp_yes')."', '".lang('yapp_no')."')) AS 'Composite'", FALSE);
$this->db->from('tab_products');
$this->db->join('tab_prod_family','tab_prod_family.prod_family_id = tab_products.prod_family_id');
$this->db->where_not_in('tab_products.product_id', "SELECT
                                                       tab_products.product_id
                                                     FROM tab_products
                                                       JOIN tab_prod_composite
                                                         ON tab_products.product_id = tab_prod_composite.product_id
                                                     WHERE tab_prod_composite.child_prod_id = $prod_id", FALSE);
$this->db->where_not_in('tab_products.product_id', $where_not);
$query = $this->db->get();
$this->yapp_log->do_log(2,'query', $this->db->last_query());
if ($query->num_rows () > 0) return $query->result_array();

Anyway I can make this work with AR?

El Forum

[eluser]Colin Williams[/eluser]
The presence of an Active Record class does not make plain SQL evil, especially when it gets complex. You might be better off with the $this->db->query() function.

And no, $this->db->select() does not return a query, so the idea in the topic title wouldn't pan out.

El Forum

[eluser]mjsilva[/eluser]
Thanks for the answer Colin.

It would be a good feature if active record have something like we have in views eg: $this->load->view('my_view', TRUE) to "spit" the content of the view insteed of loading it, same for AR a $this->db->select('select * from playBoyMansion', TRUE) could return the SQL select statement.

Just for security sake since AR automatically escapes and sanitize the query.

As for my problem, I've fixed it with plain SQL as u suggest, thanks.

El Forum

[eluser]Colin Williams[/eluser]
The DB class has functions for escaping too, or you can use query bindings with the query method, so your query will remain safe.

There is a method to compile a query (_compile_select(), I believe) so you could toy with that, but it sounds unnecessarily complex to me. Best of luck.