Hello,
I have a view with block as "List view" and page as "Teaser List", filters on "Node:Type", "VotingAPI percent vote result (count)" and "VotingAPI: Average vote", sorted by "VotingAPI: Vote value" and "VotingAPI percent vote result (count)".

This view displays twice or three times th same node in block and page views.

Is this a bug or don't I use Views module correctly?
Please see the view export below:

$view = new stdClass();
$view->name = 'more_rated_assets';
$view->description = 'Most rated assets'; 
$view->access = array ( );
$view->view_args_php = ''; 
$view->page = TRUE; 
$view->page_title = 'Most Rated'; 
$view->page_header = ''; 
$view->page_header_format = '2'; 
$view->page_footer = ''; 
$view->page_footer_format = '3'; 
$view->page_empty = ''; 
$view->page_empty_format = '1'; 
$view->page_type = 'teaser'; 
$view->url = 'assets/rated'; 
$view->use_pager = TRUE; 
$view->nodes_per_page = '20'; 
$view->block = TRUE; 
$view->block_title = 'Most Rated'; 
$view->block_header = ''; 
$view->block_header_format = '1'; 
$view->block_footer = ''; 
$view->block_footer_format = '1'; 
$view->block_empty = ''; 
$view->block_empty_format = '1'; 
$view->block_type = 'list'; 
$view->nodes_per_block = '7'; 
$view->block_more = TRUE; 
$view->block_use_page_header = FALSE; 
$view->block_use_page_footer = FALSE; 
$view->block_use_page_empty = FALSE; 

$view->sort = array ( array ( 'tablename' => 'jrating_votingapi_vote', 'field' => 'value', 'sortorder' => 'DESC', 'options' => '', ), array ( 'tablename' => 'votingapi_cache_vote_percent_count', 'field' => 'value', 'sortorder' => 'DESC', 'options' => '', ), );

$view->argument = array ( array ( 'type' => 'rss_feed', 'argdefault' => '2', 'title' => '', 'options' => '', 'wildcard' => '', 'wildcard_substitution' => '', ), );

$view->field = array ( array ( 'tablename' => 'node', 'field' => 'title', 'label' => '', 'handler' => 'views_handler_field_nodelink', 'options' => 'link', ), array ( 'tablename' => 'jrating_votingapi_cache', 'field' => 'value', 'label' => '', ), array ( 'tablename' => 'votingapi_cache_vote_percent_count', 'field' => 'value', 'label' => '', 'handler' => 'votingapi_views_formatter_raw', ), );

$view->filter = array ( array ( 'tablename' => 'node', 'field' => 'type', 'operator' => 'OR', 'options' => '', 'value' => array ( 0 => 'asset', ), ), array ( 'tablename' => 'votingapi_cache_vote_percent_count', 'field' => 'value', 'operator' => '>', 'options' => '', 'value' => '3', ), array ( 'tablename' => 'jrating_votingapi_cache', 'field' => 'value', 'operator' => '>=', 'options' => '', 'value' => '80', ), array ( 'tablename' => 'node', 'field' => 'distinct', 'operator' => '=', 'options' => '', 'value' => array ( 0 => 'distinct', ), ), );

$view->exposed_filter = array ( );
$view->requires = array(jrating_votingapi_vote, votingapi_cache_vote_percent_count, node, jrating_votingapi_cache);
$views[$view->name] = $view;

Hope this can be corrected...

Thank you,
David

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

yched’s picture

Status: Active » Fixed

The "Node: Distinct" filter is usually the answer.

yched’s picture

Ah, sorry, you seem to be using that filter already.
Then I guess this is a bug (?) in the Views elements (fields / filters) exposed by Voting API...
You should probably try to narrow the bug down to one of the fields or filters, and then submit a bug for Voring API ?

David Stosik’s picture

Hello,
Yes I tried to add "Node: distinct" and it did not resolved the problem...
Removing one or the other Voting API filter did not resolve the problem either...
Removing those two filters has no better result: I still have a duplicate entry in my lists.

David Stosik’s picture

Keeping fields and filters, and removing all sort criteria but Random sort solved my problem. And it's in fact the kind of view I wanted to have.
I think there's however some kind of bug in interraction between Views module and the voting API (but I don't know how to update the issue...).

Anonymous’s picture

Status: Fixed » Closed (fixed)
jazzitup’s picture

Version: 5.x-1.6-beta5 » 5.x-1.6
Status: Closed (fixed) » Active

Confirmed! I spotted the exact same problem, but I don't have Voting API installed at all.
I just wanted to display a list of "recent (x) nodes (node title, total comments, date created and name of the author) of a certain node-type ordered by time created descending".
I've seen duplicate entries on the output for those nodes that have several comments (I'm not sure if the last fact I've just mentioned is related somehow to this issue).

David Stosik’s picture

Hello,
I'm still experiencing this bug. I remarked that it occurs only when I am member af the og group containing the node, and I am not admin user.
Still no idea?

Regards,
David

sdelbosc’s picture

I work with David and we are now able to provide more detailed explanations on our problem. At first sight, it seems to be a bug of MySQL.

Putting logs in modules/views/views.module gave the following results.

Line 557 (after _views_get_query):

SELECT
	node.nid,
	rand() AS random_sort,
	node.title AS node_title,
	node.changed AS node_changed,
	votingapi_cache_vote_percent_count.value AS votingapi_cache_vote_percent_count_value,
	jrating_votingapi_cache.value AS jrating_votingapi_cache_value,
	jrating_votingapi_cache.value_type AS jrating_votingapi_cache_value_type,
	jrating_votingapi_cache.tag AS jrating_votingapi_cache_tag,
	jrating_votingapi_cache.function AS jrating_votingapi_cache_function,
	jrating_votingapi_cache.content_type AS jrating_votingapi_cache_content_type,
	jrating_votingapi_cache.content_id AS jrating_votingapi_cache_content_id
FROM
	node node
LEFT JOIN
	votingapi_cache jrating_votingapi_cache
ON
	node.nid = jrating_votingapi_cache.content_id
	AND
	jrating_votingapi_cache.content_type = 'node'
	AND
	jrating_votingapi_cache.value_type = 'percent'
	AND
	jrating_votingapi_cache.tag = 'vote'
	AND
	jrating_votingapi_cache.function = 'average'
LEFT JOIN
	votingapi_cache votingapi_cache_vote_percent_count
ON
	node.nid = votingapi_cache_vote_percent_count.content_id
	AND
	votingapi_cache_vote_percent_count.content_type = 'node'
	AND
	votingapi_cache_vote_percent_count.value_type = 'percent'
	AND
	votingapi_cache_vote_percent_count.tag = 'vote'
	AND
	votingapi_cache_vote_percent_count.function = 'count'
WHERE
	(node.type IN ('asset'))
	AND
	(jrating_votingapi_cache.value >= '80')
	AND
	(votingapi_cache_vote_percent_count.value > 3)
ORDER BY
	random_sort ASC
;

This SQL request returns only one result.

Line 567 (after db_rewrite_sql):

SELECT
	DISTINCT(node.nid),
	rand() AS random_sort,
	node.title AS node_title,
	node.changed AS node_changed,
	votingapi_cache_vote_percent_count.value AS votingapi_cache_vote_percent_count_value,
	jrating_votingapi_cache.value AS jrating_votingapi_cache_value,
	jrating_votingapi_cache.value_type AS jrating_votingapi_cache_value_type,
	jrating_votingapi_cache.tag AS jrating_votingapi_cache_tag,
	jrating_votingapi_cache.function AS jrating_votingapi_cache_function,
	jrating_votingapi_cache.content_type AS jrating_votingapi_cache_content_type,
	jrating_votingapi_cache.content_id AS jrating_votingapi_cache_content_id
FROM node node
LEFT JOIN votingapi_cache jrating_votingapi_cache
ON
	node.nid = jrating_votingapi_cache.content_id
	AND
	jrating_votingapi_cache.content_type = 'node'
	AND
	jrating_votingapi_cache.value_type = 'percent'
	AND
	jrating_votingapi_cache.tag = 'vote'
	AND
	jrating_votingapi_cache.function = 'average'
LEFT JOIN votingapi_cache votingapi_cache_vote_percent_count
ON
	node.nid = votingapi_cache_vote_percent_count.content_id
	AND
	votingapi_cache_vote_percent_count.content_type = 'node'
	AND
	votingapi_cache_vote_percent_count.value_type = 'percent'
	AND
	votingapi_cache_vote_percent_count.tag = 'vote'
	AND
	votingapi_cache_vote_percent_count.function = 'count'
INNER JOIN node_access na
ON
	na.nid = node.nid
WHERE
	(na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'og_public') OR (na.gid = 1036 AND na.realm = 'og_subscriber') OR (na.gid = 1038 AND na.realm = 'og_subscriber') OR (na.gid = 1056 AND na.realm = 'og_subscriber') OR (na.gid = 297 AND na.realm = 'og_subscriber')))
	AND
	( (node.type IN ('asset')) AND (jrating_votingapi_cache.value >= '80') AND (votingapi_cache_vote_percent_count.value > 3)  )
ORDER BY
	random_sort ASC
;

This SQL request returns two results with the same nid.

If we change the last request to:

SELECT
	DISTINCT(node.nid),
	node.title AS node_title,
	node.changed AS node_changed,
	votingapi_cache_vote_percent_count.value AS votingapi_cache_vote_percent_count_value,
	jrating_votingapi_cache.value AS jrating_votingapi_cache_value,
	jrating_votingapi_cache.value_type AS jrating_votingapi_cache_value_type,
	jrating_votingapi_cache.tag AS jrating_votingapi_cache_tag,
	jrating_votingapi_cache.function AS jrating_votingapi_cache_function,
	jrating_votingapi_cache.content_type AS jrating_votingapi_cache_content_type,
	jrating_votingapi_cache.content_id AS jrating_votingapi_cache_content_id
FROM node node
LEFT JOIN votingapi_cache jrating_votingapi_cache
ON
	node.nid = jrating_votingapi_cache.content_id
	AND
	jrating_votingapi_cache.content_type = 'node'
	AND
	jrating_votingapi_cache.value_type = 'percent'
	AND
	jrating_votingapi_cache.tag = 'vote'
	AND
	jrating_votingapi_cache.function = 'average'
LEFT JOIN votingapi_cache votingapi_cache_vote_percent_count
ON
	node.nid = votingapi_cache_vote_percent_count.content_id
	AND
	votingapi_cache_vote_percent_count.content_type = 'node'
	AND
	votingapi_cache_vote_percent_count.value_type = 'percent'
	AND
	votingapi_cache_vote_percent_count.tag = 'vote'
	AND
	votingapi_cache_vote_percent_count.function = 'count'
INNER JOIN node_access na
ON
	na.nid = node.nid
WHERE
	(na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'og_public') OR (na.gid = 1036 AND na.realm = 'og_subscriber') OR (na.gid = 1038 AND na.realm = 'og_subscriber') OR (na.gid = 1056 AND na.realm = 'og_subscriber') OR (na.gid = 297 AND na.realm = 'og_subscriber')))
	AND
	( (node.type IN ('asset')) AND (jrating_votingapi_cache.value >= '80') AND (votingapi_cache_vote_percent_count.value > 3)  )
ORDER BY
	rand()
;

we get only one result. Notice that we have only change the way of using rand().

Our MySQL version is : mysql Ver 14.12 Distrib 5.0.27, for redhat-linux-gnu (i686) using readline 5.0

sdelbosc’s picture

Status: Active » Needs review
FileSize
500 bytes

We have so decided to modify modules/views/modules/views_node.inc like this:

function views_handler_sort_random($action, &$query, $sortinfo, $sort) {
  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':


// Replacement of:
//      $query->add_orderby('', "rand()", "ASC", 'random_sort');
//
// by:
      $query->orderby[] = 'rand()';


      break;
    case 'pgsql':
      $query->add_orderby('', "random()", "ASC", 'random_sort');
      break;
  }
}
David Stosik’s picture

Can anyone answer, please?

David

joshuajabbour’s picture

FileSize
512 bytes

I'm having the same problem... sorting by random order doesn't honor the "node distinct" filter and creates duplicate entries in a view.

Since the above patch is not available, I recreated it below.

Summit’s picture

Subscribing!
greetings,
Martijn

jfall’s picture

Re: #9 - Nice!
I had same problem. None of the above conditions apply to my case (there were no comments on nodes, not integrating other modules, etc.)

Applied this patch (by hand) - works perfectly now. THANK-YOU sdelbosc

Lupin3rd’s picture

The proposed patch don't solve the problem for me at http://www.sabinaguzzanti.it/?q=area/musica

danimatielo’s picture

I am also having the same problem, using VotingAPI and Page views with teaser list. I have applied the patch but it didn´t work for me. Did someone have another idea how this could be solved? Thank you!

sun’s picture

Status: Needs review » Needs work

Patch seems not to apply the same fix for PostGres.

stacyh’s picture

I was having the same issue with a view showing nodes twice. I noticed it was directly related to the API voting field i choose to add to the view (results).

When you add that field there are a few drop down options that you need to select. If you don't you may get duplicates. (It even says that ignoring the options may cause duplicates to appear.)

Its a simple answer but that took me a while to find, so I thought I'd pass it on in case someone comes across this post again soon.

Good Luck!

chadwyck’s picture

Thanks! It pays to read the directions ;)

laken’s picture

Same issue for me - duplicate nodes with random sort, dupes disappear when that sort is removed. The supplied patch works for me. Can someone handle the postgres issue? Would be great to get this committed!

laken’s picture

Title: Duplicate entry displayed in a view » Random sort creates duplicate nodes in view result
matt_c’s picture

Hi,

I'm still having this issue when sorting by 'Search: Score' but just like everyone else, when I remove this sort, the duplicates are removed. Though this might make the view hard for users to use if the best results don't float to the top.

ferrangil’s picture

Title: Duplicate nodes in a view result » Random sort creates duplicate nodes in view result
Status: Active » Needs work

I have duplicated node being listed on views with no random order! I'm sorting them by rating (average) and then by nº of votes, to have the node which lots of 5's first, and then the one that only has one 5 for example.
Only some nodes are being duplicated... I can't remove the sort order, as this view is called "Ranking" so I must diplay the top rated nodes in order...
Here you can see the duplicates..
http://ibc.lynxeds.com/content/ranking/videos
I hope I could solve it soon...

Any suggestions?

UPDATE (5 hours later): Now, my views are not displaying duplicated nodes anymore... I cleared all caches and so on, but they were still there, duplicates... I don't understand why now everything seems to be all right (I didn't change anything on the views config).

jazzitup’s picture

Title: Random sort creates duplicate nodes in view result » Duplicate nodes in a view result
Status: Needs work » Active

I've also mentioned (comment #6) that I haven't used random ordered views as well, so I'm switching the title back to the much more suitable, general sense.

designeq’s picture

Title: Random sort creates duplicate nodes in view result » Duplicate nodes in a view result
Version: 5.x-1.6 » 6.x-2.3
Component: Code » User interface
Status: Needs work » Active

Thank you stacyh (#17)!!! This was driving me crazy and I spent hours trying to solve this.

To be more specific about the solution, in my case it was the Node: Voting Results setting in Relationships in my View. There are three data filters when you select that: Value Type, Vote Tag, and Aggregation Function. I found that I only had to set Aggregation Function to eliminate the duplicates. The different options produced different results in the FiveStar results. I found that Average worked best for me.

While you are correct that it does mention that duplicates may result if these are not set, this was pretty obscure and I'm sure I would have lost at least another week without your pointing this out.

sun’s picture

Version: 6.x-2.3 » 5.x-1.6
Component: User interface » Code

This issue is about Views 1. Views 2 for Drupal 6.x is a different piece of software.

joshuajabbour’s picture

Rerolled #11 with Postgres support (though untested)...

drupalninja99’s picture

the search: score field will make duplicates show up..not sure how to remove this...any ideas?

Harry Slaughter’s picture

Issue tags: +duplicate view results

do certain fields still cause duplicate results in views2?

borfast’s picture

Version: 5.x-1.6 » 6.x-2.5
Component: Code » Views Data

I am having the same issue - with Views 2 - when displaying a "Taxonomy term" field in a view that will show nodes that have more than one term assigned - the nodes appear once for every term.

I am using the Distinct option, have removed all sorting and filters, and I still get one node instance per each taxonomy term associated with it.

When adding the Taxonomy term field, there's a warning:

Note that using this can cause duplicate nodes to appear in views; you must add filters to reduce the result set.

but I don't see any filters that can help on this.

Has anyone been able to solve the problem without using some sort of hacks?

rcr1000’s picture

I was having this problem and just fixed it, but I'm not entirely sure why it worked. I have two date fields on my nodes, the default node: post date and a custom date field that I probably just need to get rid of anyway. I was using the custom field. I removed it and used the stock node: post date, and everything works fine now.

merlinofchaos’s picture

Version: 6.x-2.5 » 5.x-1.6

Please don't hijack issues.

borfast’s picture

merlinofchaos, I'm sorry, it wasn't my intention to hijack the issue, I thought I was doing the correct thing.

I thought that it was marked as 5.x-1.6 because no one had experienced the problem with 6.x-2.x, but since I did, I thought the correct thing to do was to move the issue up to the latest version.

Should a new issue be opened for Views 2?

merlinofchaos’s picture

There are already lots of issues about duplicate results, so there really isn't much value in opening an issue. THe fact is, the database doesn't always really respect DISTINCT so you can't rely on it to work. Which the help text in the distinct form says. If you're getting duplicate results, it is almost the result of the sort/field/filter you're using having a many::one relationship, and you probably need to find a different solution.

borfast’s picture

I'm not sure how my situation can be solved in another way: one node may have multiple terms associated with it and I need to do something with each of them.

Wouldn't it be possible to have views verify if it has more than one row for each node in the result set (when "Distinct" is checked) and merge the different values into one single node?

I described all this in more detail in the issue "Duplicate taxonomy terms" - http://drupal.org/node/345571#comment-1642710

brnnrc’s picture

Hi,
imho
Duplicate records are created by (left) join between node and term_data: if a node has two terms, you'll get two records for that node.

If those terms are in different vocabularies, you can filter by "vocabolary name" to select one record.
example:
vocab a: term x
vocab b: term y
node j: terms x,y

a view ordered by "term name" (for example) creates:
node j (term x)
node j (term y)

Add "vocabolary name = a" filter to the view and you'll get
node j, term x
no dupes!

Hope this helps.

jjchinquist’s picture

FileSize
2.48 KB

I had the exact same problem. I had the a view with the following scenario:
1) View with fields: NID, Node Title.
2) Sorted by random
3) 20 nodes displayed per page, 2000 records.

I believe this is the scenario that is being described above.
My problem is that rand() will generate a different random every page view so that a node that appears on the first page is also appearing on the second page. That is a problem for my customers.

My solution was to create the following module - see attached.
You must copy one file from this module to overwrite the handler function in views.

It seeds the rand() function of mysql (please someone do this correctly for me for Postgress) with a variable from the database. This value is updated by one every cron run, so that every cron run my 2000 records are newly mixed, and not every page turn. I have my cron currently running at 2x per hour and it works well (having tested lightly).

Test and let me know if this works for you so we can get a patch to views.

- Jeremy

drupov’s picture

subscribe

ThomasH’s picture

Unfortunately we don't use a random order and this doesn't help... Here's the query that's being generated by our views page... it might give insight to the issue.

SELECT 
	distinct(node.nid), 
	node.sticky AS node_sticky, 
	node_data_field_volgorde.field_volgorde_value AS node_data_field_volgorde_field_volgorde_value, 
	node_data_field_move_to_front.field_move_to_front_value AS node_data_field_move_to_front_field_move_to_front_value

	FROM node node

LEFT JOIN content_field_volgorde node_data_field_volgorde 
	ON node.vid = node_data_field_volgorde.vid
	
LEFT JOIN content_field_move_to_front node_data_field_move_to_front 
	ON node.vid = node_data_field_move_to_front.vid
	
WHERE (
node.status = '1'
)

AND (
node.type
IN (
'webform', 'advpoll_ranking', 'advpoll_binary', 'artikel', 'story'
)
)

GROUP BY 
	node.nid, 
	node_sticky, 
	node_data_field_volgorde_field_volgorde_value, 
	node_data_field_move_to_front_field_move_to_front_value
	
ORDER BY 
	node_sticky DESC , 
	node_data_field_volgorde_field_volgorde_value DESC , 
	node_data_field_move_to_front_field_move_to_front_value DESC
	
LIMIT 0 , 20

edit:
it seems that there is no real mysql bug nor is there a problem with views. it's just that the group by on the node_data_field_move_to_front_field_move_to_front_value had to be removed since it can contain more than 1 value.

ThomasH’s picture

esmerel’s picture

Status: Active » Closed (won't fix)

At this point, nobody is making changes to 1.x - this is not going to get a change.

youssefr’s picture

Version: 5.x-1.6 » 6.x-2.x-dev
The "Node: Distinct" filter is usually the answer.

Do you have a screen-shot or steps for getting to this setting.
Thanks!

youssefr’s picture

Status: Closed (won't fix) » Needs review
FileSize
37.74 KB
58.66 KB

Hi
Many people have mentioned the "NODE: DISTINCT", I can't find that anywhere! No one posted any steps for getting to it anyways. In addition, there are many patches out there and one is confused which one to use. Is there really a fix for this? an official one? or at least a good and working patch?
Please advise.
Thanks!

Youssef

joshuajabbour’s picture

Version: 6.x-2.x-dev » 5.x-1.6
Status: Needs review » Closed (won't fix)

@youssefr see #33 and #40; you're unlikely to get any traction reopening this issue.

Are you using Drupal 5 with views 5.x-1.6? If not, then this issue is not the place to discuss. If so, then Drupal 5 is not supported any longer.

To try and help though, there is a distinct option in that version, as you can see by my patches above, but I have no recollection where. If you can find it, it might help your problem, but per #33, might not.

youssefr’s picture

I apologize if I am posting in the wrong area here (I am not attempting to reopen this). I am on Drupal 6.20 with views 6.x-2.12 and still I am getting duplicate items. Josh, any help is really appreciated.

Thanks

joshuajabbour’s picture

as merlinofchaos said in #33, there are many reasons duplicate nodes can result. sometimes the distinct setting will work, sometimes not.

In views 6, it's located under the "Basic settings" section, "Distinct" option. Set that to yes.

here are some other issues around duplicates: http://drupal.org/project/issues/search/views?issue_tags=duplicate%20vie...
this one may be useful in your case: http://drupal.org/node/417054

anyway, search this site, there's likely to be more help out there...

youssefr’s picture

FileSize
41.23 KB

Basic settings" section, "Distinct" option. not found !?

nairb’s picture

Look in Query settings under Advanced Settings.

youssefr’s picture

Thank you very much!