I have a content type that has a Node Reference field and a Date field. A view is used to display the dates within a certain period and that reference a certain node. No relationship is defined in the view. The are 2 arguments: node reference and date.
When I use a wildcard for one of these arguments, everything goes well. But when I enter 2 valid arguments, I get the following error:
user warning: Unknown column 'node_data_field_datum.field_datum_value' in 'field list'.

The following SQL code is executed:

SELECT node.nid AS nid,
   node_data_field_datum.field_datum_value AS node_data_field_datum_field_datum_value,
   node_data_field_datum.field_datum_value2 AS node_data_field_datum_field_datum_value2,
   node.type AS node_type,
   node_data_field_voorst.field_datum_value AS node_data_field_voorst_field_datum_value,
   node_data_field_voorst.field_datum_value2 AS node_data_field_voorst_field_datum_value2,
   node_data_field_voorst.nid AS node_data_field_voorst_nid,
   node_data_field_voorst.field_tijd_value AS node_data_field_voorst_field_tijd_value
 FROM node node 
 LEFT JOIN content_type_speeldatum node_data_field_voorst ON node.vid = node_data_field_voorst.vid
 WHERE (node_data_field_voorst.field_voorst_nid = '37')
    AND ((STR_TO_DATE(node_data_field_datum.field_datum_value, '%Y-%m-%dT%T') >= '2008-07-17 20:25:34' AND STR_TO_DATE(node_data_field_datum.field_datum_value, '%Y-%m-%dT%T') <= '2008-07-24 20:25:33'))

Export of the view:

$view = new view;
$view->name = 'test';
$view->description = 'test';
$view->tag = 'test';
$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('fields', array(
  'field_datum_value' => array(
    'label' => '',
    'link_to_node' => 0,
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_datum_value',
    'table' => 'node_data_field_datum',
    'field' => 'field_datum_value',
    'relationship' => 'none',
  ),
  'field_tijd_value' => array(
    'label' => '',
    'link_to_node' => 0,
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_tijd_value',
    'table' => 'node_data_field_tijd',
    'field' => 'field_tijd_value',
    'relationship' => 'none',
  ),
));
$handler->override_option('arguments', array(
  'field_voorst_nid' => array(
    'default_action' => 'ignore',
    'style_plugin' => 'default_summary',
    'style_options' => array(),
    'wildcard' => 'all',
    'wildcard_substitution' => 'Alle',
    'title' => '',
    'default_argument_type' => 'fixed',
    'default_argument' => '',
    'validate_type' => 'none',
    'validate_fail' => 'not found',
    'id' => 'field_voorst_nid',
    'table' => 'node_data_field_voorst',
    'field' => 'field_voorst_nid',
    'relationship' => 'none',
    'default_options_div_prefix' => '',
    'default_argument_user' => 0,
    'default_argument_fixed' => '',
    'default_argument_php' => '',
    'validate_argument_node_type' => array(
      'image' => 0,
      'nieuws' => 0,
      'pagina' => 0,
      'speeldatum' => 0,
      'voorstelling' => 0,
    ),
    'validate_argument_node_access' => 0,
    'validate_argument_nid_type' => 'nid',
    'validate_argument_php' => '',
  ),
  'date_argument' => array(
    'default_action' => 'ignore',
    'style_plugin' => 'default_summary',
    'style_options' => array(),
    'wildcard' => 'all',
    'wildcard_substitution' => 'Alle',
    'title' => '',
    'default_argument_type' => 'fixed',
    'default_argument' => '',
    'validate_type' => 'none',
    'validate_fail' => 'not found',
    'date_fields' => array(
      'node_data_field_datum.field_datum_value' => 'node_data_field_datum.field_datum_value',
    ),
    'date_method' => 'OR',
    'granularity' => 'month',
    'id' => 'date_argument',
    'table' => 'node',
    'field' => 'date_argument',
    'relationship' => 'none',
    'default_options_div_prefix' => '',
    'default_argument_user' => 0,
    'default_argument_fixed' => '',
    'default_argument_php' => '',
    'validate_argument_node_type' => array(
      'image' => 0,
      'nieuws' => 0,
      'pagina' => 0,
      'speeldatum' => 0,
      'voorstelling' => 0,
    ),
    'validate_argument_node_access' => 0,
    'validate_argument_nid_type' => 'nid',
    'validate_argument_php' => '',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
  'role' => array(),
  'perm' => '',
));
$handler->override_option('title', 'Snelmenu');
$handler->override_option('style_plugin', 'list');
$handler->override_option('row_options', array(
  'inline' => array(
    'field_datum_value' => 'field_datum_value',
    'field_tijd_value' => 'field_tijd_value',
  ),
  'separator' => '',
));

CommentFileSizeAuthor
#19 284034-19.patch841 bytesmsielski
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

KarenS’s picture

Status: Active » Postponed (maintainer needs more info)

I think it's because the date field is set to be grouped, and when you do that CCK removes it from the query. If that's the problem, the nodereference argument won't change anything and you'll get the same error without a nodereference argument. Can you double-check that using the latest -dev version of the code? It simplifies fixing things if I know the nodereference argument is not a factor.

If that's the case, please change the title to something like 'Error using grouped date field with date argument', which would be a more accurate description of the problem.

mitchell’s picture

Version: 6.x-2.x-dev » 6.x-2.0-rc5

I get a similar error, even when I'm not using the date field in my view.

user warning: Unknown column 'node_node_data_field_organization_reference.vid' in 'on clause' query: SELECT node.nid AS nid, node_data_field_name.field_name_value AS node_data_field_name_field_name_value, node_data_field_name.nid AS node_data_field_name_nid, node.type AS node_type FROM node node LEFT JOIN content_type_event node_data_field_organization_reference ON node_node_data_field_organization_reference.vid = node_data_field_organization_reference.vid LEFT JOIN node node_node_data_field_organization_reference ON node_data_field_organization_reference.field_organization_reference_nid = node_node_data_field_organization_reference.nid LEFT JOIN content_type_event node_data_field_name ON node.vid = node_data_field_name.vid WHERE (node.status <> 0) AND (node.type in ('event')) AND (node_data_field_organization_reference.field_organization_reference_nid = 1) LIMIT 0, 10 in /var/www/ufculturalcouncil/sites/all/modules/views/includes/view.inc on line 723.
$view = new view;
$view->name = 'organizations_events';
$view->description = 'Events of this organization';
$view->tag = '';
$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(
  'field_organization_reference_nid' => array(
    'label' => 'Organization',
    'required' => 0,
    'delta' => -1,
    'id' => 'field_organization_reference_nid',
    'table' => 'node_data_field_organization_reference',
    'field' => 'field_organization_reference_nid',
    'relationship' => 'none',
  ),
));
$handler->override_option('fields', array(
  'field_name_value' => array(
    'label' => '',
    'link_to_node' => 1,
    'label_type' => 'none',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_name_value',
    'table' => 'node_data_field_name',
    'field' => 'field_name_value',
    'relationship' => 'none',
  ),
));
$handler->override_option('arguments', array(
  'field_organization_reference_nid' => array(
    'default_action' => 'default',
    'style_plugin' => 'default_summary',
    'style_options' => array(),
    'wildcard' => 'all',
    'wildcard_substitution' => 'All',
    'title' => '',
    'default_argument_type' => 'node',
    'default_argument' => '',
    'validate_type' => 'none',
    'validate_fail' => 'not found',
    'break_phrase' => 0,
    'not' => 0,
    'id' => 'field_organization_reference_nid',
    'table' => 'node_data_field_organization_reference',
    'field' => 'field_organization_reference_nid',
    'relationship' => 'field_organization_reference_nid',
    'default_options_div_prefix' => '',
    'default_argument_user' => 0,
    'default_argument_fixed' => '',
    'default_argument_php' => '',
    'validate_argument_node_type' => array(
      'event' => 0,
      'organization' => 0,
      'page' => 0,
      'story' => 0,
    ),
    'validate_argument_node_access' => 0,
    'validate_argument_nid_type' => 'nid',
    'validate_argument_node_flag_name' => '*relationship*',
    'validate_argument_node_flag_test' => 'flaggable',
    'validate_argument_node_flag_id_type' => 'id',
    'validate_argument_user_flag_name' => '*relationship*',
    'validate_argument_user_flag_test' => 'flaggable',
    'validate_argument_user_flag_id_type' => 'id',
    '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',
    'relationship' => 'none',
  ),
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'event' => 'event',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler = $view->new_display('block', 'Block', 'block_1');
$handler->override_option('block_description', 'Organization\'s Events');
$handler->override_option('block_caching', -1);
akeimou’s picture

Version: 6.x-2.0-rc5 » 6.x-2.3

i have something similar, unknown column when using date filter in combination with a user reference argument. not sure that it's a date or views or cck problem.

problem:
custom content type has a User Reference field called offuser and a Date field called offdate, no grouping. A view is used to display the dates within a certain period and for a given user argument. the Action to take if argument is not present is defined in the view as "Summary, sorted ascending." also, the Date filter is set with a default of "now" (also tried it with an absolute value like 2009). if the view is called with no argument, the error is "Unknown column 'name' in 'field list'"

that's because the query is:
SELECT node_data_field_offdate.field_offuser_uid AS node_data_field_offdate_field_offuser_uid,
name,
COUNT(node.nid) AS num_records
FROM node node
INNER JOIN content_type_timeoff node_data_field_offtype ON node.vid = node_data_field_offtype.vid
LEFT JOIN content_type_timeoff node_data_field_offdate ON node.vid = node_data_field_offdate.vid
WHERE ((node.type in ('timeoff')) AND (node_data_field_offtype.field_offtype_value = 'overtime'))
AND (DATE_FORMAT(STR_TO_DATE(node_data_field_offdate.field_offdate_value, '%Y-%m-%dT%T'), '%Y') = '2009')
GROUP BY name, node_data_field_offdate_field_offuser_uid
ORDER BY name ASC

if the action for empty arg is changed to a non-summary like "Display all values" or
if the default date is empty rather than "now" or some absolute value
the query is correct (e.g., below) and there is no error when the view is called without an arg.

SELECT node_data_field_offuser.field_offuser_uid AS node_data_field_offuser_field_offuser_uid,
users_node_data_field_offuser.name AS users_node_data_field_offuser_name,
COUNT(node.nid) AS num_records
FROM node node
INNER JOIN content_type_timeoff node_data_field_offtype ON node.vid = node_data_field_offtype.vid
LEFT JOIN content_type_timeoff node_data_field_offuser ON node.vid = node_data_field_offuser.vid
LEFT JOIN users users_node_data_field_offuser ON node_data_field_offuser.field_offuser_uid = users_node_data_field_offuser.uid
WHERE (node.type in ('timeoff')) AND (node_data_field_offtype.field_offtype_value = 'overtime')
GROUP BY users_node_data_field_offuser_name, node_data_field_offuser_field_offuser_uid
ORDER BY users_node_data_field_offuser_name ASC

that is, the column should be users_node_data_field_offuser.name instead of just name and there's a missing left join on the Users table. hope that helps close in on the problem description.

arlinsandbulte’s picture

Status: Postponed (maintainer needs more info) » Fixed

No activity after ~6 months of no activity at status "postponed (maintainer needs more info)"
I THINK this has been addressed in more recent releases...

Status: Fixed » Closed (fixed)

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

guedressel’s picture

I just ran into the same issue - I guess?!

I'm trying to build a view displaying a node list with an argument date Date: Date (node) Content: Date (field_eventdate)
The view gets an "Date Browser" Attachment from the module Calendar - which requires the date argument to have the "argument not present"-action to be set to "Current Date".

The moment I set the date argument to "Current Date" I get the same SQL warning as intru posted initially, expect that I have no node_reference stuff included in the view:

user warning: Unknown column 'node_data_field_eventdate.field_eventdate_value' in 'where clause' query: SELECT node.nid AS nid, node.type AS node_type FROM node node WHERE (node.type in ('event')) AND ((DATE_FORMAT(ADDTIME(node_data_field_eventdate.field_eventdate_value, SEC_TO_TIME(3600)), '%Y-%m') <= '2011-02' AND DATE_FORMAT(ADDTIME(node_data_field_eventdate.field_eventdate_value, SEC_TO_TIME(3600)), '%Y-%m') >= '2011-02')) in /somewhere/sites/all/modules/views/includes/view.inc on line 775.

Please take also the issue #1068812: "unknown column" error when setting date argument to default to summary in account, it spots the same problem.

arlinsandbulte, this problem seems not to be addressed in recent releases. I'm using 6.x-2.7

guedressel’s picture

Version: 6.x-2.3 » 6.x-2.7
guedressel’s picture

Status: Closed (fixed) » Active
kruser’s picture

I'm getting this too but only when I use a cck date argument set to Summary, sorted ascending. Note that this worked fine in 6.x-2.6

xpersonas’s picture

Yep. I get this when trying to use a CCK field as well.

tomas.teicher’s picture

The same problem as phaedo5, I cannot change argument to be assigned to my cck field. I hane no node reference argument, just date argument.

qtrimble’s picture

subscribe

qtrimble’s picture

I just updated the date module to 6.x-2.7 and I am experiencing the same issue as described above. I have followed all of the recommendations for troubleshooting date issues here: http://drupal.org/node/755312.

Leo Pitt’s picture

As #13: I have two sites, one which uses date 6.x-2.7 and where I get the same problem, and another site running 6.x-2.6 and where the problem does not occur.

It looks as if something has changed in 2.7 that causes Views not to be able to find the date field ... Was the date field schema naming changed in some way?

Leo Pitt’s picture

Actually, I should correct my report. The error I get is just when using a date argument - no node reference field involved so I should really create a new bug report.

spike22’s picture

yep...same thing, waiting for a patch...can't downgrade to 2.6, because of Calendar module

reaneyk’s picture

subscribe

virgyam’s picture

I have the same problem of akeimou unknown column when using date filter in combination with a user reference argument but when the arguments are presents.

msielski’s picture

Status: Active » Needs work
FileSize
841 bytes

I am observing this problem with Date 6.x-2.7 and Views 6.x-2.12 when I have a node reference argument as well as a date argument.

Reports that this didn't occur with date 6.x-2.6 made me look at the date argument code in 2.6. The only change which seemed to affect table names was in the set_relationship function in includes\date_api_argument_handler.inc. Removing this change from 6.x-2.7 fixes the problem.

Unfortunately, I don't understand the nature of the change made to set_relationship between 2.6 and 2.7, but I'm sure it was made for a reason, so I would not advise that the attached patch be used until someone more knowledgeable can look into this. Hopefully this helps narrow things down for them.

dstol’s picture

Component: Date CCK Field » Views Argument
Priority: Normal » Major
Status: Needs work » Needs review

In looking for an issue that would have resulted in this update... it's commit 8bda85c3ad831c418026c2628bd3e66a63a79497 and doesn't seem to be associated with any particular issue by the commit message 'Set relationship only if there is a relationship.'

Setting as needs review for maintainer attention on #19

anrikun’s picture

Title: "Unknown column" when using date argument » "Unknown column" when using date argument in combination with node reference argument.
Version: 6.x-2.x-dev » 6.x-2.7
Priority: Critical » Major
Status: Needs work » Needs review

I am affected by the same bug.
@dstol: patch at #19 has side effects and should be avoided.

Meanwhile, a workaround:
add the missing column as a field and check "Exclude from display"

Forget about this workaround, it does not always work :(

anrikun’s picture

Title: "Unknown column" when using date argument in combination with node reference argument. » "Unknown column" when using date argument
Version: 6.x-2.7 » 6.x-2.x-dev
Priority: Major » Critical
Status: Needs review » Needs work

Removing "in combination with node reference" from the title.
It also happens even when no node reference is present.

Also marking this as critical as it throws a SQL error.

anrikun’s picture

Title: "Unknown column" when using date argument in combination with node reference argument. » "Unknown column" when using date argument
Version: 6.x-2.7 » 6.x-2.x-dev
Priority: Major » Critical
Status: Needs review » Fixed

I have just tried the last 6.x-2.x-dev.
It seems fixed!

Status: Fixed » Closed (fixed)

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