How To Get Row Number Order

Yii 1.1 General Discussion
1

Hi,

i have a table with players with id,rank,points.

after each match between players i need to update points and rank.

one other important thing is that rank1 vs rank2 gets different points than for example rank3 vs rank10

so if i need to add a match in the past (i missed one) i need to get through all the matches after that one and recalculate points and rank until present day to get a proper leaderboard

what is the best approach to do this?

Now: How to get row number order ?

i found this solution and i don’t know to use it with Yii

this sql command works well in phpMyAdmin:


set @row_num = 0; SELECT @row_num := @row_num + 1 as row_number,points FROM `players` ORDER BY points DESC;
2

This solution works only in MySQL. In other RDBMs like PostgreSQL, there are window functions available that can do that.

You could either write those numbers in the players table, some other table (related by HAS_ONE) or calculate it by iterating over fetched data in PHP if there are not too many records.

3

This is the problem, there will be many records and i need to rank players by their points before add a match between 2 players

4

is this loop ok when the record grows ?

any other solutions ?




$criteria = new CDBCriteria;

$criteria->select = 'id, points, rank';

$criteria->order = 'points DESC';

$data = $this->model()->findAll($criteria);


$i = 0;

foreach($data as $row)

{

	$i++;

	$rank_diff = $row->rank - $i;

	$this->model()->updateByPk($row->id, array('rank'=>$i, 'rank_diff'=>$rank_diff));

}