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
Comment #1
eaton commentedThe Votingapi relationship you set up is pulling in all result calculations, which is leading to the double results. See the following:
That 'function' should be something like 'count' or 'average' or something along those lines.
Comment #2
eaton commentedSetting this to a support request, as it's the way the relationship works.
Comment #3
Anonymous (not verified) commentedHey 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