When I create a view and add a relationship to the "Taxonomy: terms on node" field, the resultant query only joins to the term_data table and doesn't also join to the term_node table.

Steps to reproduce

  1. Create node view
  2. Add a field e.g. nid
  3. Add a relationship to Taxonomy: Terms on Node
  4. Select any vocabulary
  5. Important part: tick the "Require this relationship"
  6. Preview the view

To work around this I untick the "Require this relationship" checkbox, but it results in a very slow query: #1024832: views_handler_relationship_node_term_data extremely slow.

I took a look at views_handler_relationship_node_term_data.inc and the query method looks to only be adding a relationship to the term_data table. This is fine when the "Require this relationship" checkbox isn't ticked because it joins to the sub-select. But when not ticked, it needs a relationship to the term_node table added prior to the term_data relationship.

I am adding this explicit relationship to the taxonomy in my view because I need terms from multiple vocabs on the same row as the node. When I include the terms by themselves, the node is duplicated.

CommentFileSizeAuthor
#23 views_7.x-3.x-taxonomy-term-relationship-should-join-on-its-own-table-1409640-23.patch8.02 KBDean Reilly
#20 views-8.x-3.x-taxonomy-term-relationship-test-1409640-20.patch7.42 KBaspilicious
#18 views-8.x-3.x-taxonomy-term-relationship-test-1409640-18.patch7.41 KBaspilicious
#12 views-8.x-3.x-fix-taxonomy-term-relationship-1409640-12.patch1.27 KBDean Reilly
#12 views-8.x-3.x-taxonomy-term-relationship-test-1409640-12.patch7.67 KBDean Reilly
#11 views-7.x-3.x-fix-taxonomy-term-relationship-1409640-11.patch1.27 KBDean Reilly
#11 views-7.x-3.x-taxonomy-term-relationship-test-1409640-11.patch7.04 KBDean Reilly
#10 views-6.x-3.x-fix-taxonomy-term-relationship-1409640-10.patch1.16 KBDean Reilly
#4 views-3.x-term_node-table-missing-1409640-5.patch1.07 KBDean Reilly
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented
Status: Active » Postponed (maintainer needs more info)

Without that ticked, the code normally adds term_node instead of the subquery. CAn you export the view exhibiting this and paste the actual query generated in the preview?

5kot’s picture

5kot CreditAttribution: 5kot commented
Status: Postponed (maintainer needs more info) » Active

The behaviour I saw was that when the checkbox was not ticked, the sub query was used. The sub query makes sense because you need an IJ between the term_node and term_data tables and a LOJ to the result of that. When ticked, I'd expect to see all IJ's.

Here's the view & the query.

View:

$view = new view; $view->name = 'issue_1409640'; $view->description = ''; $view->tag = ''; $view->base_table = 'node'; $view->human_name = ''; $view->core = 6; $view->api_version = '3.0'; $view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */ /* Display: Defaults */ $handler = $view->new_display('default', 'Defaults', 'default'); $handler->display->display_options['access']['type'] = 'none'; $handler->display->display_options['cache']['type'] = 'none'; $handler->display->display_options['query']['type'] = 'views_query'; $handler->display->display_options['exposed_form']['type'] = 'basic'; $handler->display->display_options['pager']['type'] = 'full'; $handler->display->display_options['style_plugin'] = 'default'; $handler->display->display_options['row_plugin'] = 'fields'; /* Relationship: Taxonomy: Terms on node */ $handler->display->display_options['relationships']['term_node_tid']['id'] = 'term_node_tid'; $handler->display->display_options['relationships']['term_node_tid']['table'] = 'node'; $handler->display->display_options['relationships']['term_node_tid']['field'] = 'term_node_tid'; $handler->display->display_options['relationships']['term_node_tid']['required'] = 1; $handler->display->display_options['relationships']['term_node_tid']['vids'] = array( 1 => 1, 2 => 0, 20 => 0, 6 => 0, 5 => 0, 4 => 0, 9 => 0, 10 => 0, 11 => 0, 12 => 0, 7 => 0, 15 => 0, 14 => 0, 13 => 0, 8 => 0, 18 => 0, 19 => 0, 16 => 0, 17 => 0, ); /* Field: Node: Nid */ $handler->display->display_options['fields']['nid']['id'] = 'nid'; $handler->display->display_options['fields']['nid']['table'] = 'node'; $handler->display->display_options['fields']['nid']['field'] = 'nid'; $handler->display->display_options['fields']['nid']['alter']['alter_text'] = 0; $handler->display->display_options['fields']['nid']['alter']['make_link'] = 0; $handler->display->display_options['fields']['nid']['alter']['absolute'] = 0; $handler->display->display_options['fields']['nid']['alter']['external'] = 0; $handler->display->display_options['fields']['nid']['alter']['replace_spaces'] = 0; $handler->display->display_options['fields']['nid']['alter']['trim_whitespace'] = 0; $handler->display->display_options['fields']['nid']['alter']['nl2br'] = 0; $handler->display->display_options['fields']['nid']['alter']['word_boundary'] = 1; $handler->display->display_options['fields']['nid']['alter']['ellipsis'] = 1; $handler->display->display_options['fields']['nid']['alter']['strip_tags'] = 0; $handler->display->display_options['fields']['nid']['alter']['trim'] = 0; $handler->display->display_options['fields']['nid']['alter']['html'] = 0; $handler->display->display_options['fields']['nid']['element_label_colon'] = 1; $handler->display->display_options['fields']['nid']['element_default_classes'] = 1; $handler->display->display_options['fields']['nid']['hide_empty'] = 0; $handler->display->display_options['fields']['nid']['empty_zero'] = 0; $handler->display->display_options['fields']['nid']['hide_alter_empty'] = 1; $handler->display->display_options['fields']['nid']['link_to_node'] = 0;

Query:
SELECT node.nid AS nid FROM node node INNER JOIN term_data term_data_node ON .tid = term_data_node.tid

keithm’s picture

keithm CreditAttribution: keithm commented

I am seeing the same problem on views-6.x-2.16. #1231380: Relationship Taxonomy Related Terms, vocabularies not filtered when require this relationship seems to be a duplicate of this issue.

Dean Reilly’s picture

Dean Reilly CreditAttribution: Dean Reilly commented
FileSize
views-3.x-term_node-table-missing-1409640-5.patch1.07 KB

I recently ran into this issue myself after updating a project I inherited from Views 6.x-3.0 Alpha 3. The problem stems from the removal of an add_table line in this commit (http://drupalcode.org/project/views.git/commitdiff/1ac96c1f6b4b8bae3e4c5...) which came form issue #834948: After upgrading "Fatal error: __clone method called on non-object in /includes/common.inc on line 1736".

The line was removed as it was causing adjust_join to attempt to clone a NULL object (you can see this in comment #9). However, when adding this table adjust_join should never be called in the first place. This problem obviously has a more far reaching affect and was discovered and resolved independently in this commit (http://drupalcode.org/project/views.git/commitdiff/1f5dafda43b9f43196f99...) from issue #887768: Notice: Undefined index: in views_query->add_table().

The missing line can now be safely re-added to restore this functionality without breaking anything else. I've attached a patch to do just this.

Dean Reilly’s picture

Dean Reilly CreditAttribution: Dean Reilly commented
Status: Active » Needs review

Updating status.

@keithm, this seems to be a separate issue which is unrelated.

Status: Needs review » Needs work

The last submitted patch, views-3.x-term_node-table-missing-1409640-5.patch, failed testing.

Dean Reilly’s picture

Dean Reilly CreditAttribution: Dean Reilly commented
Version: 6.x-3.0 » 6.x-3.x-dev
Dean Reilly’s picture

Dean Reilly CreditAttribution: Dean Reilly commented
Status: Needs work » Needs review

#4: views-3.x-term_node-table-missing-1409640-5.patch queued for re-testing.

xjm’s picture

xjm
she/her
Primary language English
CreditAttribution: xjm commented

I think this might be a duplicate of #1222324: Fix query access control on relationships (comments) and/or #1349080: node_access filters out accessible nodes when node is left joined.

Dean Reilly’s picture

Dean Reilly CreditAttribution: Dean Reilly commented
FileSize
views-6.x-3.x-fix-taxonomy-term-relationship-1409640-10.patch1.16 KB

This is a separate issue from those.

Having reread through #834948: After upgrading "Fatal error: __clone method called on non-object in /includes/common.inc on line 1736" though - Dawehner does raise the point that the $term_node variable isn't used in the join. This is a problem as it means the join isn't necessarily happening on the correct table. This affects all current versions of views. I've attached patches for 6.x-3.x, 7.x-3.x and 8.x-3.x and tests demonstrating the issue for the last two.

Dean Reilly’s picture

Dean Reilly CreditAttribution: Dean Reilly commented
Version: 6.x-3.x-dev » 7.x-3.x-dev
FileSize
views-7.x-3.x-taxonomy-term-relationship-test-1409640-11.patch7.04 KB
views-7.x-3.x-fix-taxonomy-term-relationship-1409640-11.patch1.27 KB
Dean Reilly’s picture

Dean Reilly CreditAttribution: Dean Reilly commented
Version: 7.x-3.x-dev » 8.x-3.x-dev
FileSize
views-8.x-3.x-taxonomy-term-relationship-test-1409640-12.patch7.67 KB
views-8.x-3.x-fix-taxonomy-term-relationship-1409640-12.patch1.27 KB
Dean Reilly’s picture

Dean Reilly CreditAttribution: Dean Reilly commented
Title: term_node table missing in query when adding a required relationship to the "terms on node" field. » Taxonomy terms on node relationship potentially joins on wrong table

Updated the title to reflect the more general case of the issue.

Status: Needs review » Needs work

The last submitted patch, views-8.x-3.x-taxonomy-term-relationship-test-1409640-12.patch, failed testing.

Dean Reilly’s picture

Dean Reilly CreditAttribution: Dean Reilly commented
Status: Needs work » Needs review

#12: views-8.x-3.x-fix-taxonomy-term-relationship-1409640-12.patch queued for re-testing.

dawehner’s picture

dawehner
Primary language German
CreditAttribution: dawehner commented

#12: views-8.x-3.x-taxonomy-term-relationship-test-1409640-12.patch queued for re-testing.

Status: Needs review » Needs work

The last submitted patch, views-8.x-3.x-taxonomy-term-relationship-test-1409640-12.patch, failed testing.

aspilicious’s picture

aspilicious CreditAttribution: aspilicious commented
Status: Needs work » Needs review
FileSize
views-8.x-3.x-taxonomy-term-relationship-test-1409640-18.patch7.41 KB

merged the patch and the test

Status: Needs review » Needs work

The last submitted patch, views-8.x-3.x-taxonomy-term-relationship-test-1409640-18.patch, failed testing.

aspilicious’s picture

aspilicious CreditAttribution: aspilicious commented
Status: Needs work » Needs review
FileSize
views-8.x-3.x-taxonomy-term-relationship-test-1409640-20.patch7.42 KB
dawehner’s picture

dawehner
Primary language German
CreditAttribution: dawehner commented
Status: Needs review » Fixed

Thanks for the port, it looks great!

dawehner’s picture

dawehner
Primary language German
CreditAttribution: dawehner commented
Version: 8.x-3.x-dev » 7.x-3.x-dev
Status: Fixed » Patch (to be ported)

This still has to be committed to 7.x-3.x

Dean Reilly’s picture

Dean Reilly CreditAttribution: Dean Reilly commented
Status: Patch (to be ported) » Needs review
FileSize
views_7.x-3.x-taxonomy-term-relationship-should-join-on-its-own-table-1409640-23.patch8.02 KB

The combined patch for 7.x-3.x.

aspilicious’s picture

aspilicious CreditAttribution: aspilicious commented
Status: Needs review » Reviewed & tested by the community

Looks good, needs a newline after the test but the committer can do this I think.

dawehner’s picture

dawehner
Primary language German
CreditAttribution: dawehner commented
Status: Reviewed & tested by the community » Fixed

I'm sorry for giving the wrong commit credit but i was simply confused by the fact that someone patches 8.x-3.x, so this patch should be a patch to be ported ...

Committed finally to 7.x-3.x

Dean Reilly’s picture

Dean Reilly CreditAttribution: Dean Reilly commented
Version: 7.x-3.x-dev » 6.x-3.x-dev
Status: Fixed » Reviewed & tested by the community

Hey Daniel,

No worries, it's nothing worth getting worked up over and I regret mentioning it now (I shouldn't have skipped breakfast that morning!)

The patch for 6.x-3.x is in #10 and needs to be committed too.

Dean

Dean Reilly’s picture

Dean Reilly CreditAttribution: Dean Reilly commented

#10: views-6.x-3.x-fix-taxonomy-term-relationship-1409640-10.patch queued for re-testing.

dawehner’s picture

dawehner
Primary language German
CreditAttribution: dawehner commented
Status: Reviewed & tested by the community » Fixed

Great! Committed now to 6.x-3.x as well.

Status: Fixed » Closed (fixed)

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