Points This Month
ebeyrent - February 12, 2008 - 14:03
| Project: | User Points |
| Version: | 5.x-3.x-dev |
| Component: | Code: userpoints_views |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | patch (code needs work) |
Description
One of the things I needed to do was to create a view that displayed a user's current points, total acquired points (which we call lifetime points), and the number of points acquired this month.
To accomplish this, I patched userpoints_views to give me this functionality. It appears to be working, but needs more testing from the community at large. I will be the first to admit that my solution may not be the most correct solution - I am hoping to get suggestions for improving the way this works, and hopefully others out there can benefit from this work.
#1
And here's the patch...
#2
Just what I need! Will this patch work on 5.x-2.x?
Cheers,
Peter.
#3
OK, I've inserted your code into the 5.x-2.x version and it works nicely.
But I need to be able to sort by the summed userpoints in descending order. I'm new to Drupal, and still trying to figure it out (books ordered from Amazon and on their way!). I started out by just adding an "order by" to your query for "Points This Month" to order the results, but it still gets ordered by username:
$intPointsThisMonth = db_result(db_query("select sum(userpoints_txn.points) as total from userpoints_txn inner join userpoints on userpoints.uid = userpoints_txn.uid
where userpoints.uid = %d AND userpoints_txn.uid = %d AND MONTH(FROM_UNIXTIME(userpoints_txn.time_stamp)) = MONTH(NOW()) AND
YEAR(FROM_UNIXTIME(userpoints_txn.time_stamp)) = YEAR(NOW()) order by total desc", $data->usernode_users_uid, $data->usernode_users_uid));
I guess it makes more sense to set up a "sort" and build the query using the add_orderby function, but clearly I don't know enough about how the Views code works yet. I'll spend today trying to figure it out from the online documentation. But perhaps I could borrow your knowledge and expertise on this?!
Thanks,
Peter.
#4
Hi, on further testing, your patch turns out to be quite inefficient, as the query executes once for each user - OK for a couple dozen users, but not for thousands.
I can't find any good documentation on how Views works. I've tried to write a new query handler for the "Points This Month" field, but Drupal forces me to use the node table when I don't need to. The following simple MySQL query does what I want - it totals up all the userpoints transaction points for the month and displays them in reverse order of points:
select u.uid, u.name, sum(t.points) as total from users u join userpoints_txn t where u.uid = t.uid group by u.uid order by total desc;
But I don't know how to get this into Views.
Can someone point me in the right direction, or at least point out some good Views documentation for a newbie? I'm stuck.
Thanks,
Peter.
#5
The documentation for Views is not very good: http://drupal.org/node/99564.
Views requires use of the node table. You'll notice in my code that my query handler simply returns the UID. The handler is called in the field definition:
<?php'points_sum' => array(
'name' => t('Userpoints: Points This Month'),
'help' => t('Displays the accumulated points for a given user this month'),
'notafield' => true,
'sortable' => false,
'option' => 'string',
'handler' => 'userpoints_views_views_points_handler_thismonth',
'query_handler' => 'userpoints_views_views_points_query_handler_thismonth'
),
?>
Note the use of 'notafield', which indicates that this is not a field in the database. Sortable should be true or false, depending on your needs. 'query_handler' is the function where you would put your query:
<?php
function my_userpoints_month_query_handler() {
$result = db_query('select u.uid, u.name, sum(t.points) as total from users u join userpoints_txn t where u.uid = t.uid group by u.uid order by total desc');
...
}
?>
HTH
#6
Please do not change issue titles.
#7
Hi ebeyrent, thanks for your reply. Yes I've been struggling with Views, I wish it was in the Pro Drupal book!
The code below is more efficient, as it doesn't run the SQL query for every uid (note that at the moment it does a sort as well; will take that out and put it in the appropriate place when I've finished testing):
<?php
function userpoints_views_views_tables() {
...
'points_sum' => array(
'name' => t('Userpoints: Points This Month'),
'help' => t('Displays the accumulated points for a given user during this month.'),
'notafield' => true,
'sortable' => false,
'option' => 'string',
'handler' => 'userpoints_views_points_handler_this_month',
'query_handler' => 'userpoints_views_points_query_handler_this_month'
...
}
function userpoints_views_points_handler_this_month($fieldinfo, $fielddata, $value, $data) {
return intval($data->points_sum);
}
function userpoints_views_points_query_handler_this_month($fielddata, $fieldinfo, &$query) {
$query->add_table("userpoints_txn", false, 1, array('left' => array('table' => 'node', 'field' => 'uid'), 'right' => array('field' => 'uid')));
$query->add_orderby('', 'sum(userpoints_txn.points)', 'desc', 'points_sum');
$query->add_groupby('usernode_users.uid');
$query->add_where('MONTH(FROM_UNIXTIME(userpoints_txn.time_stamp)) = MONTH(NOW())');
$query->add_where('YEAR(FROM_UNIXTIME(userpoints_txn.time_stamp)) = YEAR(NOW())');
}
?>
#8
@wotstheguts
Re: merging this in 5.x-2.x is unlikely, since 5.x-3.x is the active branch now.
@ebeyrent
I have a few comments on this patch:
1. Is not db prefix clean. All tables have to be enclosed in {}. I fixed some of that, but maybe not all (specifically the stuff in the last function).
2. It is MySQL specific, so it will not work with PostgreSQL. Personally, I don't use PostgreSQL, and those who need it can fix the SQL to do so, but at least we should put a description on the field to say it is MySQL only at present.
3. I changed points_sum to points_sum_month to be descriptive enough, in case we want to do week and year, ...etc.
4. The month names are not translatable. You need to add t() to it.
5. Lots of code style changes in conformance with coding standards.
I attached my version. Please test it and see if it works, and work on some of the above, then I will include it.
#9
@kbahey: Thanks for the patch and the comments - I'll work on your suggestions and apply/test.
#10
Another thing I noticed is that it uses things called
$data->usernode_users_uid. Is this part of the usernode module? If it is, or any other module, then that is a dependency that we don't want for userpoints_views.I realize that the default views supplied by this module do assume usernode is installed, but the exposed fields and filters should not over use this.
Again, we can just add a comment in the description so users are aware.