I recently upgraded to 2.5, and one of my views stopped working. I get the error "user warning: Unknown column 'term_data.name' in 'field list' query". Seems to have to do with using taxonomy terms together (one as an argument, one as a filter).

I've tried many combinations of dropping, re-adding the argument and filters. As soon as I add an argument with "Allow multiple arguments to work together." and pass a valid argument, I see the error message.

Thank you in advance!

View:

$view = new view;
$view->name = 'Toolkit';
$view->description = '';
$view->tag = 'toolkit';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('relationships', array(
  'parent' => array(
    'id' => 'parent',
    'table' => 'term_hierarchy',
    'field' => 'parent',
  ),
));
$handler->override_option('fields', array(
  'view_node' => array(
    'label' => '',
    'text' => '',
    'exclude' => 1,
    'id' => 'view_node',
    'table' => 'node',
    'field' => 'view_node',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'title' => array(
    'label' => '',
    'link_to_node' => 1,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'type' => array(
    'label' => '',
    'link_to_node' => 0,
    'exclude' => 1,
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'name' => array(
    'label' => '',
    'link_to_taxonomy' => 0,
    'exclude' => 1,
    'id' => 'name',
    'table' => 'term_data',
    'field' => 'name',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('arguments', array(
  'name' => array(
    'default_action' => 'ignore',
    'style_plugin' => 'default_summary',
    'style_options' => array(),
    'wildcard' => 'all',
    'wildcard_substitution' => 'All',
    'title' => '%1 Toolkit',
    'default_argument_type' => 'fixed',
    'default_argument' => '',
    'validate_type' => 'none',
    'validate_fail' => 'not found',
    'glossary' => 0,
    'limit' => '0',
    'case' => 'lower',
    'path_case' => 'lower',
    'transform_dash' => 0,
    'add_table' => 1,
    'require_value' => 0,
    'id' => 'name',
    'table' => 'term_data',
    'field' => 'name',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
    'default_options_div_prefix' => '',
    'default_argument_user' => 0,
    'default_argument_fixed' => '',
    'default_argument_php' => '',
    'validate_argument_node_type' => array(
      'webform' => 0,
      'deadlines' => 0,
      'events' => 0,
      'ithsservices' => 0,
      'news' => 0,
      'page' => 0,
      'resourcecenter' => 0,
      'story' => 0,
    ),
    'validate_argument_node_access' => 0,
    'validate_argument_nid_type' => 'nid',
    'validate_argument_vocabulary' => array(
      '3' => 0,
      '7' => 0,
      '6' => 0,
      '8' => 0,
      '10' => 0,
      '9' => 0,
    ),
    'validate_argument_type' => 'tid',
    'validate_argument_php' => '',
  ),
));
$handler->override_option('filters', array(
  'status' => array(
    'operator' => '=',
    'value' => '1',
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'status',
    'table' => 'node',
    'field' => 'status',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'vid' => array(
    'operator' => 'in',
    'value' => array(
      '10' => '10',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'vid',
    'table' => 'term_data',
    'field' => 'vid',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('header', ' ');
$handler->override_option('header_format', '3');
$handler->override_option('header_empty', 0);
$handler->override_option('items_per_page', 1000);
$handler->override_option('distinct', 1);
$handler->override_option('style_plugin', 'list');
$handler->override_option('style_options', array(
  'grouping' => 'name',
  'type' => 'ul',
));
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('relationships', array(
  'parent' => array(
    'label' => 'Parent',
    'required' => 0,
    'id' => 'parent',
    'table' => 'term_hierarchy',
    'field' => 'parent',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('sorts', array(
  'weight' => array(
    'order' => 'ASC',
    'id' => 'weight',
    'table' => 'term_data',
    'field' => 'weight',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
  ),
  'title' => array(
    'order' => 'ASC',
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('arguments', array(
  'name' => array(
    'default_action' => 'ignore',
    'style_plugin' => 'default_summary',
    'style_options' => array(),
    'wildcard' => 'all',
    'wildcard_substitution' => 'All',
    'title' => '%1 Researcher Toolkit',
    'default_argument_type' => 'fixed',
    'default_argument' => '',
    'validate_type' => 'none',
    'validate_fail' => 'not found',
    'glossary' => 0,
    'limit' => '0',
    'case' => 'ucfirst',
    'path_case' => 'lower',
    'transform_dash' => 0,
    'add_table' => 1,
    'require_value' => 0,
    'id' => 'name',
    'table' => 'term_data',
    'field' => 'name',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
    'default_options_div_prefix' => '',
    'default_argument_user' => 0,
    'default_argument_fixed' => '',
    'default_argument_php' => '',
    'validate_argument_node_type' => array(
      'webform' => 0,
      'deadlines' => 0,
      'events' => 0,
      'ithsservices' => 0,
      'news' => 0,
      'page' => 0,
      'resourcecenter' => 0,
      'story' => 0,
    ),
    'validate_argument_node_access' => 0,
    'validate_argument_nid_type' => 'nid',
    'validate_argument_vocabulary' => array(
      '3' => 0,
      '7' => 0,
      '6' => 0,
      '8' => 0,
      '10' => 0,
      '9' => 0,
    ),
    'validate_argument_type' => 'tid',
    'validate_argument_php' => '',
  ),
));
$handler->override_option('path', 'toolkit/%');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
  'name' => 'navigation',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
));

Query:

SELECT DISTINCT(node.nid) AS nid,
   node.title AS node_title,
   node.type AS node_type,
   term_data.name AS term_data_name,
   term_data.vid AS term_data_vid,
   term_data.tid AS term_data_tid,
   term_data.weight AS term_data_weight
 FROM db1node node 
 LEFT JOIN db1term_node term_node ON node.vid = term_node.vid
 LEFT JOIN db1term_node term_data ON node.vid = term_data.vid
 LEFT JOIN db1term_hierarchy term_hierarchy ON term_data.tid = term_hierarchy.tid
 LEFT JOIN db1term_data term_data_term_hierarchy ON term_hierarchy.parent = term_data_term_hierarchy.tid
 LEFT JOIN db1term_data term_data__term_data ON term_data.tid = term_data__term_data.tid
 WHERE (node.status <> 0) AND (term_data.vid in ('10')) AND (term_data__term_data.name = 't0')
   ORDER BY term_data_weight ASC, node_title ASC

Comments

seattlehimay’s picture

seattlehimay commented

In case it matters, I had recently upgraded from 2.3 to 2.5.

gayatri.sa’s picture

gayatri.sa commented

I have the same issue....since I couldn't find a fix I down graded to 2.3 and all is well again...!

agogo’s picture

agogo commented

I also have the same issue. When using a term name as an argument and at the same time using another vocabulary and term in filter all is well in 2.3 but when upgrading to 2.5 the result doesnt display anything. It seems the SQL is wrong (yes?)

FYI I needed the upgrade since Fivestars didnt work in 2.3.

Query:

SELECT node.nid AS nid,
   node.title AS node_title,
   node_revisions.body AS node_revisions_body,
   node_revisions.format AS node_revisions_format,
   node.created AS node_created
 FROM node node 
 INNER JOIN term_node term_node ON node.vid = term_node.vid
 INNER JOIN term_data term_data ON term_node.tid = term_data.tid
 LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
 WHERE (node.status <> 0) AND (node.type in ('test')) AND (term_node.tid = 51) AND (term_data.name = 'Testterm')
   ORDER BY node_created ASC
seattlehimay’s picture

seattlehimay commented
Version: 6.x-2.5 » 6.x-2.6

Updating. Problem still exists in 2.6.

seattlehimay’s picture

seattlehimay commented

Any ideas on this? How can I get multiple taxonomies to work together? This worked in 2.3, but does not work now. I get the error:

user warning: Unknown column 'term_data.name' in 'field list' query: SELECT DISTINCT(node.nid) AS nid, node.title AS node_title, node.type AS node_type, term_data.name AS term_data_name, term_data.vid AS term_data_vid, term_data.tid AS term_data_tid, term_data.weight AS term_data_weight FROM db1node node LEFT JOIN db1term_node term_node ON node.vid = term_node.vid LEFT JOIN db1term_node term_data ON node.vid = term_data.vid LEFT JOIN db1term_data term_data__term_data ON term_data.tid = term_data__term_data.tid WHERE (node.status <> 0) AND (term_data.vid in ('10', '9')) AND (SUBSTR(term_data__term_data.name, 1, 0) = 'T0') ORDER BY term_data_weight ASC, node_title ASC LIMIT 0, 1000 in /data/sites/all/modules/views/includes/view.inc on line 755.

Example: A node is tagged with X,Y,Z but also A,B,C. I want to pass "X" into the URL, then use the other taxonomy terms (A,B,C) as the grouping terms for the HTML list.

So the page would look like:

A
---------
- Link to a node labeled with X and A
- Link to a node labeled with X and A

B
-------
- Link to a node labeled with X and B

C
--------
- Link to a node labeled with X and C

Of course, X is an argument passed in the URL, so I would have similar pages for Y and Z.

akhodakovskiy’s picture

akhodakovskiy commented

subscribing

jonathanpglick’s picture

jonathanpglick commented

I just upgraded from 2.2 to 2.6 and I'm having the same problem when using two Taxonomy: Term arguments. Below is the full SQL:

SELECT node.nid AS nid,
   users.picture AS users_picture,
   users.uid AS users_uid,
   users.name AS users_name,
   node.title AS node_title,
   node_revisions.body AS node_revisions_body,
   node_revisions.format AS node_revisions_format,
   node.created AS node_created
 FROM node node 
 LEFT JOIN term_node term_node ON node.vid = term_node.vid
 LEFT JOIN term_node term_data ON node.vid = term_data.vid
 LEFT JOIN term_data term_data__term_data ON term_data.tid = term_data__term_data.tid AND term_data__term_data.name != 'some-term-name'
 INNER JOIN users users ON node.uid = users.uid
 LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
 WHERE (node.type in ('post')) AND (node.status <> 0) AND (term_data.name = 'some-term-name') AND (term_data__term_data.name = 'another-term-name')
   ORDER BY node_created DESC

The problem seems to be on the second LEFT JOIN where it's referencing the term_node table where it should be term_data (i think):

 LEFT JOIN term_node term_data ON node.vid = term_data.vid

The same problem is shown in the SQL of seattlehimay's first post. What is the last release without this issue? I see people reverting to 2.3, does 2.4 have this problem as well?

jonathanpglick’s picture

jonathanpglick commented

Not sure if this will help everyone but this bug report (http://drupal.org/node/428742) mentions the checking the 'Allow multiple arguments to work together' checkbox. It solved my problems.

agogo’s picture

agogo commented

You have got to be kidding. Yes. It solved my problems.
How could Ive been so stupid. I almost ignored your comment cause of its simplicity. That would have been my bad, definitely.

Thanks Jonathan!

ss_drupal’s picture

ss_drupal commented

Um, at the risk of sounding dumb, what does the "__" syntax mean in the expression,

LEFT JOIN term_data term_data__term_data ON term_data.tid = term_data__term_data.tid AND term_data__term_data.name != 'some-term-name'

Can you point me to documentation somewhere?

Thanks

brush’s picture

brush commented

fwiw, the fix listed (clicking the "play nice with others" button) does not deal with all issues. in my case, it solves the problem in preview, but not on the live site. (i have purged views cache, and all drupal caches). reverting to 2.3 solves the problem.

any ideas why something might solved in preview but not live?

esmerel’s picture

esmerel commented
Status: Active » Closed (fixed)

This issue is a year old, it's likely either resolved or no longer relevant.