We have a 'Related content' view that pulls in nodes that have the any of the same taxonomy terms as a certain node. And it ends up generating this SQL which completely breaks Acquia servers: http://palantir.privatepaste.com/3f09eb28ac. Working on a testing view to replicate locally and export here to demonstrate.

CommentFileSizeAuthor
#2 1023456-many_to_one.patch3.95 KBdawehner

Comments

dave reid’s picture

dave reid
he/him
Primary language English
Location Nebraska USA
commented

Here is the 'related content' view mimicking what we have in development.
http://palantir.privatepaste.com/1fdf587894

The meat is from the 'Taxonomy term ID' argument that uses PHP code for the default value:

if (arg(0) == 'node' && ($node = node_load(arg(1))) && node_access('view', $node) && $items = field_get_items('node', $node, 'field_tags') {
  $tids = array();
  foreach ($items as $term) {
    $tids[] = $term['tid'];
  }
  return implode('+', $tids);
}

There is also a Node ID argument so that the current node being used to compare its terms is excluded from the results.

dawehner’s picture

dawehner
Primary language German
commented
StatusFileSize
new 1023456-many_to_one.patch3.95 KB

Not sure whether this is related to this issue, but this fixes at least one problem for me

#955464: Missing join: 'taxonomy_term_data' creating a Glossary view based on taxonomy

dawehner’s picture

dawehner
Primary language German
commented

Here is a similar query in d6

SELECT node.nid AS nid FROM node node  INNER JOIN term_node term_node_value_0 ON node.vid = term_node_value_0.vid AND term_node_value_0.tid = 1 INNER JOIN term_node term_node_value_1 ON node.vid = term_node_value_1.vid AND term_node_value_1.tid = 2 INNER JOIN term_node term_node_value_2 ON node.vid = term_node_value_2.vid AND term_node_value_2.tid = 3 WHERE term_node_value_0.tid = 1 AND term_node_value_1.tid = 2 AND term_node_value_2.tid = 3

SELECT node.nid AS nid FROM node node  LEFT JOIN term_node term_node_value_0 ON node.vid = term_node_value_0.vid AND term_node_value_0.tid = 1 LEFT JOIN term_node term_node_value_1 ON node.vid = term_node_value_1.vid AND term_node_value_1.tid = 2 LEFT JOIN term_node term_node_value_2 ON node.vid = term_node_value_2.vid AND term_node_value_2.tid = 3 WHERE term_node_value_0.tid = 1 OR term_node_value_1.tid = 2 OR term_node_value_2.tid = 3

You can get rid of the joins by de-selecting "reduce duplicates". Not sure what for problems this might cause.

iamjon’s picture

iamjon commented
Status: Active » Needs review

Changing status. Based on comment #2

dawehner’s picture

dawehner
Primary language German
commented
Status: Needs review » Active

Commited the fix.

Thanks #iamjon

But the initial issue itself can't be fixed. Ask earl for the reasons of the joins, but they have to be there to work as expected on all possible instances...

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Fixed

It's an unfortunate consequence of many to one relationships. It's pretty difficult to compare a group that will generate multiple records to another group.

This looks like it is as fixed as it can be.

Status: Fixed » Closed (fixed)

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