Views Sort by Voting API Results (descending) lists -1 before 0.

Just confirming that this happens for me as well.
I don't think views is recognizing the 0 value. because, negative numbers are sorted correctly, empty values are being placed at the end of the results.
I think you are right in that it comes from the content not being voted on yet, because a negative vote is still a vote, and therefore showing before the "no votes" content.

10,5,1,-3,0,-1,0 is being sorted as 10,5,1,-1,-3,0,0
when it should be 10,5,1,0,0,-1,-3

Hi there.
I have the same problem.
The only workaround I've found is to vote up and down (sum is 0) somehow...

Any ideas?

This appears to be an artifact of an older issue -- nodes with no votes are actually storing "NULL", not the number zero, and that messes up SQL sorting something fierce. Newer versions of VotingAPI should correct for this. If you're still encountering the problem, please re-open the issue with some additional clarification. Thanks!

I'm experiencing this problem with the 5.x-1.6 version.

My View:

Node Blue 1 point
Node Green -1 points
Node Orange 0 points

I didn't open a new issue as the description is exactly the same as for the 6.x-2.0-rc2 version.

I'm getting this exact problem with the current versions of votingapi and views, on Drupal 6.13.

Eaton commented above that "Newer versions of VotingAPI should correct for this", but this is not hte case as far as I can see. Consequently reopened the issue. I hope that was the right hting to do.

My view is set up with a relationship to voting results using the total score aggregation function. I have added a sort to this view: Vote Results Function, descending. Still, negative results appear above nodes with no votes and a score of 0.

If anyone has overcome this problem, I'd be really grateful to know how you did it.

Thanks!

A bit more detail...

This is the query generate by my view:

SELECT DISTINCT(node.nid) AS nid,
   votingapi_cache_node_sum.value AS votingapi_cache_node_sum_value,
   node.created AS node_created
 FROM node node 
 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')
 WHERE (node.status <> 0) AND (node.type in ('idea'))
   ORDER BY votingapi_cache_node_sum_value DESC, node_created DESC

If I add an 'IFNULL()' statement, and change this to:

SELECT DISTINCT(node.nid) AS nid,
   IFNULL(votingapi_cache_node_sum.value, 0) AS votingapi_cache_node_sum_value,
   node.created AS node_created
 FROM node node 
 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')
 WHERE (node.status <> 0) AND (node.type in ('idea'))
   ORDER BY votingapi_cache_node_sum_value DESC, node_created DESC

Then I get the result I want: 0-value nodes are sorted correctly.

Am I right in thinking I will have to patch votingapi_views_handler_relationship.inc to achieve this behaviour?

OK. I'm starting to think I need to change votingapi_views_handler_field_value.inc, not votingapi_views_handler_relationship.inc

It's the former class that adds the field votingapi_cache_node_sum.value to the query, so maybe I need to make this class add the ifnull statement. Not sure how to do this, though. Any help would be appreciated.

I decided not to patch the module for my own purposes, and I have solved this issue in a different way, so I've set this issue back to closed. Once again, I hope I'm doing the right thing - I'm new to drupal.org

For anyone facing the same problem, this is what I did: In the end, I just set the "Require this Relationship" option in the Views relationship settings. This has the effect of turning the left join between nodes and votes into an inner join. Nodes without votes then simply do not show up. When I eventually thought it through, this made sense to me: I was ordering nodes by 'popularity', and it's hard to state the popularity of one with no votes at all. I'm ok with it simply not showing up until it has at least one vote.

I am using voting API with extra voting forms and I wanted to create a view with all nodes with votes below a certain number. So I wanted to display all nodes with less than 10 votes. However, nodes that haven't received any votes don't show up in the view. I am not trying to sort the nodes based on votes but I just want all nodes with less than 10 votes to be displayed (including 0 votes). "Require this relationship" is not checked under Node: Vote results relationship. I think the only way to get this to work in my case would be either extra voting forms automatically casts the first vote when a node is submitted (which doesn't happen right now and I didn't find a way of making this happen) or voting API recognizes nodes with no votes as 0 votes (I think this would be a better option since it would also solve the sorting issue and issues with other voting forms). So, is there anyway this can be fixed ?

Hey, guys. I'm posting to let you guys know how I solved my issue.

My problem was I'm making a playlist site, and the playlist sorts by vote percentage. Of course, songs that have not been voted on = NULL, so the sorting returns +1, -1, then 0. For a playlist, it would make sense that songs that have not been voted on are treated as "indifferent", so -1 should fall below these.

They way I solved this was by (gasp!) adding a function to the module (in this case, it was Vote Up / Down). Technically, you should make a separate module for this, but c'mon...

I posted this on the very bottom of vote_up_down.views.inc. I hope it's somehow useful to other people on this forum:

/**
 * Implementation of hook_views_query_alter()
 */
function vote_up_down_views_query_alter(&$view, &$query) {
 	$query->fields['votingapi_cache_node_points_vote_average_value']['table'] = 'IFNULL(votingapi_cache_node_points_vote_average';
 	$query->fields['votingapi_cache_node_points_vote_average_value']['field'] = 'value, 0)';
}

Hi everyone. I am also experiencing this issue with sorting content in a view by value with content with no votes. Negative values preceed those nodes with no votes when sorting by descending value. I'm using the latest dev version.

Switching the version to 6.x-2.x-dev since its happening there too.

This still happens, I am using Voting API 6.x-2.3 and Vote Up/Down 6.x-2.0

Result of votes sorted by Total score:
+4
+2
+1
-1
-3
0
0

same thing here, will need to hack it or something... :/

#9
@godawful

positive sorting has never been an issue, you just need a "filter by vote results > 0"

the issue is not fixed, because the problem has always been with negatives and 0s sorting. Since 0 is NULL (no value) instead of actually being assigned a 0 value, it messes up the sorting

A fix for this has been checked into the D7 branch, and will be backported. I'm concerned about the potential performance impact of the COALESCE statement that had to be used to make it work, so the sort handler being used allows the behavior to be toggled. The default is the current behavior, and the 'treat nulls as zeros' behavior can be toggled on the sort plugin's settings form.

@eaton

thanks for working on the issue eaton

something i noticed about the sorting problem is that not all modules suffer from it

as far as i can tell vud does suffer from it (for comments and nodes equal)
http://drupal.org/project/vote_up_down

then there is UpDown, which works fine when you sort its votes
http://drupal.org/project/updown

the *curious* thing is that if you have vud and then install UpDown for comments (just install, dont even need to configure anything else), new vud votes start getting sorted fine in the view, as they are now assigned a 0 instead of nothing/null

so it seems i fixed my issue in a strange way, but updown is being deprecated so it will not be an option soon.

maybe fixing the issue in votingapi itself is the best way to go, so that all these voting modules behave correctly, instead of each having a different way of handling things

do you think the performance impact with your fix could be very noticeable?

anyway, thanks for looking into this and let us know if you need testers.

Yeah, from the looks of it, updown was inserting zero votes automatically whenever new nodes were created. vote_up_down wasn't, and thus unvoted content had null aggregate values.

I'm still a little twitchy about the performance implications: that's why I've included a toggle for it in the sort config screen. The one place where it CAN'T be toggled off right now is the click-sorting for table views when the field is in place. I may add another toggle there; I don't like littering the settings all over, but I'd hate to have this fix result in much slower sort performance for the folks who are using it on large data sets.

ok cool

well the issue is just for negative votes and 0/null, so only those people toggling negative voting on content actually need it (just those using vud module and views)

i wont even be using the vote sorted view by default, only if people optionally click on a tab/button to see them. I guess in my case it wont be much of a problem.

if a webmaster only wants to show the top positive voted, then they wont need to use the fix or have performance issues

maybe a small warning in the readme

Im also experiencing this on the latest head versions of votingapi and vote_up_down at the time of this writing.

As per robobeau's comment #12 above I was after speed and simply modified his code to fit my version:

Voting API 6.x-2.3
Vote Up/Down 6.x-2.4
Sort Criteria - Vote Results
- relationship = Vote results
- Sort order = Descending
Fields - Vote results
- Relationship = Vote results

Added at the end of vud.module in the root vote_up_down folder:

function vud_views_query_alter(&$view, &$query) {
    $query->fields['votingapi_cache_node_sum_value']['table'] = 'IFNULL(votingapi_cache_node_sum';
    $query->fields['votingapi_cache_node_sum_value']['field'] = 'value, 0)';
}

Hmmm, a side effect of the previous post was that all my comments attached to nodes disappeared when viewing a node.
As a quick fix I have had to determine the view being called (in this case a custom view called 'ideas_view' I have created) and ONLY if it is that then add the sorting fix.
Note it was failing above as it appears that when on a node that has comments attached it will run through this function and give a views->name of 'nodecomments'.

function vud_views_query_alter(&$view, &$query) {
    if($view->name == 'ideas_view'){
        $query->fields['votingapi_cache_node_sum_value']['table'] = 'IFNULL(votingapi_cache_node_sum';
        $query->fields['votingapi_cache_node_sum_value']['field'] = 'value, 0)';
    }
}

or if you want everything else to sort correctly but omit nodecomments then:

function vud_views_query_alter(&$view, &$query) {
    if($view->name != 'nodecomments'){
        $query->fields['votingapi_cache_node_sum_value']['table'] = 'IFNULL(votingapi_cache_node_sum';
        $query->fields['votingapi_cache_node_sum_value']['field'] = 'value, 0)';
    }
}

Also note I moved the code to:
'vote_up_down/vud_node/vud_node.module'
as it seems a better location than in the core module file.

Hey all,

I would like fix this for comments, but I cannot find enough documentation about votingapi_cache to change the code for my use. Anyone have an idea on how to do this?

I am still experiencing this problem with negative values ranking above "0". Has anyone found a solution?

I modified the function above to the following in order to get it to work for me.

function vud_views_query_alter(&$view, &$query) {
$query->fields['votingapi_cache_node_points_vote_sum_value']['table'] = 'IFNULL(votingapi_cache_node_points_vote_sum';
$query->fields['votingapi_cache_node_points_vote_sum_value']['field'] = 'value, 0)';
}

I hate to add to a year old thread, but I thought I'd share how I fixed this for a slightly different use case. I'm a drupal newbie, so if there is a better way I should handle this, please PM me. I'm trying to learn. Thanks.

I am using VotingAPI, Vote Up/Down and Voting Rules. I am assigning user points to the node author depending on voting results. I needed to be able to get previous value, and have it be initialized as zero rather than null, for my logic to work.

I applied patches by iStryker in #4 here http://drupal.org/node/1882086 and then just needed previous_vote to be zero instead of null. I solved this by editing votingapi.module around line 175 by adding a foreach, resulting in...

// Cast the actual votes, inserting them into the table.
foreach ($votes as $vote) {
if(!isset($vote['previous_value'])){
$vote['previous_value'] = 0;
}
}
votingapi_add_votes($votes);

Now, upon the first vote, the previous_vote column in the database gets zero rather than null.

Hi jcsnyder,

As stated in the other bug report I won't support the "previous_vote" patches. Can you really reproduce the bug from the original report with 7.x-2.10 ?

Is this still reproducible in 6.x-2.x?

I just tried the following trick (like others before me)...

  $query->fields['votingapi_cache_node_sum_value']['table'] = 'IFNULL(votingapi_cache_node_sum';
  $query->fields['votingapi_cache_node_sum_value']['field'] = 'value, 0)';

but the SQL query generated this for the field...

IFNULLvotingapi_cache_node_sum.value0 AS votingapi_cache_node_sum_value

It seems that some filter removed the '(' , the ',' and the ')' from the field, much to my chagrin. Is there anyway to avoid this?

I am trying this in Drupal 7.

In the same D7 views_query_alter hook was this...

[node_comment_statistics_last_updated] => Array
(
  [field] => GREATEST(node.changed, node_comment_statistics.last_comment_timestamp)
  [table] => 
  [alias] => node_comment_statistics_last_updated
)

I also tried the following...

$query->fields['votingapi_cache_node_sum_value']['field'] = IFNULL(votingapi_cache.sum, 0);

and

$query->fields['votingapi_cache_node_sum_value']['field'] = COALESCE(votingapi_cache.sum, 0);

to no avail. Suggestions anyone?

For Drupal 6
The code like this didn't work for me either, it doesn't work to change the existing field but it works to add a field.

So my code, for comments voting looks like this and it works for me

function module_views_query_alter(&$view, &$query) {
  if(
    $view->name == '....' &&
    in_array($view->current_display, array("...", "..."))
  ){
    $query->fields['votingapi_cache_comments_points_vote_sum_altered_value']['table'] = '';
    $query->fields['votingapi_cache_comments_points_vote_sum_altered_value']['alias'] = 'votingapi_cache_comments_points_vote_sum_altered_value';
    $query->fields['votingapi_cache_comments_points_vote_sum_altered_value']['field'] = 'IFNULL(votingapi_cache_comments_points_vote_sum.value, 0)';
    $query->orderby[0] = 'votingapi_cache_comments_points_vote_sum_altered_value DESC';
  }
}

So the other code would probably look something like

    $query->fields['votingapi_cache_node_sum_value_altered']['table'] = '';
    $query->fields['votingapi_cache_node_sum_value_altered']['alias'] = 'votingapi_cache_node_sum_value_altered';
    $query->fields['votingapi_cache_node_sum_value_altered']['field'] = 'IFNULL(votingapi_cache_node_sum.value, 0)';
    $query->orderby[0] = 'votingapi_cache_node_sum_value_altered DESC';

Drupal 6 is no longer supported. Closing old issues to clean up the issue queue.

Please reopen and update this issue if this is still an issue in the D7 or D8 version.