Early Bird Registration for DrupalCon Portland 2024 is open! Register by 23:59 PST on 31 March 2024, to get $100 off your ticket.
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' => '',
));
Comment | File | Size | Author |
---|---|---|---|
#19 | 284034-19.patch | 841 bytes | msielski |
Comments
Comment #1
KarenS CreditAttribution: KarenS commentedI 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.
Comment #2
mitchell CreditAttribution: mitchell commentedI get a similar error, even when I'm not using the date field in my view.
Comment #3
akeimou CreditAttribution: akeimou commentedi 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.
Comment #4
arlinsandbulte CreditAttribution: arlinsandbulte commentedNo activity after ~6 months of no activity at status "postponed (maintainer needs more info)"
I THINK this has been addressed in more recent releases...
Comment #6
guedressel CreditAttribution: guedressel commentedI 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:
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
Comment #7
guedressel CreditAttribution: guedressel commentedComment #8
guedressel CreditAttribution: guedressel commentedComment #9
kruser CreditAttribution: kruser commentedI'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
Comment #10
xpersonas CreditAttribution: xpersonas commentedYep. I get this when trying to use a CCK field as well.
Comment #11
tomas.teicher CreditAttribution: tomas.teicher commentedThe same problem as phaedo5, I cannot change argument to be assigned to my cck field. I hane no node reference argument, just date argument.
Comment #12
qtrimblesubscribe
Comment #13
qtrimbleI 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.
Comment #14
Leo Pitt CreditAttribution: Leo Pitt commentedAs #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?
Comment #15
Leo Pitt CreditAttribution: Leo Pitt commentedActually, 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.
Comment #16
spike22 CreditAttribution: spike22 commentedyep...same thing, waiting for a patch...can't downgrade to 2.6, because of Calendar module
Comment #17
reaneyk CreditAttribution: reaneyk commentedsubscribe
Comment #18
virgyam CreditAttribution: virgyam commentedI 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.
Comment #19
msielskiI 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.Comment #20
dstolIn 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
Comment #21
anrikun CreditAttribution: anrikun commentedI 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 :(
Comment #22
anrikun CreditAttribution: anrikun commentedRemoving "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.
Comment #23
anrikun CreditAttribution: anrikun commentedI have just tried the last 6.x-2.x-dev.
It seems fixed!