Using WHERE NOT IN CLAUSE in codeigniter

Question

I have the following query. Could you please tell me how to implement it in codeigniter.

SELECT mnu_name,mnu_desc,mnu_type FROM central_web_auth_mnu 
  WHERE mnu_name NOT IN(
      SELECT mnu_name FROM central_web_auth_grp_mnu WHERE  gr_id='.$grp.'
  ) ORDER BY mnu_desc;

Thank You.

Answer 1

 $this->db->select('mnu_name, mnu_desc, mnu_type')->where('`mnu_name` NOT IN (SELECT `mnu_name` FROM `central_web_auth_grp_mnu` WHERE idgr_id= $grp)', NULL, FALSE)->order_by('mnu_desc', 'desc')->get('central_web_auth_mnu');

set false as third parameter so that escaping will skip:

Answer 2

CI active db library does support array params in where_in() or where_not_in() methods, so you can use where() method and pass condition as string, try this query

$this->db->select('mnu_name, mnu_desc, mnu_type')
->where('mnu_name NOT IN(SELECT mnu_name FROM central_web_auth_grp_mnu WHERE gr_id='.$grp.')')
->order_by('mnu_desc', 'desc')
->get('central_web_auth_mnu')

Answer 3

You can simply use codeigniter's Active Record library to achieve this (thanks Girish)

$this->db->select('mnu_name, mnu_desc, mnu_type')
->where('mnu_name NOT IN(SELECT mnu_name FROM central_web_auth_grp_mnu WHERE gr_id='.$grp.')')
->order_by('mnu_desc', 'desc');

$query = $this->db->get();
$num_rows = $query->num_rows();