Total score aggregate function for Node: Vote results has no effect

Getting the same error. I can't sort by votes at all with views. The value is always 0.

Using the plus 1 module with nodes. The tables votingapi_vote and votingapi_cache are getting updated correctly.

Here's the view:

$view = new view;
$view->name = 'most_popular';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('relationships', array(
  'votingapi_cache' => array(
    'label' => 'Vote results',
    'required' => 0,
    'votingapi' => array(
      'value_type' => 'points',
      'tag' => 'vote',
      'function' => 'count',
    ),
    'id' => 'votingapi_cache',
    'table' => 'users',
    'field' => 'votingapi_cache',
    'relationship' => 'none',
    'override' => array(
      'button' => 'Override',
    ),
  ),
));
$handler->override_option('fields', array(
  'title' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'target' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
      'strip_tags' => 0,
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'link_to_node' => 1,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
));
$handler->override_option('sorts', array(
  'created' => array(
    'order' => 'DESC',
    'granularity' => 'second',
    'id' => 'created',
    'table' => 'node',
    'field' => 'created',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'status' => array(
    'operator' => '=',
    'value' => '1',
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'status',
    'table' => 'node',
    'field' => 'status',
    'relationship' => 'none',
  ),
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'story' => 'story',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('cache', array(
  'type' => 'none',
));
$handler->override_option('items_per_page', 5);
$handler->override_option('style_plugin', 'list');
$handler->override_option('style_options', array(
  'type' => 'ul',
));
$handler = $view->new_display('block', 'Block', 'block_1');
$handler->override_option('block_description', '');
$handler->override_option('block_caching', -1);

Double checking on this. Did something change in the latest release of views? Just trying to get this to the right people.

Also tested it with fivestar, and still the view is coming up with values of 0, even though there has been votes, and even though the vote are in the database.

Thanks!

Figured the problem out, at least for me. The relationship was set up wrong. There are multiple "Vote: Results" relationships and I chose...poorly. ;)

Got same kind of problem, not with filters or sorting, but with displaying TOTAL. Total votes of any nodes (even if they have correct AVG) are always 0.
Algorithm:
1. Add relationship Content:Vote results, set Aggregation function=Total score;
2. Add field Vote results: Value, select Relationship = name of #1 relationship; Add some another fields (i.e.title of node).
3. Save. Field #2 always shows 0.

Views 6.x-2.10, Voting API 6.x-2.x-dev (2010-04-23)

Seems that votingapi module does not write SUM function to votingapi_cache table, because VIEWS result query has correct statement:
....LEFT JOIN votingapi_cache votingapi_cache_node_sum ON node.nid = votingapi_cache_node_sum.content_id AND (votingapi_cache_node_sum.content_type = 'node' AND votingapi_cache_node_sum.function = 'sum')
...
So, I have NO rows in votingapi_cache table with function=sum. Only 'average' and 'count'.

P.S. Running cron and clearing cache does not help.

Same results with vud. Filtering by vote results value greater than 0, but all 0 votes still display.

Subscribing. Thanks

It seems to be working ok for me. It is not trivial to setup. I am using vud (Vote Up/Down module). I have three relationships setup- for each one--- Value type: points, Vote tag: Normal vote. Aggregation function for each relationship: one using Positives, one using Negatives, and one using Number of votes. I can filter by number of votes greater than zero. And I can sort by both # of positives and # of negatives.

By the way, Negatives use negative number (doesn't count as in Integer the # of negatives). Keep in mind when sorting asc of desc.

Using Voting API 6.x-2.3, Vote Up/Down 6.x-2.0 and Views 6.x-2.11.

I initially struggled with it. So, thought I would post this hoping it could help others.

+1 has this been fixed as I can't seem to get a result other than 0 and am wondering if I am configuring this incorrectly?

Have the same issue.
Relationship: Node Vote results
Label: Total score
Value type: percent
Vote tag: normal vote
Aggregation function: Total Score

Field: Vote results: Value
Appearance: Default appearance
Relationship: Total score

It outputs: 0

subscribing

I happen to have the exact same problem: showing average vote results of nodes works perfect, the total amount of votes as well, but displaying the total score (points) simply doesn't work. As Julia Trushina states, the voting tables don't hold any of the 'total points' information.

As a temporary solution, I did manage to show the total points for every node not using the vote api, but by adding a custom php field (http://drupal.org/project/views_customfield). This custom field allows me to use php in a view and provides some parameters such as $data->nid which in my case, functions as the node id (since my filters only select nodes of a certain cck type). Using php, and with the nid supplied, it's fairly easy to calculate the total points yourself with a query on the votingapi_vote table.

Not an ideal sollution since the query is executed for every single row in the view result, but since there's no real bugfix yet, it might be a solution for some of you... The only problem I still have is that my view should be ordered desc based on this total points field, which can't be done, the value of the custom field is not available as a sort parameter.

+1 for fivestar

need this to work

subscribing

subscribing

The initial investigation suggests that the 'Value type' attribute is missing, causing the problem. I will post more later tonight.

I upgrade to Views 3. Still no luck. I gave up after realizing the complexity of it.

A little progress, but not there yet:
I was able to display the vote count. Views 3.x dev, VotingAPI 2.x dev.
Value type Percent, Tag normal vote, Aggregation Number of Votes.

It shows the number of votes in percent format. For example if 4 votes are casted, it will show 0.20 (20%, 100/4).

Fixed it! :D

The cache function was generating incorrect output. Fix the cache function as described below and vote a content to update the cache for that content (or update the vote cache thru cron). It will show the correct result.

I will post a patch shortly.

Line 487 and 490 must be changed.

/**
 * Builds the default VotingAPI results for the three supported voting styles.
 */
function _votingapi_get_standard_results($content_type, $content_id) {
  $cache = array();

  $sql  = "SELECT v.value_type, v.tag, ";
  $sql .= "COUNT(v.value) as value_count, SUM(v.value) as value_sum  ";
  $sql .= "FROM {votingapi_vote} v ";
  $sql .= "WHERE v.content_type = '%s' AND v.content_id = %d AND v.value_type IN ('points', 'percent') ";
  $sql .= "GROUP BY v.value_type, v.tag";
  $results = db_query($sql, $content_type, $content_id);

  while ($result = db_fetch_array($results)) {
    $cache[$result['tag']]['points']['count'] = $result['value_count'];
    $cache[$result['tag']]['percent']['average'] = $result['value_sum'] / $result['value_count'];
    $cache[$result['tag']]['points']['sum'] = $result['value_sum'];
  }

Adding the patch for 6.x-2.x.

The head version is completely different for Drupal 7. Not sure if it will need the patch. Leaving as it is.

what about those value types that aren't points or percentage? For example, I am using the Mark module and I have that type selected.

dtecson,
If it's working for you, just leave it like that. Otherwise you will need find out what change needs to be there in the cache function to fix your problem.

IMHO, the Voting API module developers are the right persons to comment and include/exclude the patch. From my point of view it needs to be fixed.

Is it gonna get fixed any time soon? :(

A side effect to this:

http://drupal.org/node/1054990#comment-4137120

subscribing

subscribing

subscribing

I can confirm this also, the function column has no 'sum' data in votingapi cached results, so your Total Score wont ever work

I've applied the patch above and can see new rows in my cache table

mysql> select * from votingapi_cache where content_id = 1169;
+---------------+--------------+------------+-------+------------+------+----------+------------+
| vote_cache_id | content_type | content_id | value | value_type | tag  | function | timestamp  |
+---------------+--------------+------------+-------+------------+------+----------+------------+
|        189447 | node         |       1169 |    96 | points     | vote | count    | 1301361398 |
|        189448 | node         |       1169 |  9000 | points     | vote | sum      | 1301361398 |
|        189449 | node         |       1169 | 93.75 | percent    | vote | average  | 1301361398 |
+---------------+--------------+------------+-------+------------+------+----------+------------+
3 rows in set (0.05 sec)

However SORT still does not work in views for this entry

I've added a relationship to my views

Top Voted: Configure Relationship Node: Vote results
[*] Require this relationship
Value type = points
Vote Tag = normal vote
Aggregate Function = Total Score

Additionally if i visit node/1169 the fivestar widget reports "No votes have been made"

dgtlmoon,
Please apply this patch as well: http://drupal.org/node/1054990#comment-4137120

Yes I tried that change but still it did not make the fivestar work (see my comment on that thread)

got the same still not working

I am bumping this issue, changing the version and the status. The last post here was 1 year, 35 weeks ago.

For context here, I have a multi-site win/unix dev platform. One of the sites is d7b.dev which is always a fresh install when things go wrong.

d7b.dev has just been updated to 7.23 today.

The voting_api and view modules each have their own Drupal git repositories and have each been refreshed today. A modified version of Vote Up/Down is being used to enumerate votes.

So I'm attempting to create a new view that presents the Questions with the most Votes. Here is a screen shot (first image) of the high level and then (second image) the Configure Relationship: Content: Vote results form.

Note that no filters are enabled yet the query yields 0 votes for every node. With no filters enabled, it should yield mutiple aggregraton results -- Number, Average, Total, Positives, Negatives -- but everything is zero (image 3).

Something is wrong here...

UPDATE - I forgot to mention that I saw this same behavior in D6 as well so I think the patch below might fix both. Hence the version change from 6 to 7.

I thought you would never ask...

A patch has been attached...

I only figured this out by hacking the convoluted query generated by views/votingapi...

SELECT votingapi_cache_node.value AS votingapi_cache_node_value, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, node_counter.totalcount AS node_counter_totalcount, node.title AS node_title, node.nid AS nid, userpoints_total.points AS userpoints_total_points, users_userpoints.name AS users_userpoints_name, users_userpoints.uid AS users_userpoints_uid, users_userpoints.picture AS users_userpoints_picture, users_userpoints.mail AS users_userpoints_mail, node.created AS node_created, 'node' AS field_data_body_node_entity_type
FROM 
{node} node
LEFT JOIN {votingapi_cache} votingapi_cache_node ON node.nid = votingapi_cache_node.entity_id AND votingapi_cache_node.entity_type = 'node'
LEFT JOIN {userpoints} userpoints ON node.uid = userpoints.uid
LEFT JOIN {users} users_userpoints ON userpoints.uid = users_userpoints.uid
INNER JOIN {node_comment_statistics} node_comment_statistics ON node.nid = node_comment_statistics.nid
LEFT JOIN {node_counter} node_counter ON node.nid = node_counter.nid
LEFT JOIN {userpoints_total} userpoints_total ON node.uid = userpoints_total.uid
WHERE (( (node.status = '1') AND (node.type IN  ('question')) ))
ORDER BY votingapi_cache_node_value DESC
LIMIT 20 OFFSET 0

There are a few too many left joins here...

This is what I get after applying above patch (see image)

Although the numerical vote results show up, they are not sorted properly. Any question that has not been voted on (NULL) is at the bottom of the list, even after the stupid questions have been voted down.

I think this is because there is no way of applying a IFNULL(, 0) or COALESCE(, 0) function to an individual field in Views. I may be wrong.

COALESCE is a function name that must have been invented by a couple of software engineers after they had a few too many beers.

Whatever; it seems to be needed here. I digress.

Over to you Views...

Hello, much text here and little I understand.
Do I understand this is about the automatically generated view under /top-rated-points, which does NOT count the Plus 1 votes?
I see patches and configurations, and a jump from 3 years ago to 11 months ago, but nothing that looks like a real solution. Is the /top-rated-points view (still) not working and should I de-activate it in the menus or is there a way to give it some sense?
The Fivestar vote count under /top-rated-percent DOES seem to work (not sure if Plus 1 votes might be going there...)
I'm talking: D7.28, Plus 1 7.x-1.0-alpha2 and Fivestar 7.x-2.1
Thanks.

+1
Same issue here, can't get the results.
Keep showing 0, Views 3, Voting API 7.x-2.11, and Advanced Poll.

Seems like this is still an issue...

Why no solution for this big bugg ? How get indivifdual value cast a vote to put a block how better vote a node ?

The module almost did all the things that I needed. I appreciate all the hard work that went into it! Unfortunately, I really needed the total to work and none of the patches I found did the trick for me. Since the module is in "no further development" and I don't need anything too sophisticated, I just went with the https://www.drupal.org/project/raty and https://www.drupal.org/project/starrating because they don't depend on the Voting API.

I'm fairly sure this topic is a misunderstanding about how Voting API and Views works.

There is no need for a separate SUM column in Voting API. This option already exists in Views. Do the following:

1) Choose the view to edit.
2) Make sure you have the "Votes: Value" field included in your view.
3) Click the "Use aggregation" link in the Other section in the right column.
4) Choose "Yes" and save. After saving, all fields, filters, and sorts will show an "Aggregation settings" link next to them.
5) Click "Aggregation settings" for the "Votes: Value" field, and choose SUM.

This creates a view of all your results, with the votes summed together to display a total for each record.

Marking this issue as closed.