Convert the nesting mysql query into codeigniter s

I have a nested sql query as below.

SELECT  A.ID, A.fName, A.lName,
   COALESCE (B.calls, 0) AS Calls
FROM TableA AS  A
   LEFT JOIN
   (
      SELECT COUNT(B.event) AS  Calls, B.ID
      FROM TableB AS B
      WHERE B.event LIKE 'call'
        AND `Time` >= 1360540800
        AND `Time` <= 1361232000
      GROUP BY B.ID

    ) B
      ON A.ID = B.ID 
WHERE A.State  LIKE 'SENT' OR A.State  LIKE 'SET'

I am trying to convert it in codeigniter style. I know Code Igniter's Active Record class does not natively support subqueries. But how could this be done. I have been trying the below code. So I made two different queries and combined them using union as below:

$query  = $this->db->query("select * from $subQuery2 UNION $subQuery1  as unionTable ");

Union is not the right way, can help me help me to convert it into codeigniter style.

You can use sub query way of codeigniter to do this for this purpose you will have to hack codeigniter. like this Go to system/database/DB_active_rec.php Remove public or protected keyword from these functions

public function _compile_select($select_override = FALSE)
public function _reset_select()

Now subquery writing in available And now here is your query with active record

$data   =   array(
                'COUNT(B.event) AS  Calls',
                'B.ID'
            );

$this->db
        ->select($data)
        ->from('TableB AS B')
        ->like('B.event','call','both')
        ->where('`Time` >=',1360540800)
        ->where('`Time` <=',1361232000)
        ->group_by('B.ID');

$subQuery = $this->db->_compile_select();
$this->db->_reset_select();
unset($data);
$data   =   array(
                'A.ID',
                'A.fName',
                'A.lName',
                'COALESCE (B.calls, 0) AS Calls'
            );
$this->db
        ->select($data)
        ->from('TableA AS A')
        ->join("$subquery")
        ->like('A.State','SENT','both')
        ->or_like('A.State','SET','both');

I have put both in the likes you can do according to your requirements. Note : While using sub queries you must use