WordPress.org

Support

Support » Plugins and Hacks » Achievements for WordPress » [Resolved] How-To: Create a Leaderboard

[Resolved] How-To: Create a Leaderboard

  • Creating a leader-board is actually fairly easy. Granted, it’s no short code, but using a custom post template, you can do it. I have created a leaderboard using the following code:

    <?php
    /*
    Template Name: Roster
    */
    	get_header();
    ?>
    
    <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
    	$listUsersID = $wpdb->get_col(
    		$wpdb->prepare(
    			"SELECT $wpdb->users.ID FROM $wpdb->users ORDER BY %s ASC",
    			"konb_user_level, nickname"
    		)
    	);
    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>
    <?php
    	comments_template( '', true );
    	get_sidebar();
    	get_footer();

    This code is saved in the root of my child theme as roster.php. You will be able to use it by creating a normal page in WordPress, then selecting the Roster template. Feel free to modify and customize as needed. I’d show a working example, except our roster page requires the user to be logged in.

    Using a custom page template you can fully customize the roster to your liking, where as with a short code, you’d be very dependent on how the layout was done in the plugin.

    ~Mike

    http://wordpress.org/extend/plugins/achievements/

Viewing 15 replies - 1 through 15 (of 76 total)
  • Plugin Author Paul Gibbs

    @djpaul

    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!

    Plugin Author Paul Gibbs

    @djpaul

    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 ๐Ÿ™‚

    http://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.

Viewing 15 replies - 1 through 15 (of 76 total)
  • The topic ‘[Resolved] How-To: Create a Leaderboard’ is closed to new replies.
Skip to toolbar