VotingAPI/Views Integration - something's wrong?

We're seeing something similar. In our case it's a block with some data from the voting API where sometimes the query returns no results and sometimes it returns the right results, as if some cache were being invalidated out from under us and not refilled at page load time.

I'll try to get some more details, but it's good of you to point at the voting api cache as a possible source of this problem.

Two comments -- you're right in that the VotingAPI isn't currently displaying un-voted nodes when it's added to a View. I can put in some additional handling code that outputs 'NULL' values as empty strings. Also, you're correct that the current approach doesn't allow 'completely unfiltered' uses of the field. I'll see what I can do about fixing that.

I can confirm that VotingAPI/Views is a bit wacky. I have DRUPAL-4-7 Views, VotingAPI, and Vote Up Down modules.

A) When sorting by "Voting result" I get duplicates of each node (Distinct can fix this, but still bizarre and might point out some existing prob.)
B) When sorting by "Vote value", it sorts the nodes with votes before those without...(Node voted -1)(Node voted 1)(Node without a vote)(Node without a vote)(Node without a vote)(Node without a vote)...

If I knew more about ViewsAPI I'd help, but I'm still working on that.

RobRoy,

A couple quick bits about what you saw. The reason you see multiple results when sorting by vote results is this: for every node, VotingAPI actuall stores several result records: the total number of voters, the average vote, etc. Views is displaying a copy of the node for each one of those stored values. The solution to this is updating the 'sort' selection code to add the same kind of filtering that the 'field' selection now uses.

The reason nodes with no votes are sorting oddly is because the vote value they return is 'null.' It sounds like you want it to sort as if it were a 'zero'. That should be possible view some views magic, but it requires some looking into.

For some insights into how VotingAPI stores its data (and hints at why integrating it with views can be tricky), check http://drupal.org/node/68857 (VotingAPI data structure).

Still need to give this issue more attention when I have a chance. :)

@Eaton Thanks man. I'm really seeing the potential for the VotingAPI on my sites. Getting a grasp on the ViewsAPI is the next step in my Drupal enlightenment and once I get a hang of that I'll see if I can apply that to the NULL vote issue. (I know MySQL has COALESCE(), but that's a DB-specific solution.) Hopefully, I can finally help out some on the VotingAPI!

I have too have a view that includes "Average vote value". Similarly, only those nodes that have a vote are shown.

Was there ever a fix or patch released to return NULL (or zero) so that these nodes can be viewed as well or is that still coming?

Ok, I went ahead and made a minor change to votingapi_views.inc on line 184 in function votingapi_views_value_query_handler() so that it would return nodes where there are no votes. Here's the change:

old: $query->add_where("$table.$column = '$value'");
new: $query->add_where("($table.$column = '$value') OR ($table.$column is NULL)");

votingapi_views_value_query_handler() is used in two places: once when choosing "Voting Results" and the other when choosing "Individual vote value". I only tested it with "Voting Results". I'm presuming the results will be the same (nodes with no votes will still show up) but I haven't tested it.

I can confirm that the change in #7 worked for listing all nodes.

I too am having problems with VotingAPI/Views Integration.

1. I can not create a view with both the voting averge and vote count (# of votes for the given node). Seperately works fine.

2. When I filter by 'VotingAPI: Current user has voted' I get a this mess...

    * user warning: Unknown table 'votingapi_vote' in where clause query: SELECT count(node.nid) FROM node node LEFT JOIN votingapi_cache votingapi_cache ON node.nid = votingapi_cache.content_id AND votingapi_cache.content_type = 'node' WHERE (votingapi_vote.uid = '2') AND (votingapi_cache.value_type = 'percent') AND (votingapi_cache.tag = 'vote') AND (votingapi_cache.function = 'average') in /home/openbior/public_html/run/includes/database.mysql.inc on line 121.
    * user warning: Unknown table 'votingapi_vote' in where clause query: SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.type AS node_type, votingapi_cache.value AS votingapi_cache_value, votingapi_cache.value_type AS votingapi_cache_value_type, votingapi_cache.tag AS votingapi_cache_tag, votingapi_cache.function AS votingapi_cache_function, votingapi_cache.content_type AS votingapi_cache_content_type, votingapi_cache.content_id AS votingapi_cache_content_id FROM node node LEFT JOIN votingapi_cache votingapi_cache ON node.nid = votingapi_cache.content_id AND votingapi_cache.content_type = 'node' WHERE (votingapi_vote.uid = '2') AND (votingapi_cache.value_type = 'percent') AND (votingapi_cache.tag = 'vote') AND (votingapi_cache.function = 'average') ORDER BY node.title ASC LIMIT 0, 30 in /home/openbior/public_html/run/includes/database.mysql.inc on line 121.

Any ideas out there? Many thanks to anyone that has the skill and motivation to help out!

same problem as NathanRAFT with 'VotingAPI: Current user has voted', but i reported it at the parallel issue at http://drupal.org/node/68718#comment-159997

what i get is:

* user warning: Unknown table 'votingapi_vote' in where clause query: SELECT count(node.nid) FROM node node WHERE (node.status = '1') AND (votingapi_vote.uid = '1') in /public_html/drupal/includes/database.mysql.inc on line 120.
* user warning: Unknown table 'votingapi_vote' in where clause query: SELECT node.nid, node.title AS node_title, node.changed AS node_changed FROM node node WHERE (node.status = '1') AND (votingapi_vote.uid = '1') ORDER BY node.created DESC LIMIT 0, 15 in /public_html/drupal/includes/database.mysql.inc on line 120.

shall we set this as 'critical', as seems many issues are arising with Views integration?

+1: confirm that the solution at #7 above solved the "no itam displayed" issue.

i attach the small patch

Sorting works now.
Thank you all! Post #4 helped me to solve the problem of wrong sorting in the view. I had to add the filter "VotingAPI: Result function" is "average vote value". This way it only uses the 'average' entries and not the 'count' entries. (Before it sometimes sorted by 'count' if this line appeared before the 'average' line in the database.)

I am opening this back up.. kinda. The provided in comment #11 patch keeps errors from showing but restricts to only those nodes that have not been voted on.

So here is the scenerio. Create a view with two voting results fields. 1) percent, rating, average vote value. 2) percent, rating, Number of votes.

They work just fine seperately but not together. When combined only nodes not voted on show. (would be nice to show users how much weight to put on a average vote.

p.s. Would also be cool if I could get the sum votes to work or some other sort of measure for vote impact score.

+1 on comments #7 / #11 for fixing the "no show on views" problem.

I second the idea that Voting API might not be working with Views.

I have numerous nodes - some with votes, some without. Whenever i seem to add any sort of VotingAPI filter i get no results. And if i remove filter i also can not sort on voting results (but maybe since i have no field for it???)

sorry.. i meant fields not filter in post above.

had sort of hoped someone would have figured this out by now but still seems like votingapi and views do not work together??? is this a correct statement?

although i guess it might be possible to include vote value in a view it seems as though it isn't possible to sort on the vote results

i have tried what i think are all the possible combinations of voting fields and sort options and results, although they do seem to differ from test to test.. seem to be pretty much random (although i am sure there is some logic to what it is doing)

i guess options now are to either fix it myself (scary thought) or write a custom block to do the same thing as what i was trying to do with a view.

so a few nudges from rseiser and i decided i should start to look into where this bug actually is.. and sure enough it didnt take to long.. well at least to get this far...

just a quick recap.. as far as i can tell the view blocks (and from what rseiser is suggesting maybe it does work with page views???) do not work with the votingapi module.. and here's why

this is the query that the views module gets handed... i suspect from the votingapi module but possibly from the voting module ( i havent looked that deep yet).

SELECT node.nid, votingapi_cache.value AS votingapi_cache_value, node.title AS node_title, 
node.changed AS node_changed, node_counter.totalcount AS node_counter_totalcount,
 node_comment_statistics.comment_count AS node_comment_statistics_comment_count, node.nid AS node_nid,
 event.event_start AS event_event_start, event.event_end AS event_event_end, 
event.timezone AS event_timezone 
FROM node node  LEFT JOIN votingapi_cache votingapi_cache ON node.nid = votingapi_cache.content_id AND 
votingapi_cache.content_type = 'node' 
LEFT JOIN node_counter node_counter ON node.nid = node_counter.nid 
LEFT JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid
 LEFT JOIN event event ON node.nid = event.nid WHERE (node.type IN ('content_resource')) AND
 (votingapi_cache.function = 'average' OR 
votingapi_cache.function IS NULL)  ORDER BY votingapi_cache_value DESC

the problem is that in line 7 here: votingapi_cache.content_type = 'node' my content type is not 'node' rather it is my cck type ('content_resource' in my case).

since later on the query is correct in using the right type but at that point the votingapi_cache_value 's are all NULL (since my votingapi_cache table has no 'node' types in it) the sort order is pretty much random.

.. hopefully will sort out where the actual bug starts in a bit.. and will post the solution.

Peter Lindstrom
LiquidCMS - Content Management Solution Experts

ok, well my issue seems to have been my confusion on using the voting modules api.

i was using the command:

$vote_widget = voting_control_generic('content_myckktype',$nid, array ('mode'=>'show_avg_only'));  

to place the flash voting widget in various places throughout my site.

This causes the voting api cache table to have entries with a type of 'content_myckktype' which is incorrect.. they should be 'node'

so the correct code to use is:

$vote_widget = voting_control_generic('node',$nid, array ('mode'=>'show_avg_only'));  

and now i can sort...

perhaps the voting module could add a little mention that their variable $content_type" isn't what i think most of us think of as content type - i.e. page, blog, content_ccktype, etc but i think it is basically 'node' or 'comment'.

it appears like the Current User has Voted filter has not been fixed yet, correct? Does anyone have any idea why I am getting these errors when using it..

* user warning: Unknown column 'votingapi_vote.uid' in 'where clause' query: SELECT count(DISTINCT(node.nid)) FROM node node WHERE (node.type IN ('article','weblink')) AND (votingapi_vote.uid = '1') in /var/www/includes/database.mysql.inc on line 121.
* user warning: Unknown column 'votingapi_vote.uid' in 'where clause' query: SELECT DISTINCT(node.nid), node.created AS node_created_created FROM node node WHERE (node.type IN ('article','weblink')) AND (votingapi_vote.uid = '1') GROUP BY node.nid ORDER BY node_created_created DESC LIMIT 0, 10 in /var/www/includes/database.mysql.inc on line 121.

I am expanding on comment #18...

I recently noticed that my 'Most Popular' view page..which sorts descending for voting result..no longer works at all...And i am pretty sure it worked before..

I am using one custom-made node type; article (replica of story) - and one from the links modules; weblink ..

The view spits out nodes in random order, no where near in order of votes..

And since my site is suppose to launch very soon - i might just have to kill myself if this can't be resolved..

Do i have to edit the sql query like posted in #18? What is the solution here?

THANKS

I forgot to add: I am not 100% sure but very close to it that sorting by vote value like trying to do before, worked in block view. The page view is not working at all. (i could be mistaken about this).

I take that back - nothing works...

Should I submit this as a new issue?

OK I GOT IT...you need to add the filter 'Voting API: Result Function' ...

i really hope i am not getting excited over common knowledge...anyway i think prevented a heart attack..

howdy, could any savvy have a look also at another VotingAPI/Views integration problem at http://drupal.org/node/103054 ?

it has been around since a while but in the wrong queue (Vote_up_down.module), maybe progresses here can benefit also there?

thx

As of this evening at 2:06 AM, views integration for VotingAPI has been rewritten from scratch. All hail views! The good news? It should actually work. The bad news? You'll need to remove and re-add any VotingAPI fields that you had on existing Views. This shouldn't be problematic, as the old views integration was completely broken anyhow.

Caveats: Output formatting of vote results, etc is still being finished. In addition, much more testing is needed before it can be said that the new integration code is 'ready for prime time.' But it's definitely much better. I'm going to be closing the existing threads regarding VotingAPI and Views because all of them point to known issues with the *old* versions of the code. Those interested in testing the new code can download the latest VotingAPI from: http://drupal.org/node/128605

Thanks for your patience!