codeigniter active record nested query

Question

I'm having a problem converting below MySQL code to a Codeigniter active record query.

SELECT sss.*, c.country_name, c.country_code 
  FROM ( SELECT gr.* 
           FROM (`be_goldrate` as `gr`) 
          WHERE `gr`.`country_id` != 0 
            AND `gr`.`rate_type` = 'calculated' 
           ORDER BY gr.date DESC ) as sss 
   LEFT JOIN `be_countries` as `c` 
     ON `c`.`country_id`=`sss`.`country_id`  
   GROUP BY `sss`.`country_id`

I've tried as suggested here but without success.

you might want to make an sql view of: SELECT gr.* FROM (be_goldrate as gr) WHERE gr.country_id != 0 AND gr.rate_type = 'calculated' ORDER BY gr.date DESC ) ? to increase performance? — Joel Harkes
CodeIgniter doesn't support subqueries natively. If you want, you can try my subquery library, it should let you do what you want. github.com/NTICompass/CodeIgniter-Subqueries — Rocket Hazmat

itsme itsme · Accepted Answer · 2013-03-29T16:31:55

you just need to check the Codeigniter Documentation

     $this->db->select('sss.*, c.country_name, c.country_code ');
       $this->db->from('(SELECT gr.* 
       FROM (`be_goldrate` as `gr`) 
      WHERE `gr`.`country_id` != 0 
        AND `gr`.`rate_type` = "calculated" 
       ORDER BY gr.date DESC) as sss');
       $this->db->join('be_countries as c','c.country_id = sss.country_id','left');

       $this->db->group_by('sss.country_id');

please report if any error

codeigniter active record nested query

1 Answers