All of my views-lists are showing multiple items of the same node - in this case, blog entries. I believe it has to do with the caching associated with VotingAPI since the lists are sorted by votes - and the multiple entries will display because the exact same entries will have different vote numbers. I am using the Plus1 module to tally votes. I was going to post this in the Views queue but thought I would post it here first in case it was a VotingAPI issue. Thanks so much for anyone's help with this. I really would appreciate some help in resolving this issue as quickly as possible.

When I try to select Distinct on all these lists, I am given the following error:

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(node.nid), users.name AS users_name, users.uid AS users_uid, n' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), users.name AS users_name, users.uid AS users_uid, node.title AS node_title, votingapi_cache_node_points_vote.value AS votingapi_cache_node_points_vote_value, node.created AS node_created FROM node node LEFT JOIN votingapi_cache votingapi_cache_node_points_vote ON node.nid = votingapi_cache_node_points_vote.content_id AND (votingapi_cache_node_points_vote.content_type = 'node' AND votingapi_cache_node_points_vote.value_type = 'points' AND votingapi_cache_node_points_vote.tag = 'vote') INNER JOIN term_node term_node ON node.vid = term_node.vid INNER JOIN users users ON node.uid = users.uid WHERE (term_node.tid = 28) AND (node.type in ('blog')) ORDER BY votingapi_cache_node_points_vote_value DESC LIMIT 0, 10 in /home/forens7/public_html/modules/views/includes/view.inc on line 731.

Here's the Query from the live preview:

SELECT node.nid AS nid, users.name AS users_name, users.uid AS users_uid, node.title AS node_title, votingapi_cache_node_points_vote.value AS votingapi_cache_node_points_vote_value, node.created AS node_created FROM node node LEFT JOIN votingapi_cache votingapi_cache_node_points_vote ON node.nid = votingapi_cache_node_points_vote.content_id AND (votingapi_cache_node_points_vote.content_type = 'node' AND votingapi_cache_node_points_vote.value_type = 'points' AND votingapi_cache_node_points_vote.tag = 'vote') INNER JOIN term_node term_node ON node.vid = term_node.vid INNER JOIN users users ON node.uid = users.uid WHERE (term_node.tid = 28) AND (node.type in ('blog')) ORDER BY votingapi_cache_node_points_vote_value DESC

Here's an export of the view:


$view = new view;
$view->name = 'Best_LD_Blogs';
$view->description = 'The most recommended blogs within the L-D category. ';
$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' => 'Voting results',
    'required' => 0,
    'votingapi' => array(
      'value_type' => 'points',
      'tag' => 'vote',
      'function' => '',
    ),
    'id' => 'votingapi_cache',
    'table' => 'node',
    'field' => 'votingapi_cache',
    'relationship' => 'none',
  ),
));
$handler->override_option('fields', array(
  'name' => array(
    'label' => 'Name',
    'link_to_user' => 1,
    'exclude' => 0,
    'id' => 'name',
    'table' => 'users',
    'field' => 'name',
    'relationship' => 'none',
  ),
  'title' => array(
    'label' => 'Title',
    'link_to_node' => 1,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
  'value' => array(
    'label' => 'Votes',
    'set_precision' => FALSE,
    'precision' => 0,
    'decimal' => '.',
    'separator' => ',',
    'prefix' => '',
    'suffix' => '',
    'appearance' => '',
    'exclude' => 0,
    'id' => 'value',
    'table' => 'votingapi_cache',
    'field' => 'value',
    'relationship' => 'votingapi_cache',
  ),
  'created' => array(
    'label' => 'Post date',
    'date_format' => 'small',
    'custom_date_format' => '',
    'exclude' => 0,
    'id' => 'created',
    'table' => 'node',
    'field' => 'created',
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'tid' => array(
    'operator' => 'or',
    'value' => array(
      '28' => '28',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'type' => 'select',
    'vid' => '2',
    'id' => 'tid',
    'table' => 'term_node',
    'field' => 'tid',
    'hierarchy' => 0,
    'relationship' => 'none',
    'reduce_duplicates' => 0,
  ),
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'blog' => 'blog',
    ),
    '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('title', 'Lincoln Douglas Debate');
$handler->override_option('header', '<br> 

<h2><table> <tr> <td> <strong> Best L-D Blogs </strong></td> <td> <a href="http://www.forensicscommunity.com/forum/2"> Discuss L-D Debate </a></td>  </table> </h2> <br> 

');
$handler->override_option('header_format', '2');
$handler->override_option('header_empty', 1);
$handler->override_option('footer', '<br> <br> 

<br> <br> <center> <h2> <b> Lincoln-Douglas Debate Videos </b> </h2> <br> </center> 

<center>
<?php
   $params[\'width\'] = 652;
   $params[\'height\'] = 432;
   $params[\'playlist\'] = \'ld_videos\';
   print dashplayer_get_player($params);
 </center>
');
$handler->override_option('footer_format', '3');
$handler->override_option('footer_empty', 1);
$handler->override_option('distinct', 0);
$handler->override_option('style_plugin', 'table');
$handler->override_option('style_options', array(
  'grouping' => '',
  'override' => 1,
  'sticky' => 0,
  'order' => 'desc',
  'columns' => array(
    'name' => 'name',
    'title' => 'title',
    'value' => 'value',
    'created' => 'created',
  ),
  'info' => array(
    'name' => array(
      'sortable' => 0,
      'separator' => '',
    ),
    'title' => array(
      'sortable' => 0,
      'separator' => '',
    ),
    'value' => array(
      'sortable' => 0,
      'separator' => '',
    ),
    'created' => array(
      'sortable' => 0,
      'separator' => '',
    ),
  ),
  'default' => 'value',
));
$handler = $view->new_display('block', 'Block', 'block_1');
$handler->override_option('block_description', '');
$handler->override_option('block_caching', -1);
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'lincolndouglas');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
  'name' => 'navigation',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
));

Comments

eaton’s picture

The Votingapi relationship you set up is pulling in all result calculations, which is leading to the double results. See the following:

$handler->override_option('relationships', array(
  'votingapi_cache' => array(
    'label' => 'Voting results',
    'required' => 0,
    'votingapi' => array(
      'value_type' => 'points',
      'tag' => 'vote',
      'function' => '',    <--- AWOOGA! AWOOGA!
    ),
    'id' => 'votingapi_cache',
    'table' => 'node',
    'field' => 'votingapi_cache',
    'relationship' => 'none',
  ),
));

That 'function' should be something like 'count' or 'average' or something along those lines.

eaton’s picture

Category: bug » support
Status: Active » Fixed

Setting this to a support request, as it's the way the relationship works.

Anonymous’s picture

Hey Eaton,

Thank you soo sooo much. I set my Note Voting API Relationship to count and that solved the problem.

I thought it was a deeper issue but am happily relieved!!

Just out of curiosity, this probably sounds like a stupid question, but do you know the difference between - count, sum and average - for those fields? How is the functional result different?

Thanks again. I really appreciate it. Feel free to close this issue.

best,
Michael

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.