Views Total points field (all categories)

Paul,

I believe the issue is because the userpoints table sums up the userpoints by category. A simple solution would be to have a single category, but I appreciate this would be limiting. In my case, I faced the same issue and this is what I did as a work around.

Note: I have updated the userpoints code directly, but you may want to consider creating a mini module to have a clean implementation. When I get down to cleaning my code, I will post it here.

Step 1: In your mysql database create a view as follows:

CREATE VIEW userpoints_total SELECT uid, sum(points) total_points FROM `userpoints` GROUP BY uid

Step 2: Update the userpoints_views_data hook code in userpoints.views.inc to the following:

function userpoints_views_data() {
  // ----------------------------------------------------------------
  // userpoints table

  // Describe the userpoints table.
  // Define the base group of this table. Fields that don't
  // have a group defined will go into this field by default.
  $data['userpoints']['table']['group']  = t('Userpoints');
  $data['userpoints_total']['table']['group']  = t('Userpoints');

  $data['userpoints']['table']['base'] = array(
    'field' => 'uid',
    'title' => t('Userpoints'),
    'help' => t('!Points accumulated by users on your site.', userpoints_translation()),
  );

  $data['userpoints']['table']['join'] = array(
    'users' => array(
      'left_field' => 'uid',
      'field' => 'uid',
    ),
    'node' => array(
      'left_field' => 'uid',
      'field' => 'uid',
    ),
    // This goes to the node so that we have consistent authorship.
    'node_revisions' => array(
      'left_table' => 'node',
      'left_field' => 'uid',
      'field' => 'uid',
    ),
  );

  // Describe the points column of the userpoints table.
  $data['userpoints']['points'] = array(
    'title' => t('!Points', userpoints_translation()),
    'help' => t("A User's current !points.", userpoints_translation()), // The help that appears on the UI,
    'field' => array(
      'handler' => 'views_handler_field_numeric',
      'click sortable' => TRUE,
    ),
    'argument' => array(
      'handler' => 'views_handler_argument_numeric',
      'numeric' => TRUE,
      'name field' => 'points', // display this field in the summary
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_numeric',
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
  );

  // Add relationship to user table.
  $data['userpoints']['uid'] = array(
    'title' => t('User'),
    'help' => t('Relate the userpoints table to the user table.'),
    'relationship' => array(
      'base' => 'users',
      'field' => 'uid',
      'label' => t('Users'),
      'handler' => 'views_handler_relationship',
    ),
  );

  //CUSTOMIZATION STARTS HERE

  //User points total

  $data['userpoints_total']['table']['base'] = array(
    'field' => 'uid',
    'title' => t('Userpoints Total'),
    'help' => t('Total !Points accumulated by users on your site.', userpoints_translation()),
  );

  $data['userpoints_total']['table']['join'] = array(
    'users' => array(
      'left_field' => 'uid',
      'field' => 'uid',
    ),
    'node' => array(
      'left_field' => 'uid',
      'field' => 'uid',
    ),
    // This goes to the node so that we have consistent authorship.
    'node_revisions' => array(
      'left_table' => 'node',
      'left_field' => 'uid',
      'field' => 'uid',
    ),
  );

  // Describe the total_points column of the userpoints_total table.
  $data['userpoints_total']['total_points'] = array(
    'title' => t('Total !Points', userpoints_translation()),
    'help' => t("A User's current total !points.", userpoints_translation()), // The help that appears on the UI,
    'field' => array(
      'handler' => 'views_handler_field_numeric',
      'click sortable' => TRUE,
    ),
    'argument' => array(
      'handler' => 'views_handler_argument_numeric',
      'numeric' => TRUE,
      'name field' => 'total_points', // display this field in the summary
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_numeric',
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
  );

  // Add relationship to user table.
  $data['userpoints_total']['uid'] = array(
    'title' => t('User'),
    'help' => t('Relate the total userpoints table to the user table.'),
    'relationship' => array(
      'base' => 'users',
      'field' => 'uid',
      'label' => t('Users'),
      'handler' => 'views_handler_relationship',
    ),
  );

  //CUSTOMIZATION ENDS HERE

  return $data;
}

Step 3: Clear the cache from admin/settings/performance

You should now be able to see an additional field called "Total ..." under Userpoints Group in the view

Hope this helps.

Cheers,
Dash

Hi dash,

I'm trying to do what you said but I'm getting a syntax error with:

"CREATE VIEW userpoints_total SELECT uid, sum(points) total_points FROM `userpoints` GROUP BY uid"

Maybe I'm doing it wrong as I'm not very familiar with db views. How and where should I be calling this?

I worked it out. For those interested on how to do so in phpmyadmin, I did so by doing SQL query for:

SELECT uid, sum(points) total_points FROM `userpoints` GROUP BY uid

When that returned, there's an option under the query for 'create view'. I pressed that, entered 'userpoints_total' for the view name and press 'go'. Everything worked out from there.

I hope they commit a total points field for the next release.

At me the same does not leave.
For the line CREATE VIEW userpoints_total SELECT uid, sum (points) total_points FROM ` userpoints ` GROUP BY uid

Writes:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' SELECT uid, sum (points) total_points FROM ` userpoints ` GROUP BY uid ' at line 1CREATE VIEW userpoints_total SELECT uid, sum (points) total_points
FROM ` userpoints`
GROUP BY uid

And red allocates SUM.

subscribing

From reading the different posts in the issue queue, it seems like views integration is incomplete at the moment. I hope to help with any testing of patches if/when available. Thanks

subscribing

It was just necessary to add a prefix for my table and all!

Here is a separate module implementation as dash suggested. It includes the db view creation in the install hook (and deletion in the uninstall hook), so as long as your drupal db user has the privileges to create a view you should be able to just turn on the module and have it work.

Thank you very much. +100
This was a very much needed feature.
My Platform: Drupal 6.19 with Views 3.0-alpha3

Should this be 'needs review' to be considered as possible code to be added to the core module? This seems like a very useful field with views integration.

@gilgabar - would you be able to kindly submit your code as a patch? Thanks for the great work!

This is a good workaround, but is it really the right solution for a patch? I'm not sure. I would consider the performance and compatibility of using a db view vs other alternatives before recommending this as *the* approach to adding total points in Views.

I haven't considered this very deeply but, one alternative is using the userpoints table. The same way that per category totals are tracked, an aggregate overall total could also live there. But then how does that fit with the existing schema? The tid column currently distinguishes categories, so does an overall total just use NULL to set itself apart? Doesn't seem very elegant. Perhaps a separate table for the total instead?

It seems worth a bit of discussion in any case before a patch is put together.

Interesting idea, not sure i like adding a special meaning to NULL however, especially since 0 already has a special meaning (the default category). Separate table (userpoints_total?) sounds better.

Some things to keep in mind:

- I'm not sure how much progress the 6.x version will see. I am pretty much only working on 7.x-1.x and I'm already thinking about 7.x-2.x, I don't have any plans for a 6.x-2.x version.

- 6.x-1.x on the other side is already stable and I don't think that big features will be added, at least not if they break API's. Maybe this could call itself a performance improvement ;)

Thank you for the consideration. I hope it can be thought of as a "performance improvement" =)

For my use-case points are rewarded for different interactions with the site.
1) adding comments in forums
2) approving user relationship requests
3) sending invites
4) etc

So I have different categories for points.
Forums:
Relationships:
Invites:

But I also would like to show Total Points and allow sorting by the total value.
Forums: 25
Relationships: 50
Invites: 25
Total: 100

Hope to help with testing if this is considered as a needed functionality in the core module.
Thanks very much in advance!

Is there a solution to this problem in the 7.x version already? I imagine when I move to d7 I'll be looking for the same functionality there, so if it is not already solved perhaps we should fix it there first and then backport the same solution to 6.

No, 7.x-1.x doesn't have a solution for this either.

For 7.x-2.x, we are thinking about using the entity/field system: points would be a field which you can attach to entities like users and point transactions would be a fieldable entity. Categories as they work now would be dropped. Instead, to have really separate field amounts, you could add two fields to users and to categorize them and have a common total, you could add a terms field to the transactions. But we haven't really thought this through yet, input is welcome (somewhere else than in this issue however, I'd say).

The real solution is to write a Views field handler that does the query, and add a new field type for it. I just spent the past hour trying to find documentation on how to do that, with absolute minimal results. Maybe someone with Views Fu can chime in.

subscribing

The solution at #8 seems great, although I didn't try it out yet.
Any chance to post it as a contributed module, or to include it in the User Points package?
UPDATE: It works great! Thanks.

subscribing

thank #8
this works great
this should have to be included in userpoints

I tested #8, with userpoints 6.x-1.2.

When you're listing users "with points" in more than one category, views will list the same user more than once.

Now this was tested with two categories: uncategorized and abc. Maybe uncategorized is not being summed up in the total, and that's why views displays the same user twice.

For example: spongebob is in two categories, the views list would look like this:

johndoe  -----------  200 points
janedoe  -----------  180 points
spongebob  ------   25 points
spongebob  ------   120 points
stevensegal  -----   50 points

Spongebob has 25 points in abc category, and has 120 points in uncategorized category. So this is not the sum, which should read only one spongebob with 145 points.

However when you go to the user profile, the sum is there 145 points. But this has nothing to do with the patch module above, as this setting is for userpoints module and found in settings/userpoints/Points Categorization "Select which category of points to display on the user profile page. Select "All" to display a sum total of all categories"

Any other ideas or suggestions on what might be causing the user to listed more than once and not showing the correct total?

for some reason I had a view sorted by Total Points which was working perfectly and now it returns nothing. If I remove the sort method of Total points then it works. I don't know what could cause this since I did not touch the view or anything I know.

I unsinstalled the module #8.. and reinstalled it and it is back working... mmm I don't know what is happening.

subscribing...

So hello - I'm working on a site that uses the module in comment #8, and am being bitten by an issue with the security DEFINER context of the mysql view defined therein ({userpoints_total})... this after migrating to new infrastructure with new server and mysql credentials. Has anyone else bumped into this?... I think I need to just cobble together an ALTER DEFINER statement, which I'm working on now.

Note that this doesn't seem to break the site, but it does break drush sql-dump/sql-sync.

Has anyone found a way to do what Billmar is asking in #8. This is something that I would assume should be built into the core of userpoints with a default view. As he mentioned there are probably 5-6 ways to earn points on my site, and each method awards a different point value. My users would probably LOVE to see a breakdown of how they are receiving those posts AND to make sure they are actually receiving them. For example if they get 5 points for adding new node content and they added 5 nodes, they would want to confirm they have 25 points for adding nodes. Also I encourage content creation by awarding points for each time their content is viewed. If I can show them a hugh number in their node viewed column it will further encourage them to add more nodes.

On a side note, perhaps some of you might know how to do this. I would like to also have a view or at least a way to pull out my top point earner for a given month. For example I would like to give out prizes for the highest point earner for the month of November. Since I don't reset the points every month and let them continue growing I have no idea how to gauge who earned the most points in the current or previous month. Does anyone know how to pull out this data? or put it on a view page?

Hi drupalfan81,

I was wondering if you were able to implement the side note you mentioned.

On my website, users are given points everyday for each month to play a game ... At the end of each month, the users with the most points win prizes.

Currently, I have to reset my userpoints at the end of each months but I would love to have the ability to accumulate points and have a block for points earn the current month.

I have been searching for a solution (Still am) but without much success.

Any help would be greatly appreciated!

Thank you!

Hi LoudPixels,

I unfortunately never got around to figuring this out. I was hoping to hear from someone else that found the solution. But looks like we are both in the same situation. Maybe someone will come along and provide a solution to this.

You might be able to do it with a scheduled rule and using some custom PHP to just clear the database table?!?! I haven't been touching the code on my Drupal sites for a while now. I take 6 months off at a time from coding to focus on other things in life. But if I get back around to it and solve this one, I will be sure to share. Thanks!

I found a blog, not long ago, that described the process of showing in a view the total points each user has earned broken down by months but I can't locate this post again.

Can someone suggest a solution or point me to a step by step instruction on doing this?

Basically, all I want to do is show a view block like the one below

May 2016
UserC ......70pts
UserB.......60pts
UserA .......50pts

Aril 2016
UserB ...... 100pts
UseA .......50pts
UserC .......30pts

March 2016
UserC ...... 120pts
UserA .......80pts
UserB .......50pts

Any help would be greatly appreciated!

Thanks!

How do I do this for Drupal 7?

Closing this as outdated, 6.x version is no longer maintained.