CakePHP Query with Result Rank -

i'm trying "rank" of record in cakephp application , having trouble doing on database end. know can use example item rank sql query...

set @rownum := 0; select rank, id (         select @rownum := @rownum + 1 rank, id         item_table order some_count desc         ) result id=xxxxxxxx 

i tried use model query function perform task, cakephp throws errors when try use multi-statement query.

$rank = $this->item->query("set @rownum := 0; select rank, id (select @rownum := @rownum + 1 rank, id item_table order some_count desc ) result id=xxxxxxxx"); 

this seems more efficient approach doing in cakephp, getting list of records ordered "some_count" , looping through them until have result. table grow millions of records, become strenuous on application server.

$itemlist = $this->item->find(         'list',         array(             'conditions' => array(                 'item.hidden' => 0             ),             'order' => array(                 'item.some_count desc',                 'item.created desc'             ),             'fields' => array(                 'item.id'             )         )     );      $rank;     $i = 1;     foreach($itemlist $j){         if($j == $curritem['item']['id']){             $rank = $i;             break;         }         $i++;     }     $this->set('itemrank', $rank); 

is there way efficiently "rank" of record in cakephp? i'm looking other "brute force" solution, have now. item's rank change based on how performs against other records in table, it's not actively store in table need calculated every second, or fraction of second.

may use this.

        select id, some_count, find_in_set(                                      some_count                                 ,  (select  group_concat(                                     distinct some_count                                     order some_count  desc                                  )                                 item_table)                            ) rank          item_table; 

with cakephp query() function.

see mysql rank trip more info. :)