How-To: Create a Leaderboard

One of the problems with this is that you’ve used a direct SQL query (without caching; without a LIMIT) that won’t scale. Let’s pretend I run the above on a site with 25,000 user accounts. On most hosts, this will just crash the page ??

This scalability and problems over getting data out the usermeta table (where the points are scored) is why a scoreboard didn’t make it into a recent release yet ??

You’ve also got some checks in it for bbPress roles and ordering by a column “konb_user_level” (I hope your wp_user table doesn’t have non-standard columns!).

Hi Paul,

You’re right, there’s no pagination or anything of the sort to prevent huge queries.

My database prefix is konb_ instead of wp_, all tables are standard out of the box and plugins. Yes, I do have some bbPress fields in it, but this is only an example. Should easily adapt to vanilla WP.

This might work for what I need in the short term. I only need the username, avatar, and points columns, but I wonder, as you have it set up it sorts by when the user registered. How would I have the table sort by points instead, descending in value, in the traditional leaderboard fashion?

Paul is right in that this query is less than optimal. The query would have to be updated to include taxonomies.

Let me see if I can improve this to WP_QUERY instead, and use the functionality that WordPress provides.

Thanks Mike! A leaderboard is one of the last pieces I need for a project I’m launching at the end of the month. Anything you could offer would be greatly appreciated!

FOR EVERYONE READING: This particular technique has not been tested by me, and so it’s not recommend. If you break your code or your server, it’s your problem. ??

^^^^^ What Paul says! ^^^^^^ Use at own risk. I use the leaderboard on a members-only page and we have under 100 members, so I’m comfortable with the current risks. I will see if I can get a working query in place that has some limitations and pagination to prevent the system bogging down.

Here is the code for a bit more solid query that should work on all WP sites that have Achievements installed. This query now features pagination and orders by total Karma points in descending order.

<?php
/*
Template Name: Roster
*/
	get_header();

	global $wpdb, $current_date;

    $paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
    $post_per_page = intval(get_query_var('posts_per_page'));
    $offset = ($paged - 1)*$post_per_page;
	$listUsersID = $wpdb->get_col
	(
		$wpdb->prepare
		(
			'SELECT SQL_CALC_FOUND_ROWS person.*
				,nick.meta_value
				,SUM(karma.meta_value)
			FROM konb_users AS person
			LEFT JOIN konb_usermeta as nick
				ON person.id = nick.user_id
				AND nick.meta_key = "nickname"
			LEFT JOIN konb_usermeta as karma
				ON person.id = karma.user_id
				AND karma.meta_key = "konb__dpa_points"
			GROUP BY nick.meta_value
				,person.ID
			ORDER BY karma.meta_key DESC
			LIMIT ' . $offset . ', ' . $post_per_page . ';'
		)
	);
	$sql_posts_total = $wpdb->get_var( "SELECT FOUND_ROWS();" );
    $max_num_pages = ceil($sql_posts_total / $post_per_page);
?>

<table id="roster">
	<tr>
		<th>Avatar</th>
		<th>Name</th>
		<th>Rank</th>
<?php
	if (is_user_logged_in())
	{
?>
		<th>Advancement</th>
		<th>Email</th>
<?php
	}
?>
		<th>Member Since</th>
	</tr>
<?php
foreach ( $listUsersID as $userid )
{
	$user = get_userdata( $userid );
?>
	<tr>
		<td><?php echo get_avatar($userid, $size = '33'); ?></td>
		<td><? echo $user->nickname; ?></td>
		<td><?
if ( !empty( $user->roles ) && is_array( $user->roles ) )
{
	foreach ( $user->roles as $role )
	{
		if (strpos($role, "bbp_") === false)
		{
			echo ucfirst(strtolower($role));
		}
	}
}
		?></td>
		<td><?php echo dpa_get_user_points( $user->ID ); ?></td>
<?php
	if (is_user_logged_in())
	{
?>
		<td><? echo $user->user_email; ?></td>
<?php
	}
?>
		<td><? echo $user->user_registered; ?></td>
	</tr>
<?php
}
?>
</table>
<div class="navigation">
    <div class="previous panel"><?php previous_posts_link('? Previous Page',$max_num_pages) ?></div>
    <div class="next panel"><?php next_posts_link('Next Page ?',$max_num_pages) ?></div>
</div>
<?php
	comments_template( '', true );
	get_sidebar();
	get_footer();

The only reason I haven’t used WP_USER_QUERY to do this, is because I can’t for the life of me figure out how to do aggregate functions in WP_USER_QUERY. If someone knows the answer, please share. ??

Pagination works great but for some reason its not sorting by karma value. Looking closer at the sort order, I can’t really tell by what value it is sorting, as there doesn’t appear to be any pattern.

Also, how much trouble would it be to add a numerical rank as a column for each user, so a person could see, “hey, I’m number 43 on the leaderboard right now”?

Doh! When I tested I guess it was coincidence that it worked for me. ?? I also added the rank as the first column. I don’t have this in the query, as it is something very easily calculated on the front-end:

<?php
/*
Template Name: Roster
*/
	get_header();

	global $wpdb, $current_date;

    $paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
    $post_per_page = intval(get_query_var('posts_per_page'));
    $offset = ($paged - 1)*$post_per_page;
	$listUsersID = $wpdb->get_col
	(
		$wpdb->prepare
		(
			'SELECT SQL_CALC_FOUND_ROWS person.*
				,nick.meta_value
				,SUM(karma.meta_value) AS total_karma
			FROM konb_users AS person
			LEFT JOIN konb_usermeta as nick
				ON person.id = nick.user_id
				AND nick.meta_key = "nickname"
			LEFT JOIN konb_usermeta as karma
				ON person.id = karma.user_id
				AND karma.meta_key = "konb__dpa_points"
			GROUP BY nick.meta_value
				,person.ID
			ORDER BY total_karma DESC
			LIMIT ' . $offset . ', ' . $post_per_page . ';'
		)
	);
	$sql_posts_total = $wpdb->get_var( "SELECT FOUND_ROWS();" );
    $max_num_pages = ceil($sql_posts_total / $post_per_page);
?>

<table id="roster">
	<tr>
		<th>Rank</th>
		<th>Avatar</th>
		<th>Name</th>
		<th>Rank</th>
<?php
	if (is_user_logged_in())
	{
?>
		<th>Advancement</th>
		<th>Email</th>
<?php
	}
?>
		<th>Member Since</th>
	</tr>
<?php
$counter = 1;
foreach ( $listUsersID as $userid )
{
	$user = get_userdata( $userid );
?>
	<tr>
		<td><?php echo $counter++; ?></td>
		<td><?php echo get_avatar($userid, $size = '33'); ?></td>
		<td><? echo $user->nickname; ?></td>
		<td><?
if ( !empty( $user->roles ) && is_array( $user->roles ) )
{
	foreach ( $user->roles as $role )
	{
		if (strpos($role, "bbp_") === false)
		{
			echo ucfirst(strtolower($role));
		}
	}
}
		?></td>
		<td><?php echo dpa_get_user_points( $user->ID ); ?></td>
<?php
	if (is_user_logged_in())
	{
?>
		<td><? echo $user->user_email; ?></td>
<?php
	}
?>
		<td><? echo $user->user_registered; ?></td>
	</tr>
<?php
}
?>
</table>
<div class="navigation">
    <div class="previous panel"><?php previous_posts_link('&laquo; Previous Page',$max_num_pages) ?></div>
    <div class="next panel"><?php next_posts_link('Next Page &raquo;',$max_num_pages) ?></div>
</div>
<?php
	comments_template( '', true );
	get_sidebar();
	get_footer();

Ah! Beautiful. Everything appears to be sorting properly. I did notice that the rank column resets at each new page advance, as its numbering only that list of users for the given page.

Excellent work Mike!

Ah, no problem. Replace $counter = 1; with $counter = $offset + 1;

Glad its working for you. Please do keep in mind though that this is unsupported functionality and may not scale well in some cases. ??

Just saw the leaderboard you implemented!!!! Hats off to you sir! Love it ??

https://challengebeta.mypcls.org/leaderboard/

Thanks!

Got me to thinking, I wonder if there’s a good way to pull the position number for the current logged in user separately outside of the table. I’d like to put it above the table to save users the trouble from scrolling through a long table to find their name, since I expect we’ll have over a thousand participants this summer.

Ah! Yes, that’s completely doable. I’ll add that to the list to do. Good idea, something I’ll want as well down the road.