Sortable User Flag Stats in Views Using the Profile Module
Recently I was trying to build a user view for Tutr.tv that would include statistics on user flags. I fought views for a couple hours to try to generate the number of videos a user had flagged as watched and favorite. Then I took another direction that solved the problem in 30 minutes.
The flag module does integrate into views well. You can get counts of the number of nodes a user has flagged. The problem is that the counts sum all flags. I wanted to get counts of individual flags, watched and favorite in particular. I tried finding the answer both using a view with users as the base and with nodes as a base. Neither way produced the results I wanted.
Out of frustration and to just try something different, I worked through a method of saving stats for the user and then integrated those numbers into views. We were already using the Profile module. So the first step was to add some profile fields to store the stats. I went to admin/user/profile and created text fields in a new category called stats with visibility set to hidden. Profile fields are automatically available to views. You can also make them hidden (so the user can't change them), thus they can work well for storing user stats.
The next step is to store the flag stats. To do this we will need to create a custom module that uses the flag api to save the counts each time user flags or un-flags a node. For this example, the module is called custom.module. Below is the hook_flag implementation for storing user flags to the profile fields.
/**
* Implementation of hook_flag()
* Monitors flagging acction then updates stats in the user profile stats
*/
function custom_flag($action, $flag, $content_id, $account) {
// machine names of flags we want to track
$track = array('watched', 'favorite', 'later');
if (in_array($flag->name, $track)) {
$count = $flag->get_user_count($account->uid);
profile_load_profile($account);
$edit = custom_generate_profile_edit_array($account);
$edit['profile_stats_flag_' . $flag->name] = $count;
profile_save_profile($edit, $account, 'stats');
}
}
/**
* Returns the edit array needed for profile_save_profile from a profile
*/
function custom_generate_profile_edit_array($account, $category = 'stats') {
$edit = array(
'profile_stats_flag_watched' => 0,
'profile_stats_flag_favorite' => 0,
'profile_stats_flag_later' => 0,
);
if ($category == 'stats') {
foreach ($edit AS $k => $v) {
if ($account->{$k}) {
$edit[$k] = $account->{$k};
}
}
}
return $edit;
}
Next I wanted to implement sorts based on flags. Views 2.x does not support exposed sorts and I didn't want to create a different display for each sort so I fell back to using a hook_views_query_alter(). The sort order is passed in by using a sortby query parameter.
/**
* Implementation of hook_views_query_alter()
*/
function custom_views_query_alter(&$view, &$query) {
$sorts = array(
'joined' => 'users_created_minute DESC',
'watched' => 'profile_values_profile_stats_flag_watched_value DESC',
'favorites' => 'profile_values_profile_stats_flag_favorite_value DESC',
);
if(($sortby = $_GET['sortby']) && $sorts[$sortby]) {
$query->orderby[1] = $query->orderby[0];
$query->orderby[0] = $sorts[$sortby];
}
}
The last step is to create a block form that provides the sorting select box.
/**
* implementation of hook_block().
*/
function custom_block($op = 'list', $delta = 0, $edit = array()) {
if ($op == 'list') {
$blocks[0] = array('info' => t('Sort by'));
return $blocks;
}
else if ($op == 'view') {
switch ($delta) {
case 0:
$block = array('subject' => t('Sort by'),
'content' => drupal_get_form('custom_tutorial_sortby_form'));
break;
}
return $block;
}
}
/**
* Returns sort by form
*/
function custom_tutorial_sortby_form() {
$sorts = array(
'joined' => 'Joined date',
'watched' => 'Most watches',
'favorites' => 'Most favorites',
);
$form['sortby'] = array(
'#title' => t(''),
'#type' => 'select',
'#options' => $sorts,
'#default_value' => ($_GET['sortby']) ? $_GET['sortby'] : 'joined',
// javascript to auto submit form when item selected
'#attributes' => array('onchange' => 'this.form.submit();'),
);
$form['submit'] = array(
'#type' => 'submit',
'#value' => t('Sort'),
// hide submit button since javascript is auto submitting. Note: this should be done with a javascript to fail gracefully
'#attributes' => array('style' => 'visibility: hidden; height: 0px;'),
);
return $form;
}
/**
* Processing for sort by form submit
*/
function custom_tutorial_sortby_form_submit($form, &$form_state) {
drupal_goto($_GET['q'], 'sortby=' . $form_state['values']['sortby']);
}
Everything seemed to be working great. Until the first person hit 10 watch flags. Then the sort order was wrong. The profile module was storing counts as a text field, thus it was doing a text sort so 10 would be below 8. At first I thought there was no simple way to solve this problem and my whole approach would blow up. Then I found that MySQL can cast a field. Who knew? (probably you DBA types out there) That is pretty cool. So I simply cast the varchar field to an unsigned (int) and presto, sorts were working properly.
/**
* Implementation of hook_views_query_alter()
*/
function custom_views_query_alter(&$view, &$query) {
$sorts = array(
'joined' => 'users_created_minute DESC',
'watched' => 'CAST(profile_values_profile_stats_flag_watched_value AS SIGNED) DESC',
'favorites' => 'CAST(profile_values_profile_stats_flag_favorite_value AS SIGNED) DESC',
);
if(($sortby = $_GET['sortby']) && $sorts[$sortby]) {
$query->orderby[1] = $query->orderby[0];
$query->orderby[0] = $sorts[$sortby];
}
}
I readily admit that this is an alternative hack. You always want to do lists in views using only standard views functionality. However sometimes you get into some edge cases with views that are complex to figure out or even impossible. That's when Drupal's hook system can start to look very enticing. Ultimately I spent 2 hours trying to figure this out in views, and 30 minutes using views with some custom hooks.
That being said, does anyone know how to pull this off using just views?
photo credit: Marc_Smith
