Convert complex JOIN query in codeigniter

Question

I'm converting my existing website to CI, and I have been trying for several days to convert this query to CI-friendly code:

$result = mysql_query("
    SELECT t1.mnumber, t1.mcontent, t1.mcontact
    FROM sms t1
    JOIN (
        SELECT mContent,mcontact, mnumber, MAX(mID) mID
        FROM sms
        GROUP BY mContact
    ) t2 ON t1.mcontact = t2.mcontact AND t1.mid = t2.mid
    GROUP BY t1.mContact
    ORDER BY t1.mid DESC
"); 

But no matter what I try, I can't get the correct result on CI.

I hope you guys can help me out here!

---

The closest to a result that i did get, was when i used the subquery hack. However, out of frustration i deleted the block of code and kept trying.

I decided to use a flat query, like the one posted above. This almost gives me results.

$query = $this->db->query("SELECT t1.mnumber, t1.mcontent, t1.mcontact FROM sms t1
JOIN (SELECT mContent,mcontact, mnumber, MAX(mID) mID FROM sms GROUP BY mContact) t2
ON t1.mcontact = t2.mcontact AND t1.mid = t2.mid GROUP BY t1.mContact ORDER BY t1.mid DESC"); 


$contacts = array();

//Add data to our array
foreach($query->result() as $row){
     echo $row->mNumber;
}

return $contacts;

However, in my view i get the notice "Message: Undefined property: stdClass::$mNumber"

So still no results, plus i prefer the CI query method.

Answer 1

You can use it in codeigniter like this

$query = "  SELECT t1.mnumber, t1.mcontent, t1.mcontact
            FROM sms t1
            JOIN (
                SELECT mContent,mcontact, mnumber, MAX(mID) mID
                FROM sms
                GROUP BY mContact
            ) t2 ON t1.mcontact = t2.mcontact AND t1.mid = t2.mid
            GROUP BY t1.mContact
            ORDER BY t1.mid DESC"; 
$result =   $this->db->query($query);           
return $result->result();

2nd Method

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

$select =   array(
                'mContent',
                'mcontact',
                'mnumber',
                'MAX(mID) mID'
            );
$this->db->select($select);
$this->db->from('sms');
$this->db->group_by('mContact');
$subquery = $this->db->_compile_select(); // get the query string

$this->db->_reset_select(); // reset so it can newly form the query

unset($select);
$select =   array(
                't1.mnumber',
                't1.mcontent',
                't1.mcontact'
            );
$this->db->select($select);
$this->db->join('',"($subquery)");
$this->db->from('sms t1');
$this->db->group_by('t1.mContact');
$this->db->order_by('t1.mid','DESC');
$result =   $this->db->get();
return $result->result();

And the thing is done. Cheers!!! Note : While using sub queries you must use

$this->db->from('myTable')

instead of

$this->db->get('myTable')

which runs the query.