Thank Paul for the additional references. This allowed me to significantly optimize the query and wrap it neatly in a function.
For those using this functionality, please add the following function to your functions.php in your child theme:
/**
* Returns either the ranking for the current user (single row result) or for all users (limited to the current page, if not overriden).
*
*
* @param bool $show_current_user Optional. If true will get the ranking for the current user only. Default is false.
* @param int $offset Optional. If set, will start the query from a given offset record. Default is to use normal pagination offset.
* @param int $posts_per_page Optional. If set, will change the number of records returned per page. Default is WordPress default value.
*
* @return array. Two-dimensional array is returned, array["restults"] holds search results, while array["total_number_of_pages"] holds the max number of pages which can be used for pagination links.
* @since Achievements (3.2.2)
* @author Mike Bronner <[email protected]>
*/
function dpa_get_leaderboard_rankings($show_current_user = false, $offset = null, $posts_per_page = null)
{
global $wpdb;
if ($show_current_user)
{
get_currentuserinfo();
}
if (null === $posts_per_page)
{
$posts_per_page = intval(get_query_var('posts_per_page'));
}
$db_prefix = $wpdb->base_prefix;
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$posts_per_page = intval(get_query_var('posts_per_page'));
if (null === $offset)
{
$offset = ($paged - 1) * $posts_per_page;
}
$leaderboard_query = "SELECT SQL_CALC_FOUND_ROWS
person.*
,nick.meta_value AS nickname
,SUM(karma.meta_value) AS total_karma
,FIND_IN_SET(karma.meta_value, (SELECT GROUP_CONCAT(DISTINCT ranking.meta_value ORDER BY ranking.meta_value DESC) FROM konb_usermeta AS ranking WHERE ranking.meta_key = 'konb__dpa_points')) as rank
FROM " . $db_prefix . "users AS person
LEFT JOIN " . $db_prefix . "usermeta as nick
ON person.id = nick.user_id
AND nick.meta_key = 'nickname'
LEFT JOIN " . $db_prefix . "usermeta as karma
ON person.id = karma.user_id
AND karma.meta_key = '" . $db_prefix . "_dpa_points'
WHERE 1 = 1";
if ($show_current_user)
{
$leaderboard_query .= "
AND ID = " . $current_user->ID;
}
$leaderboard_query .= "
GROUP BY nick.meta_value
,person.ID
ORDER BY total_karma DESC
,person.user_registered ASC";
if ($show_current_user)
{
$leaderboard_query .= "
LIMIT 0, 1;";
}
else
{
$leaderboard_query .= "
LIMIT " . $offset . ", " . $posts_per_page . ";";
}
$leaderboard["results"] = $wpdb->get_results($wpdb->prepare($leaderboard_query, null));
$sql_posts_total = $wpdb->get_var( "SELECT FOUND_ROWS();" );
$leaderboard["total_number_of_pages"] = ceil($sql_posts_total / $posts_per_page);
return $leaderboard;
}
Also, consider the following example to implement this functionality, again using a page template as described above:
<?php
/*
Template Name: Roster
*/
get_header();
$listCurrentUser = dpa_get_leaderboard_rankings($current_user->ID);
$listAllUsers = dpa_get_leaderboard_rankings();
$html = '
<table id="roster">
<tr>
<th>Rank</th>
<th>Avatar</th>
<th>Name</th>
<th>Rank</th>';
if (is_user_logged_in())
{
$html .= '
<th>Advancement</th>
<th>Email</th>';
}
$html .= '
<th>Member Since</th>
</tr>';
//put current user at top of each page
foreach ($listCurrentUser["results"] as $row)
{
if (($row->Rank > 10) || ($offset > 0))
{
$this_user = get_userdata($row->ID);
$html .= '
<tr class="current_user_top">
<td>' . $row->rank . '</td>
<td>' . get_avatar($row->ID, 39) . '</td>
<td>' . $this_user->nickname . '</td>
<td>';
if ( !empty( $this_user->roles ) && is_array( $this_user->roles ) )
{
foreach ( $this_user->roles as $role )
{
if (strpos($role, "bbp_") === false)
{
$html .= ucfirst(strtolower($role));
}
}
}
$html .= '</td>
<td>' . dpa_get_user_points( $row->ID ) . '</td>';
if (is_user_logged_in())
{
$html .= '
<td>' . $this_user->user_email . '</td>';
}
$html .= '
<td>' . $this_user->user_registered . '</td>
</tr>';
}
}
//get the current page of the leaderboard
foreach ( $listAllUsers["results"] as $row )
{
$this_user = get_userdata( $row->ID );
if ($row->ID == $current_user->ID)
{
$html .= '
<tr class="current_user">';
}
else
{
$html .= '
<tr>';
}
$html .= '
<td>' . $row->rank . '</td>
<td>' . get_avatar($row->ID, 39) . '</td>
<td>' . $this_user->nickname . '</td>
<td>';
if ( !empty( $this_user->roles ) && is_array( $this_user->roles ) )
{
foreach ( $this_user->roles as $role )
{
if (strpos($role, "bbp_") === false)
{
$html .= ucfirst(strtolower($role));
}
}
}
$html .= '</td>
<td>' . dpa_get_user_points( $this_user->ID ) . '</td>';
if (is_user_logged_in())
{
$html .= '
<td>' . $this_user->user_email . '</td>';
}
$html .= '
<td>' . $this_user->user_registered . '</td>
</tr>';
}
$html .= '
</table>
<div class="navigation">
<div class="previous panel">' . previous_posts_link('« Previous Page', $listAllUsers["total_number_of_pages"]) . '</div>
<div class="next panel">' . next_posts_link('Next Page »', $listAllUsers["total_number_of_pages"]) . '</div>
</div>';
echo $html;
comments_template( '', true );
get_sidebar();
get_footer();
Please update your code if you have been using my work-around above. Let us know how it works, and please post screenshots. Maybe we can convince Paul to incorporate the function. ??
