StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCakePHP Query with Result Rank
text
Body
copied!<p>I'm trying to get the "rank" of a record in my CakePHP application and I am having trouble doing it on the database end. I know I can use an example like this to get the item rank with a SQL query...</p> <pre><code>SET @rownum := 0; SELECT rank, id FROM ( SELECT @rownum := @rownum + 1 AS rank, id FROM item_table ORDER BY some_count DESC ) as result WHERE id=xxxxxxxx </code></pre> <p>I originally tried to use the model query function to perform this task, but CakePHP throws errors when I try to use a multi-statement query.</p> <pre><code>$rank = $this->Item->query("SET @rownum := 0; SELECT rank, id FROM (SELECT @rownum := @rownum + 1 AS rank, id FROM item_table ORDER BY some_count DESC ) as result WHERE id=xxxxxxxx"); </code></pre> <p>This seems to be a more efficient approach than what I am currently doing in CakePHP, which is getting a list of all the records ordered by "some_count" and then looping through them until I have the result. My table could grow to millions of records, so this would become very strenuous very quickly on the application server.</p> <pre><code>$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 as $j){ if($j == $currItem['Item']['id']){ $rank = $i; break; } $i++; } $this->set('itemRank', $rank); </code></pre> <p>Is there a way to efficiently get the "rank" of a record in CakePHP? I'm looking for something other than a "brute force" solution, which is what I have now. The item's rank will change very frequently based on how it performs against the other records in the table, so it's not something I could actively store in the table as it would probably need to be calculated every second, or even fraction of a second.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload