Order table numerically and get the list number

Question

In my WordPress site, I have a custom user metadata reputation. The value is numeric and is used similarly to how points/reputations are used here at Stackoverflow.

I want to get the number of which the user ranks to others.

Browsing the database, all I need to do is to list the table in a descending order and I can see who is ranked at 1 (has most points) and so on...

Is there any way I can show the rank number to the user? I am guessing the best way is to order the table in descending order, and somehow get the number of where the user is placed in that list?

I want to GET the user's rank number. Similar to the function below that get the user's total comment count. I need user_id to be the argument:

function get_user_comments_count( $uid ){
    global $wpdb;
    $where = 'WHERE comment_approved = 1 AND user_id = ' . $uid ;
    $comment_count = $wpdb->get_var("SELECT COUNT( * ) AS total 
                FROM {$wpdb->comments}
                {$where}");
    return $comment_count;
}

Answer 1

You can use join for your user table and meta data and order by the meta_value stored in column,

SELECT u.* 
FROM `wp_users` u
JOIN `wp_usermeta` um ON(u.ID = um.`user_id`)
WHERE um.`meta_key` = 'reputation'
ORDER BY um.`meta_value` * 1 DESC

Using * 1 in order is a simple casting trick to integer and desc will list the user by their reputation score so the user with higher reputation will be listed first and so on

---

global $wpdb;
$query="    SELECT u.*,um.`meta_value` as `rank` 
    FROM $wpdb->users u
    JOIN $wpdb->usermeta um ON(u.ID = um.`user_id`)
    WHERE um.`meta_key` = 'reputation'
    ORDER BY um.`meta_value` * 1 DESC";
$results = $wpdb->get_results($query, OBJECT );

Loop through your results

foreach($results as $r){
echo $r->rank; // rank of user
}

---

Edit from comments

here is your function

function getReputationByUser($user_id){
    if(!is_numeric($user_id)){
    return false;
    }
    global $wpdb;
    $query="    SELECT um.`meta_value` as `rank`
        FROM  $wpdb->usermeta um
        WHERE um.`meta_key` = 'reputation'
        AND um.user_id = '".$user_id."'
        ";
    $result  = $wpdb->get_row($query);
    if(!empty($result)){
    return $result->rank;
    }
    return false;
}

echo getReputationByUser(100);

---

Above code is for returning the reputation if you want the rank of user based on reputation you can get this by below query but i am not sure if you can use @variables of Mysql in WPDB class

SELECT t1.`rank`
FROM(
SELECT 
  um.user_id,
  @rownum:= @rownum + 1 `rank` 
FROM
  wp_usermeta um 
  CROSS JOIN (SELECT @rownum := 0) t 
WHERE um.`meta_key` = 'reputation' 
ORDER BY um.`meta_value` * 1 DESC 
) t1
WHERE t1.user_id = 100

function for above query

function getReputationByUser($user_id){
        if(!is_numeric($user_id)){
        return false;
        }
        global $wpdb;
        $query=" SELECT t1.`rank`
        FROM(
        SELECT
          um.user_id,
          @rownum:= @rownum + 1 `rank`
        FROM
          $wpdb->usermeta um
          CROSS JOIN (SELECT @rownum := 0) t
        WHERE um.`meta_key` = 'reputation'
        ORDER BY um.`meta_value` * 1 DESC
        ) t1
        WHERE t1.user_id = '".$user_id."'";
        $result  = $wpdb->get_row($query);
        if(!empty($result)){
        return $result->rank;
        }
        return false;
}

echo getReputationByUser(100);

---

Here is another way to find the rank for user using group_concat and find_in_set functions

SELECT user_id,FIND_IN_SET(
              user_id,(SELECT GROUP_CONCAT( user_id ORDER BY meta_value * 1 DESC)  
                      FROM `wp_usermeta` 
                      WHERE meta_key ='reputation')
                   ) AS rank
FROM wp_usermeta
WHERE meta_key ='reputation'
AND user_id = 100

function getReputationByUser($user_id){
        if(!is_numeric($user_id)){
        return false;
        }
        global $wpdb;
        $query="SELECT user_id,FIND_IN_SET(
                      user_id,(SELECT GROUP_CONCAT( user_id ORDER BY meta_value * 1 DESC)
                              FROM $wpdb->usermeta
                              WHERE meta_key ='reputation')
                           ) AS rank
        FROM $wpdb->usermeta
        WHERE meta_key ='reputation'
        AND user_id = '".$user_id."'
        ";
        $result  = $wpdb->get_row($query);
        if(!empty($result)){
        return $result->rank;
        }
        return false;
}

echo getReputationByUser(100);

---

• group_concat

According to docs The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.