Newbie Q - controller/view code for mixed query

[eluser]osci[/eluser]
Native yes
Undocumented yes
unsupported yes

Check this

Code:

$this->db->from('comments');
$this->db->select('task_id, count(*) AS comment_count');
$this->db->group_by('task_id');
// ------------
// "magic" here
$subquery = $this->db->_compile_select();
$this->db->_reset_select();
// ------------
$this->db->from('tasks');
$this->db->select('tasks.*, comments.*, COALESCE(mydata.comment_count, 0)');
$this->db->join('comments','tasks.id = comments.task_id','LEFT OUTER');
$this->db->join("($subquery) as mydata",'mydata.task_id = tasks.id','LEFT OUTER');
$this->db->order_by('tasks.id','ASC')->order_by('comments.id','ASC');
$this->db->get();

You compile the select, thus produce the statement, store it and use it later

I haven't set up a db so it produces error, but the error gives me the above sql statement.

Drawback is _magic is unsupported.

EDIT:
Actually the above code produces error. I hadn't noticed the returned sql string.
To get correct sql statement you need to do

Code:

$this->db->select('tasks.*, comments.*, COALESCE(mydata.comment_count, 0)',FALSE);