Newbie Q - controller/view code for mixed query

[eluser]theprodigy[/eluser]
Just to add my $0.02.

Do I know how to write SQL? Yes.
Do I prefer to use AR? Yes.

Mainly for simple reasons. It allows much more flexibility since the SQL statement isn't compiled until you call the get() function.

One example:

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();

Notice osci put the from() before the select() in the "after magic" part? You can do this with AR.

Also,

Code:

foreach($tasks as $task)
{
    $this->db->or_where('task_id',$task->id);
}

is a little easier to write (and read), than something like:

Code:

$where = array();

foreach($tasks as $task)
{
    $where[] = 'task_id = ' . $task->id;
}

$where_clause = implode(' OR ', $where);

Does AR have it's limitations? Yes, but as osci showed, you can still do what you need to do (even if it is not the expected way to do it).

Don't get me wrong. I agree with the reasoning that you should know what you're doing before you start taking shortcuts, but I also believe that if you do know what you're doing, it's not bad to take shortcuts (as long as they still get the job done right).

For those that are battling against AR because it doesn't make sense (or just adds a wrapper), let me ask you this. Do you handcode all your javascript or do you use jQuery (or another js framework)? If you use a js framework, why? Why not just handcode all your javascript? It's just basically a wrapper anyway.