Kohana ORM group_by ORDER

Question

currently i am trying to build another Query into the Kohana ORM. This is my SQL Statement (works fine for me)

SELECT needle, COUNT(needle) FROM my.haystacks GROUP BY needle ORDER BY COUNT(needle) DESC;

In Kohana it's the following snippet:

$needleCount = ORM::factory('haystack')->select(array('needle'))->group_by('needle')->order_by('COUNT("needle")', 'DESC')->find_all()->as_array();

Looks good? Following exception returns:

Unknown column 'COUNT("needle")'

[ SELECT `needle` AS `needle` FROM `haystacks` AS `haystack` GROUP BY `needle` ORDER BY `COUNT("needle")` DESC ]

So, Kohana is trying to SELECT the COUNT("needle") as a column. Any ideas to fix this?

Answer 1

Kohana have some nice "DB expression" (Database expressions can be used to add unescaped SQL fragments to a Database_Query_Builder object. > http://kohanaframework.org/3.0/guide/api/Database_Expression).

My query required "DB::expr('COUNT("needle")'". So replace the query with the following one:

$needleCount = ORM::factory('haystack')->select(array('needle'))->group_by('needle')->order_by(DB::expr('COUNT("needle")'), 'DESC')->find_all()->as_array();