Convert Sql query to sql CodeIgniter

convert sql query to sql codeigniter i done try to use this method How to convert sql query to codeigniter active records but not working for me… so i try to post in here

this my sql query

  $sql = "SELECT b.id, b.us_id, b.kredit, b.info,u.us_name, 
                 u.us_username, u.us_email, u.us_phone, b.cnt, b.amnt 
          FROM users u JOIN
              (SELECT id, us_id, kredit, info, COUNT(info) cnt, SUM(kredit) amnt 
               FROM balance_history 
               GROUP BY info HAVING cnt > 1
              ) AS b 
              ON u.us_id = b.us_id 
          WHERE b.kredit != '0' AND 
                b.info NOT LIKE '[PERBAIKAN]%' AND 
               (b.info LIKE 'Transfer saldo%' OR b.info LIKE 'Ket%') 
          ORDER BY b.id ASC";

thanks before…

Answer

If you aren’t loaded database globally, you can load it by calling $this->load->database(); The following query may provide the same db call.

$this->db->select('id, us_id, kredit, info, COUNT(info) cnt, SUM(kredit) amnt')
        ->from('balance_history')
        ->group_by('info')
        ->having('cnt > 1');

        $subquery = $this->db->get_compiled_select();
        $query  =   $this->db
                    ->select('b.id, b.us_id, b.kredit, b.info,u.us_name,u.us_username, u.us_email, u.us_phone, b.cnt, b.amnt')
                    ->from('users u')
                    ->join('('.$subquery.') b','u.us_id = b.us_id')
                    ->where('b.kredit !=','0', true)
                    ->not_like('b.info', '[PERBAIKAN]', 'after', false, true)
                    ->where('(b.info LIKE "Transfer saldo%" OR b.info LIKE "Ket%")')
                    ->get();