mikebronner

I see what the problem is now … the column where the points are stored is a string, therefor the query is ranking incorrectly. It thinks that 99 ranks higher than 101 because it looks at it as a string. I’m investigating how to do this right without bringing the query to its knees. ??

@agentbarnes: good to hear ?? When you get the page finalized, go ahead and attach a screenshot here. ?? Always good to see how others implement it.

Hi jrman, I will try to replicate the issue and get back to you on this. Can you post a link for me to look at? I will try to create 10 users or so modeled after yours and see what’s happening.

@strozzapr: how would you envision the widget looking? What fields should it have, rank, points, and name? Should it show just the top 10? Hmm, maybe two widgets: one that shows the users rank and score, and another that shows the top 10?

Let me know what you would like to see and I’ll see what I can do. ??

Ah yes, it sounds like you’re missing the function referenced in the error message. Two posts above the code you just copied, you see some more code. Be sure to copy/paste that into the functions.php file of your child-theme. Give that another shot and let me know.

OK, I’ve been thinking about this some more, and can see where some things may not work: let’s say I have various achievements set up that are dependant on other achievements.

Achievement B requires Achievement A to be awarded.
Achievement C requires a combination of Achievement A (again) and B to be awarded. However, since A was already awarded, this would reward the achievement too early, as I want them to do A again. I know this falls somewhat in the category of multiple awarding of the same achievement issue, but I’m wondering if this can be achieved with using events alone, or if the achievements need to be hierarchical by enabling that feature on the taxonomy as well?

Also, Paul: could you point me in the right direction as to where I need to register new events? I’m a little lost on that part. ??

Thanks!
~Mike

Can you provide a link to your page?

Tomas, was looking at Badge Fever, and even there to implement it in wordpress you need to do custom coding in your theme, or at the very least install another plugin.

Obviously you know what Achievements for WordPress is, and as a plugin it will evolve and get more features. I don’t think it’s quite right to troll here for users in this support forum as a developer on Badge Fever. This is a support forum, and we’re all trying to help each other out here. There are those that like Achievements for WordPress just fine and just want to tweak it here and there.

I have performed some scalability tests on the leaderboard function above, here are the results. Please post feedback if you have ideas, comments, concerns.

The entire function call above is tested, not just the SQL query. So, if you have around 20,000 users, it will take about 2.8 seconds to load each page.

1 User
======
Time to fetch one ranked row for current user with 100 attempts:
Average: 0.000323865413666
Min: 0.000309944152832
Max: 0.000519990921021
Total: 0.0323865413666
Time to fetch one page of all users with 100 attempts:
Average: 0.000194818973541
Min: 0.000191926956177
Max: 0.00019907951355
Total: 0.0194818973541

100 Users
=========
Time to fetch one ranked row for current user with 100 attempts:
Average: 0.000301289558411
Min: 0.000284910202026
Max: 0.00036883354187
Total: 0.0301289558411
Time to fetch one page of all users with 100 attempts:
Average: 0.000399761199951
Min: 0.000257968902588
Max: 0.000548124313354
Total: 0.0399761199951

500 Users
=========
Time to fetch one ranked row for current user with 100 attempts:
Average: 0.000312306880951
Min: 0.000298023223877
Max: 0.000331878662109
Total: 0.0312306880951
Time to fetch one page of all users with 100 attempts:
Average: 0.0575305533409
Min: 0.0567970275879
Max: 0.0626060962677
Total: 5.75305533409

1500 Users
==========
Time to fetch one ranked row for current user with 100 attempts:
Average: 0.000312027931213
Min: 0.00029993057251
Max: 0.00035285949707
Total: 0.0312027931213
Time to fetch one page of all users with 100 attempts:
Average: 0.167186529636
Min: 0.165693044662
Max: 0.174437999725
Total: 16.7186529636

5000 Users
==========
Time to fetch one ranked row for current user with 100 attempts:
Average: 0.000325660705566
Min: 0.000312089920044
Max: 0.000381946563721
Total: 0.0325660705566
Time to fetch one page of all users with 100 attempts:
Average: 0.560528736115
Min: 0.555218219757
Max: 0.661763906479
Total: 56.0528736115

10102 Users
===========
Time to fetch one ranked row for current user with 100 attempts:
Average: 0.000309851169586
Min: 0.000298023223877
Max: 0.00036096572876
Total: 0.0309851169586
Time to fetch one page of all users with 100 attempts:
Average: 1.1599576211
Min: 1.14222192764
Max: 1.35492897034
Total: 115.99576211

20057 Users
===========
Time to fetch one ranked row for current user with 100 attempts:
Average: 0.000322690010071
Min: 0.000284910202026
Max: 0.000576972961426
Total: 0.0322690010071
Time to fetch one page of all users with 100 attempts:
Average: 2.89920308352
Min: 2.79761099815
Max: 3.93713092804
Total: 289.920308352

@strozzapr: thanks for working with me on this! Glad to have found that bug. ??

I have submitted the function for Paul’s consideration to include in the plugin. That way people would be free to create their own leader boards. If Paul decides it’s not something he wants to do, I might consider making it a plugin, depending on the security and scalability risks.

??

Also updated the roster.php template file to work correctly with the headers… was hiding two columns in the header if the user wasn’t logged in.

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

	$listCurrentUser = dpa_get_leaderboard_rankings($current_user->ID);
	$listAllUsers = dpa_get_leaderboard_rankings();
	$user_is_logged_in = is_user_logged_in();
    $html = '
<table id="roster">
	<tr>
		<th>Rank</th>
		<th>Avatar</th>
		<th>Name</th>
		<th>Rank</th>
		<th>Advancement</th>';
	if ($user_is_logged_in)
	{
		$html .= '
		<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 ($user_is_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('&laquo; Previous Page', $listAllUsers["total_number_of_pages"]) . '</div>
    <div class="next panel">' . next_posts_link('Next Page &raquo;', $listAllUsers["total_number_of_pages"]) . '</div>
</div>';
	echo $html;

	get_footer();

Aha! Thanks for your help on this. Found some errors in the function, here is the updated function code. Everyone, please use this:

/* Generate Leaderboard Rankings **********************************************/
/**
 * 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 " . $db_prefix . "usermeta AS ranking WHERE ranking.meta_key = '" . $db_prefix . "_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;
}

I had missed two hard-coded database prefixes of mine. Sorry about that.

Got it. I sent two emails back with the var_dumps included. Let me know when you have them on the site and I will check it out again.

Thanks!
~Mike

Also, do a var_dump in dpa_get_leaderboard_rankings() right before the return statement, like so: var_dump($leaderboard_query);

Send me those results as well.

Got it ?? I copied your file verbatim, and it displays correctly on my site.

Do a var_dump() on $listCurrentUser and $listAllUsers right after they are fetched and email me those results.