Adding a multiple taxonomy term argument leads to a large number of unnecessary joins. [#1023456]

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.

Comment	File	Size	Author
#2	1023456-many_to_one.patch	3.95 KB	dawehner
#2

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

Dave Reid

he/him

English

Nebraska USA

CreditAttribution: Dave Reid commented 11 January 2011 at 21:18

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.

Comment #2

dawehner

German

CreditAttribution: dawehner commented 11 January 2011 at 21:46

File	Size
1023456-many_to_one.patch	3.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

Comment #3

dawehner

German

CreditAttribution: dawehner commented 11 January 2011 at 21:49

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.

Comment #4

iamjon CreditAttribution: iamjon commented 13 February 2011 at 10:19

Status:

Active

» Needs review

Changing status. Based on comment #2

Comment #5

dawehner

German

CreditAttribution: dawehner commented 21 March 2011 at 22:14

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...

Comment #6

merlinofchaos CreditAttribution: merlinofchaos commented 29 March 2011 at 19:29

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.