Case and conditional statements in Active Record

[eluser]CroNiX[/eluser]
You can do a subquery, just not totally using active record. Example using a separate select:

You just have to manually escape anything that is user input, and use FALSE as the 2nd parameter to db:elect() so that AR won't escape the identifiers which will mess up the query.

Code:

$this->db
  ->select('id, field1, field2')
  ->select('(SELECT COUNT(id) FROM tableY WHERE tableY.' . $this->db->escape('field1') . ' = tableX.id) as image_count', FALSE)
  ->get('tableX');

You can do the same with any query, like your CASE statements. It's just more complex queries can't use the built in "protect identifiers" because it's too complex for CI to figure out and escape them properly. They'd have to extensively extend the AR class to be able to work in all situations, which would be quite bloated and complex IMO.