-1

Please help me convert this SQL to CodeIgniter ActiveRecord without using the following:

$this->db->query('SELECT * FROM (SELECT * FROM inbox ORDER BY created_at DESC) AS query GROUP BY id');
imlouisrussell
  • 936
  • 11
  • 30
rails_noob
  • 311
  • 2
  • 5
  • 14

1 Answers1

0

CI Active Record does not support subqueries natively. But you can include a subquery library like this one and use it. I've not used this library before, so this is not tested. But should be enough to get you started.

$this->db->select()->from('query')->group_by('id');
$sub = $this->subquery->start_subquery('select');
$sub->select()->from('index')->order_by('created_at', 'DESC');
$this->subquery->end_subquery('query');
swatkins
  • 13,530
  • 4
  • 46
  • 78
  • If the question asker is using CodeIgniter 2.1 then this library is not going to work. The library utilizes the `_compile_select()` and `get_compiled_select()` methods, both of which are unavailable in CI 2.1. `compile_select()` is protected and `get_compiled_select()` didn't make it into 2.1. – birderic Dec 19 '11 at 16:13
  • @birderic - Hmm, I was unaware of this. Then the solution may be in this answer: http://stackoverflow.com/a/6047554/844726 – swatkins Dec 19 '11 at 16:27
  • I may be wrong, but I tried unsuccessfully to compile the inner query using methods similar to the ones described here: http://heybigname.com/2009/09/18/using-code-igniters-active-record-class-to-create-subqueries/. As far as I can tell there is currently no way to compile a query without actually running it. – birderic Dec 19 '11 at 16:39