phpcodeigniteractiverecordsubquerywhere-clause

Codeigniter where in and replace Subquery


How do I write in codeigniter in active record the following query?

SELECT
  time AS date,
  user AS name
FROM b_table
WHERE id IN(SELECT
          a
        FROM a_table
        WHERE group = $group
        AND tag >= '$date1'
        AND tag <= $date2')

This is what I come out with but it does not do the job.

$this->db->select('a');
$this->db->from('a_table');
$this->db->where('group',$group);
$this->db->where('tag>=',$date1);
$this->db->where('tag<=',$date2);

$subQuery = $this->db->_compile_select();
$this->db->_reset_select();

$this->db->select('time AS date, user AS name');
$this->db->from('b_table');
$this->db->where_in($subQuery);

my problem is (a) from select one is record which need to be replace like replace space to be comma or maybe I'm wrong :(,
another I use str_replace but still wrong,

sample data a from select one is
222,111,444
333,444

555,666
replace with 222,111,444,333,444,555,666

even I'm new in codeigniter Appreciate any helps and sorry for my english.
Thanks.


Solution

  • try this:

     ->where() 
    

    support passing any string to it and it will use it in the query.

    so the above will be like:

     $this->db->select('time AS date,user AS name')->from('b_table');
     $this->db->where_not_in('id', "SELECT a FROM a_table WHERE group = $group AND tag >= '$date1' AND tag <= $date2");
    

    hope it will help!