Jump to:
| Project: | nodeorder |
| Version: | 6.x-1.1 |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Issue Summary
I'm using a View (overriding the default taxonomy/term/tid View) to display nodes ordered by node order.
Nodes that are assigned to two terms appear twice when viewing those term pages. If I remove one of those terms the duplicates go away.
The View is set to sort by weight_in_tid (and nothing else); Distinct is set to "yes" for the View. If I remove the weight_in_tid sort criteria the duplicates go away.
This only occurred after an upgrade from D5 to D6 (D5 site used the same nodeorder and views setup, but without this duplicates in views issue).
The SQL for the view is (term ID in this example is 60):
SELECT DISTINCT(node.nid) AS nid,
term_node.weight_in_tid AS term_node_weight_in_tid
FROM node node
LEFT JOIN term_node term_node ON node.vid = term_node.vid
WHERE (node.status <> 0 OR (node.uid = ***CURRENT_USER*** AND ***CURRENT_USER*** <> 0) OR ***ADMINISTER_NODES*** = 1) AND (node.vid IN (
SELECT tn.vid FROM term_node tn
WHERE tn.tid = 60
))
ORDER BY term_node_weight_in_tid ASCmerlinofchaos of Views has said that he's found distinct to be unreliable in mysql (we're using version 5.0.51a on a standard Debian install). I posted the issue in the Views issue queue initially but merlinofchaos says it should be posted here.
Comments
#1
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)
#2
Not really an issue, but: this is a problem, for sure.
A possible solution:
http://drupal.org/node/345571#comment-1188223
Ciao;
Matteo
#3
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.
#4
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...
#5
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
#6
Thanks a lot!
I've got even simpler solution. Remove sorting by weight_in_tid from view at all. And then:
<?phpfunction <mymodule>_views_query_alter(&$view, &$query) {
if ($view->name =='taxonomy_term'):
$query->orderby[] = 'term_node.weight_in_tid ASC';
endif;
}
?>
#7
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