Just upgraded to 6.x-2.2, and my calendar view is now looking for a field in the wrong table ... but only when you specify an argument. I'm not at all sure that this is a Calendar bug; it could be in Date or Views, but since it shows up in the calendar view, I'm starting here...
I have two content types (Event and Service) using two different date fields (service_date and datetime) that I want to display together in the calendar. So the Date:Date (node) argument has checkboxes checked for both these fields, with the OR method selected. When I preview this display or view it with the default argument, it comes up just fine, and the query is as follows:
SELECT node.nid AS nid,
node.title AS node_title,
node_data_field_service_date.field_service_date_value AS node_data_field_service_date_field_service_date_value,
node_data_field_service_date.field_service_date_value2 AS node_data_field_service_date_field_service_date_value2,
node_data_field_service_date.delta AS node_data_field_service_date_delta,
node.type AS node_type,
node.vid AS node_vid,
node_data_field_datetime.field_datetime_value AS node_data_field_datetime_field_datetime_value,
node_data_field_datetime.field_datetime_value2 AS node_data_field_datetime_field_datetime_value2,
node_data_field_datetime.field_datetime_rrule AS node_data_field_datetime_field_datetime_rrule,
node_data_field_datetime.delta AS node_data_field_datetime_delta,
term_data.name AS term_data_name,
term_data.vid AS term_data_vid,
term_data.tid AS term_data_tid
FROM node node
LEFT JOIN content_field_service_date node_data_field_service_date ON node.vid = node_data_field_service_date.vid
LEFT JOIN content_field_datetime node_data_field_datetime ON node.vid = node_data_field_datetime.vid
LEFT JOIN term_node term_node ON node.vid = term_node.vid
LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
WHERE (node.status <> 0) AND (node.type in ('event', 'service'))
ORDER BY node_data_field_service_date_field_service_date_value ASC, node_data_field_datetime_field_datetime_value ASC
However, when I specify an argument, I get this error:
user warning: Unknown column 'node_data_field_datetime.field_service_date_value' in 'where clause' query:
SELECT DISTINCT node.nid AS nid,
node.title AS node_title,
node_data_field_service_date.field_service_date_value AS node_data_field_service_date_field_service_date_value,
node_data_field_service_date.field_service_date_value2 AS node_data_field_service_date_field_service_date_value2,
node_data_field_service_date.delta AS node_data_field_service_date_delta,
node.type AS node_type,
node.vid AS node_vid,
node_data_field_datetime.field_datetime_value AS node_data_field_datetime_field_datetime_value,
node_data_field_datetime.field_datetime_value2 AS node_data_field_datetime_field_datetime_value2,
node_data_field_datetime.field_datetime_rrule AS node_data_field_datetime_field_datetime_rrule,
node_data_field_datetime.delta AS node_data_field_datetime_delta,
term_data.name AS term_data_name,
term_data.vid AS term_data_vid,
term_data.tid AS term_data_tid
FROM node node
INNER JOIN term_node term_node ON node.vid = term_node.vid
LEFT JOIN content_field_service_date node_data_field_service_date ON node.vid = node_data_field_service_date.vid
LEFT JOIN content_field_datetime node_data_field_datetime ON node.vid = node_data_field_datetime.vid
LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
WHERE ((node.status <> 0) AND (node.type in ('event', 'service')) AND (term_node.tid = 8)) AND (((DATE_FORMAT(ADDTIME(node_data_field_datetime.field_service_date_value, SEC_TO_TIME(-14400)), '%Y-%m') <= '2010-08' AND DATE_FORMAT(ADDTIME(node_data_field_datetime.field_service_date_value2, SEC_TO_TIME(-14400)), '%Y-%m') >= '2010-08')) OR ((DATE_FORMAT(ADDTIME(node_data_field_datetime.field_datetime_value, SEC_TO_TIME(-14400)), '%Y-%m') <= '2010-08' AND DATE_FORMAT(ADDTIME(node_data_field_datetime.field_datetime_value2, SEC_TO_TIME(-14400)), '%Y-%m') >= '2010-08'))) ORDER BY node_data_field_service_date_field_service_date_value ASC, node_data_field_datetime_field_datetime_value ASC in /home/fusnweb/public_html/sites/all/modules/views/includes/view.inc on line 775.
Notice that the WHERE clause refers to node_data_field_datetime.field_service_date_value when it should be node_data_field_service_date.field_service_date_value . I'm not sure where to start looking for this bug, so any help would be welcome. Thanks in advance!
Comments
Comment #1
SaxxIng commentedExactly the same problem to me too!
Comment #2
Liam Mitchell commentedConfirming this issue.
I manually changed and ran the SQL and it worked without error. In the above case it would require changing both occurrences of node_data_field_datetime.field_service_date_value with node_data_field_service_date.field_service_date_value.
I'm going to have a look at the source now to see if I can make any sense of it.
EDIT
Its a bug in the query() function of date_api_argument_handler.inc I think...
Comment #3
Liam Mitchell commentedYep, pretty sure I got it.
Try out this patch I made and see if that fixes it: http://drupal.org/node/580178#comment-3405282
Comment #4
SaxxIng commentedDuplicate of #580178: Views: SQL Error "Unknown column" when using fields from different content-types in a filter (the same bug).
Patch works!
Comment #5
Liam Mitchell commentedFYI - That patch may cause problems if you have relationships? I changed it and posted it under the first one