User ranking

Yii 1.1 General Discussion
1

Hi,

I have 2 tables: users (id, name) and scores (id, user_id, point).

Users table stores user information and scores table stores points attached to the user on user_id. One user can have more points. I’d like to make a 1) high score rank list and 2) method which returns a placement to a user.

I have some difficulties with 2). I can make a list of high scores via the following query:


SELECT u.name, u.id, SUM(s.score) as scoresSum FROM tbl_users as u INNER JOIN tbl_scores as s ON s.user_id=u.id GROUP BY u.name ORDER BY scoresSum DESC

This one gives the correct list of user ranking but if I would like to show the user’s current ranking on the user profile page it doesn’t work. I found a formula to compute the ranking for a user but the GROUP BY messes up the query somehow and gives back a wrong result.

So I added a new column to the users table: ‘scoresum’ which stores the sum of the scores of a user.

But here I have another error. Maybe it isn’t allowed to put such a queries in a single method:

The queries:

SET @row = 0;

SELECT rank, id FROM (SELECT @row := @row + 1 AS rank, name, id, scoresum FROM tbl_users ORDER BY scoresum DESC) as q WHERE id=’:id’;

The yii methods:


$sql = "SET @row = 0; SELECT rank, id FROM (SELECT @row := @row + 1 AS rank, name, id, scoresum FROM tbl_users ORDER BY scoresum DESC) as q WHERE id=':id';";

$command = $connection->createCommand($sql);

$command->bindValue(":id", $this->id, PDO::PARAM_INT);

$dataReader = $command->query();

foreach($dataReader as $row) { 

	var_dump($row);

}

I get this error: SQLSTATE[HY000]: General error

Could someone give me hints how I can solve this ‘getting ranking to a user’ problem?

Thank you in advance!

2

Why not use relation BelongTo in your user model?

3

Would you please say it in more details?

I have only a has_many relation in users model like this:


'scores' => array(self::HAS_MANY, 'Scores', 'user_id')
4

You can add a stat relations for retrive this value:




'scores' => array(self::STAT, 'Scores', 'user_id', 'select'=>'SUM(score)'),

and then:




$user= User::model()->with('scores')->toghether();

Take a look at the giude for detail.

5

Can you explain what ‘->together()’ actually does? I tried reading the manual for that but I can’t understand it. Thanks.

6

Thanks for your answer, it is a good point!

But it won’t resolves the base question: how can I get the proper ranking for a user

7

No idea so far…

Has anybody found a solution for getting rank for a user?