Active Records Subquery

[eluser]ibnclaudius[/eluser]
Is there a better way to do a subquery with Active Records? Here's my query:

Code:

return $this->db->select('a.id, a.name, a.url_segment, a.categories_id, (SELECT image FROM supplements WHERE subcategories_id = a.id ORDER BY RAND() LIMIT 1) AS supplements_image')
                ->from('subcategories AS a')
                ->join('supplements AS b', 'b.subcategories_id = a.id', 'LEFT')
                ->join('reviews AS c', 'c.supplements_id = b.id', 'LEFT')
                ->order_by('a.categories_id', 'ASC')
                ->order_by('COUNT(c.id)', 'DESC')
                ->group_by('a.id')
                ->get()
                ->result_array();

[eluser]Aken[/eluser]
No, there's not. Subqueries and active record aren't designed to get along.

[eluser]ibnclaudius[/eluser]
Thanks for the answer!

[eluser]Otemu[/eluser]
You could use a library such as this one https://github.com/EllisLab/CodeIgniter/wiki/Subqueries