Duplicate View results when sorting by weight_in_tid

I think this is the Views issue. There must be something wrong in sql rewrites for sorting with taxonomy data.
Try changing the sort criteria from weight_in_tid to something more general like Taxonomy: TermID and there are duplicates too. And it doesn't matter if you use DISTINCT or not.

I had the same problem until I changed the Arguments from Taxonomy: TermID (with depth) to Taxonomy: TermID and the duplicates are gone. (if you don't need the 'depth' parameter, of course)

Not really an issue, but: this is a problem, for sure.
A possible solution:
http://drupal.org/node/345571#comment-1188223

Ciao;
Matteo

I was also having the same problem and to found out that it is indeed how the views was created:

The views created:

SELECT DISTINCT(node.nid) AS nid,
   node.title AS node_title,
   node_data_field_image.field_image_fid AS node_data_field_image_field_image_fid,
   node_data_field_image.field_image_list AS node_data_field_image_field_image_list,
   node_data_field_image.field_image_data AS node_data_field_image_field_image_data,
   node.type AS node_type,
   node.vid AS node_vid,
   uc_products.sell_price AS uc_products_sell_price,
   term_node.weight_in_tid AS term_node_weight_in_tid
 FROM drup_node node 
 LEFT JOIN drup_content_field_image node_data_field_image ON node.vid = node_data_field_image.vid
 LEFT JOIN drup_uc_products uc_products ON node.vid = uc_products.vid
 LEFT JOIN drup_term_node term_node ON node.vid = term_node.vid
 WHERE (node.status <> 0 OR node.uid = ***CURRENT_USER*** or ***ADMINISTER_NODES*** = 1) AND (node.vid IN (
  SELECT tn.vid FROM drup_term_node tn
  WHERE tn.tid  = 1
  ))
   ORDER BY term_node_weight_in_tid DESC

The DISTINCT(node.nid) is really useless because of the term_node.weight_in_tid) AS term_node_weight_in_tid therefore when I re-write the views query into

SELECT DISTINCT(node.nid) AS nid,
   node.title AS node_title,
   node_data_field_image.field_image_fid AS node_data_field_image_field_image_fid,
   node_data_field_image.field_image_list AS node_data_field_image_field_image_list,
   node_data_field_image.field_image_data AS node_data_field_image_field_image_data,
   node.type AS node_type,
   node.vid AS node_vid,
   uc_products.sell_price AS uc_products_sell_price
 FROM drup_node node 
 LEFT JOIN drup_content_field_image node_data_field_image ON node.vid = node_data_field_image.vid
 LEFT JOIN drup_uc_products uc_products ON node.vid = uc_products.vid
 LEFT JOIN drup_term_node term_node ON node.vid = term_node.vid
 WHERE (node.status <> 0 OR node.uid = ***CURRENT_USER*** or ***ADMINISTER_NODES*** = 1) AND (node.vid IN (
  SELECT tn.vid FROM drup_term_node tn
  WHERE tn.tid  = 1
  ))
   ORDER BY term_node.weight_in_tid DESC

By removing the term_node.weight_in_tid AS term_node_weight_in_tid in display and changing the ORDER BY term_node_weight_in_tid DESC to ORDER BY term_node.weight_in_tid DESC I was able to get a unique node items.

This means that adding additional field in the select ruins the query.

The fix I can think of is that the views should not include a field for those that are in sorted or filter options.

The "real" fix for this would be to select a derived table that joins "weights_in_tid" values for each argument into one row per nid, THEN left join to the node table... would end up with values something like:

nid, weight_in_tid_arg1, weight_in_tid_arg2
1, 0, 12
2, 1, 0
3, 2, 2

slightly harder php code wise, but it sidesteps distinct completely, and would allow for much more powerful control of which weight takes precedence... hope this idea helps...

I got this one solved by creating a custom module and add this line of code:

function <mymodule>_views_query_alter(&$view, &$query) {
	if ($view->name =='taxonomy_term'):
		unset($query->fields['term_node_weight_in_tid']);
		$query->orderby[0]= 'term_node.weight_in_tid ASC';
	endif;
}

Replace to the name of your module. Make sure also to know the index of the orderby for your weight_in_tid.

Thats it!

dzieyzone

Thanks a lot!

I've got even simpler solution. Remove sorting by weight_in_tid from view at all. And then:

function <mymodule>_views_query_alter(&$view, &$query) {
	if ($view->name =='taxonomy_term'):
		$query->orderby[] = 'term_node.weight_in_tid ASC';
	endif;
}

This is present in 6.x-1.1 , remember to activate the "Distinct" option in your view, to correctly apply the workaround posted by dzieyzone

the workaround posted by dzieyzone doesn't work with Drupal 7 views.

My workaround for D7 based on dzieyzone's D6. But sorting by weight_in_tid must be active.

<?php
function MYMODULE_views_query_alter(&$view, &$query) {
    if ($view->name =='NAME_OF_VIEW'){
	   	   $query->where[] = array(
			'conditions' => array(
				'0'=> array(
					'field' => 'taxonomy_index.tid', 
					'value' => $view->args[0], 
					'operator' => '='
				)
			),
			'args' => array(),
			'type' => 'AND'
		);
	}
}
?>

The above didn't work for me however here is a very similar fix that did the trick in my situation (D7), just be sure to update the view name value and plugin name if it's a block view. GL.

function MYMODULE_views_query_alter(&$view, &$query) {
    if ($view->name == 'category_view' && $view->display_handler->plugin_name == 'page') {
        $query->add_where(1, 'field_product_category_taxonomy_term_data__taxonomy_index.tid', $view->args[0], '=');
    }
}

Duplicat Rows for me too. Somehow there is no join between the taxonomy_index.nid and the node.nid in my case. Not sure how to correct without custom module

Same issue in D7
#9 and #10 didn't help

here is the query

SELECT DISTINCT node.nid AS nid, commerce_product_field_data_field_sku.product_id AS commerce_product_field_data_field_sku_product_id, taxonomy_index.weight AS taxonomy_index_weight, node.created AS node_created, node.sticky AS node_sticky, taxonomy_term_data_node.name AS taxonomy_term_data_node_name, 'commerce_product' AS field_data_field_photo_commerce_product_entity_type, 'commerce_product' AS field_data_commerce_price_commerce_product_entity_type, 'node' AS field_data_title_field_node_entity_type
FROM {node} node
INNER JOIN (SELECT td.*, tn.nid AS nid
FROM {taxonomy_term_data} td
INNER JOIN {taxonomy_vocabulary} tv ON td.vid = tv.vid
INNER JOIN {taxonomy_index} tn ON tn.tid = td.tid
WHERE (tv.machine_name IN ('categories')) ) taxonomy_term_data_node ON node.nid = taxonomy_term_data_node.nid
LEFT JOIN {field_data_field_sku} field_data_field_sku ON node.nid = field_data_field_sku.entity_id AND (field_data_field_sku.entity_type = 'node' AND field_data_field_sku.deleted = '0')
LEFT JOIN {commerce_product} commerce_product_field_data_field_sku ON field_data_field_sku.field_sku_product_id = commerce_product_field_data_field_sku.product_id
LEFT JOIN {taxonomy_index} taxonomy_index ON node.nid = taxonomy_index.nid
WHERE (( (node.status = 1 OR (node.uid = 1 AND 1 <> 0 AND 1 = 1) OR 1 = 1) AND (taxonomy_term_data_node.tid = '29' ) )AND) )))
ORDER BY taxonomy_index_weight ASC, node_created DESC, node_sticky DESC, taxonomy_term_data_node_name ASC

I'd just like to add a note here as I was experiencing the same problems. I had updated to the latest version of Views (7.x-3.8), and that is when the duplicates stared to appear, along with throwing up errors about "broken handlers".

#1860380: "This view has been automatically updated to fix missing relationships". Referenced entity relationships removed.

(For me personally just disabling the views cache was enough to remove duplicates.)

EDIT: Hmm... the duplicates just came back after a while, will try to take the approach given by others.

This issue is due to an incomplete JOIN condition created either by Views or Node Order modules.

Table {taxonomy_index} is joined only to node.nid field:
LEFT JOIN taxonomy_index taxonomy_index ON node.nid = taxonomy_index.nid

while it should be:

LEFT JOIN taxonomy_index taxonomy_index
ON node.nid = taxonomy_index.nid
AND <your_taxonomy_term_table_alias.tid = taxonomy_index.tid

As a workaround it's easier to add an additional WHERE clause to the query generated by Views instead of edit the JOIN.
Suppose you're sorting by node field (linked to taxonomy term) 'MYFIELD' you could add this to a custom module:

function MYMODULE_views_query_alter(&$view, &$query) {
  if ($view->name == 'MY_VIEW_MACHINE_NAME') {
    $query->add_where_expression(
      0,
      'taxonomy_term_data_field_data_field_MYFIELD.tid = taxonomy_index.tid'
    );
  }
}

A better fix will be replace the default views_handler_sort handler defined in nodeorder.views.inc with a customized one that generates the right join.

@nicorac #14

If I understood well, I think that your suggested solution is not very good:

function MYMODULE_views_query_alter(&$view, &$query) {
  if ($view->name == 'MY_VIEW_MACHINE_NAME') {
    $query->add_where_expression(
      0,
      'taxonomy_term_data_field_data_field_MYFIELD.tid = taxonomy_index.tid'
    );
  }
}

because you have to enter a specific field table, but the generic taxonomy_term view is supposed to handle every taxonomy/term/tid request, regardless of field it belongs.
So, if there are more than one term reference fields in this particular node type, how should one choose which one field table to type into the SQL expression? And what if the term belongs to another field?

I suggest a different solution:

<?php
function MYMODULE_views_query_alter(&$view, &$query) {
  if ($view->name == 'taxonomy_term') {
    $query->add_where_expression(
      0,
      "taxonomy_index.tid = " . $view->args[0] 
    );
  }
}
?>

I tried and it seems to work. I wish it doesn't break anything else...

Please try this solution and provide feedback.

@efpado: my solution is a quick-and-dirty fix that should be adapted to anyone needings.

Your solution is more "dynamic" but I wonder if $view->args[0] shouldn't be cleaned up against SQL injections since it's added as-is through $query->add_where_expression().

Anyway the best fix will be a distinct sort field for each sortable taxonomy vocabulary instead of a single one.

The fix in #15 seems to be working for me.

We experience the same problem.

Surprisingly, when we change the contextual filter to "Taxonomy term ID" (without depth), it seems to work OK.
In both views we use the "Query settings: distinct", but we don't use the "Reduce duplicates" possibility provided by the "Taxonomy term ID" contextual filter.

There are both queries:

1) the original query - with the "Content: Has taxonomy term ID (with depth)" contextual filter:

SELECT DISTINCT node.nid AS nid, node.title AS node_title, taxonomy_index.weight AS taxonomy_index_weight, 'node' AS field_data_body_node_entity_type, 'node' AS field_data_field_images_node_entity_type
FROM
{node} node
LEFT JOIN {taxonomy_index} taxonomy_index ON node.nid = taxonomy_index.nid
WHERE (( (node.status = 1 OR (node.uid = 737 AND 737 <> 0 AND 1 = 1) OR 1 = 1) AND (node.nid IN (SELECT tn.nid AS nid
FROM
{taxonomy_index} tn
WHERE ( (tn.tid = '326') ))) ))
ORDER BY taxonomy_index_weight ASC
LIMIT 10 OFFSET 0

2) The modified query - with the "Taxonomy term ID" contextual filter (without depth):

SELECT DISTINCT node.nid AS nid, node.title AS node_title, taxonomy_index.weight AS taxonomy_index_weight, 'node' AS field_data_body_node_entity_type, 'node' AS field_data_field_images_node_entity_type
FROM
{node} node
INNER JOIN {taxonomy_index} taxonomy_index ON node.nid = taxonomy_index.nid
WHERE (( (node.status = 1 OR (node.uid = 737 AND 737 <> 0 AND 1 = 1) OR 1 = 1) AND (taxonomy_index.tid = '326') ))
ORDER BY taxonomy_index_weight ASC
LIMIT 10 OFFSET 0

Here is an alternate solution for those using the interface instead of the code:

Duplicate node entries with node_order, part 3

Summary:
To filter out duplicate node entries in a View when using the Node Order module:

1. In the View, under Other, set Use Aggregation to “Yes”.
2. Under Sort Criteria, make sure you are sorting by Taxonomy Term: Weight in tid.
3. Under Fields, add a field for Taxonomy Term: Weight in tid and exclude it from display.
4. Under Sort Criteria, set the Aggregation settings for Taxonomy Term: Weight in tid to “Sum”.
5. Under Fields, set the Aggregation settings for Taxonomy Term: Weight in tid to “Sum”.
6. Save it.

Thank you Mootly! solution works! #19

#19 doesn't works if you need differents nodes orders per taxonomy.

#15 is working for me !

Answer to #16 (one year later, but I just saw it :P)

Although I didn't actually confirm it, I suppose that $view->args[0] is already sanitized, as it comes from the view object and not directly from the path. I'm pretty sure that the views module does not import stuff into the view objects before checking against malicious code injection.

Same issue here.

Adding weight_in_tid to a view, albeit as field or as sort criterion, I get duplicate entries, which cannot be removed by setting the views options to distinct.

This applies to both the stable and the dev version.

#19 is the way to go. Had a problem with entity reference duplication and #19 fixed it. Brilliant

I'm attaching a patch that incorporates some ideas from comments #14 and #15. This issue has been plaguing Node Order from the beginning, so it would be nice to finally get it resolved once and for all. I don't think setting up aggregation is a good enough solution.

I've tested this on various setups, with the node table as a base. It's possible that coming at this from other tables might not result in the right effect. I'd probably like a little more refinement, but after 7+ years it is better than nothing.

I had also attempted to modify the table join statements, but unsuccessfully, as while you can add extra conditions you can't base them on variables. It can work if you have the right 'data' table joined to the view, but once again that is variable.

Summary

• Add a function to nodeorder.views.inc to modify handler queries with some extra WHERE clauses if required.
• Add two new views handlers, one for weight as a field and the other for weight as a sort. They extend the respective base classes and only override the query() function to call the new function.
• Add some clarity to the labels and descriptions of the elements exposed to view, "weight in tid" is a bit ambigious, so now we actually reference "NodeOrder" in the labels.
• Add all of Node Order's views handlers to the nodeorder.info file.

I think this patch still needs some work so that the query modifications in nodeorder_weight_query_add_where_clauses are "safer" and "cleaner", but I am hoping this is the right approach toward a permanent fix.

Hmmm, seems the changes to the info file don't patch nicely. Might need to move them higher up in the file instead of adding to the end :/

Hi, I don't think that the problem is on the .info file. On your patch you create 2 new files:
includes/views/handlers/nodeorder_handler_field_weight.inc and includes/views/handlers/nodeorder_handler_sort_weight.inc

There's a problem with git diff when you add new files. When applying the patch, the files do not get created:

$ git apply nodeorder-duplicate_view_results-590622-26.patch
error: includes/views/handlers/nodeorder_handler_field_weight.inc: No such file or directory
error: includes/views/handlers/nodeorder_handler_sort_weight.inc: No such file or directory

So I guess this is what cause the fail on testing. You need to create empty files with the same filename before applying the patch.

Since you are a maintainer on the project, I propose that you create the empty files and commit them to the project. They are empty, so they will make no harm. Then, we will be able to use the automated testing environment in order to test your patch.
I'm sure that your patch is correct, I just tried it. But I propose this solution so that everybody can test on the platform.
What do you think?

Ah, I've been using the patch command instead of git apply. Patching an existing module gave me a conflict in the info file because of the Drupal added comments at the end. The patch tool also doesn't care about the correct specification of new files, but git apply does.

I just did some more reading and I think I have the correct output from git diff this time (same patch as #26, but explicitly specifies that new files need to be created).

OK, so I feel much more confident about this patch. I had a few days to try different things and I discovered many bugs with my patch above.

I reworked the query altering function to (a) check for the correct table aliases and (b) simply check that taxonomy_index.nid = node.nid and that taxonomy_index.tid = taxonomy_term_data.tid (if either table is present in the query).

In the various setups I tested, this was sufficient to remove duplicates (so there is no need to rely on a flaky tid contextual filter being passed).

This patch might still fail the CI test (the Node Order module doesn't have any tests yet anyway), but please do try it out if you get the chance (don't forget to clear the cache) and report back here. Thanks.

Setting back for manual review.

If have the same issue with node duplicates in a view with nested taxonomy terms (nodes have multiple terms from 2 taxonomies).

But I use the Drupal 8 module instead which had it's last release almost a year ago. Is there a newer version which addresses this issue, probably on Github, which I am not aware of?

I can confirm that after applying the patch and using the new sort handler there is still duplicate entries for items which have more than one taxonomy term assigned

Drupal 8: I got duplicates when the taxonomy terms were translated. I did not get rid of the duplicates by checking the Advanced > Other >Query settings > Distinct.

When the terms were not translated there were no duplicates and I could create a list sorted on the weight of the taxonomy terms. The list was also grouped by the weigth of the terms in that vocabulary. To be able to sort on the weights of the terms I added a relationship (Advanced > Relationsships > [vocabulary]:Taxonomyterm). To group them I used the weights as the grouping element and rewrote the output to display the name of the term.

I can confirm that Patch from #32 works just fine and code seems good.

To people trying to use this patch: Be aware that since the patch is created from the git and not release, the `.info` file will not be patched because it contains extra data generated by d.o. Don't worry, just add the 3 lines to the file and you're good to go. :)

Setting to RTBC!

I experienced this issue in a rather complicated view in Drupal 8 as well. I solved it with a variation of #15:

<?php
function MYMODULE_views_query_alter(&$view, &$query) {
  ...
  $query->addWhereExpression(0,'taxonomy_index.tid = node__field_category.field_category_target_id');
  ...
?>

You will, of course, want to change node__field_category.field_category_target_id to whatever your field name is

Using Path 32 removed some of the duplicates, but sadly not all. Not sure what to do, my view is rather complicated and I do nto want to mess with altering via theme file since I might break things even more.