CodeIgniter Active Record select with 2 or more tables

Question

I have this sql statement setup in my old code and want to move it over to active record, the problem that I'm phased with is how do I do a

 SELECT news_items.id FROM 

 (SELECT news_items.* FROM news_items 
 JOIN feeds ON feeds.id=news_items.feed 
 LEFT JOIN news_item_country ON news_item_country.id=news_items.country 
 WHERE news_items.id=30758 
 ORDER BY date_item DESC ) 

 AS news_items

 GROUP BY news_items.id 
 ORDER BY date_item DESC

Trying to do this with Active Record, first I created the select that goes inside the select at least that is what I think needs to be done.

$news_items = $this->db->select('default_news_items.*')
              ->from('default_news_items ')
              ->join('default_feeds','default_feeds.id=default_news_items.feed')
->join('default_news_item_country','default_news_item_country.id=default_news_items.country','left')
->join('default_news_item_options','default_news_item_options.id=default_news_items.options','left')
              ->where($where)                        
              ->order_by('date_item DESC')
              ->limit($limit)
              ->get();               

Next I create the actual select and reference the other select inside the from

    if ($query = $this->db->select("
                            news_items.id,
                    ->from($news_items)
                    ->group_by('news_items.id')
                    ->get()) {

It did not work, so I'm asking if someone have experiences with this and how to do it?

Answer 1

You can't do a subquery directly in native active record syntax. Check out this answer for a solution:

subquery in codeigniter active record

(The author wrote a library for active record subqueries http://labs.nticompassinc.com/CodeIgniter-Subqueries/ )

Without this or similar technique you need to keep your current select code at least for the subquery ($news_items).

Answer 2

Take a look at this answer of mine this might help how you can achieve it.

Using Mysql WHERE IN clause in codeigniter