| Project: | Date |
| Version: | 6.x-2.7 |
| Component: | Date API |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | closed (fixed) |
| Issue tags: | calendar, column, date, permissions, unknown |
Issue Summary
When creating views and adding a date-filter which uses different date-fields from different content-types the created query is malformed and throws a "Unknown column" error:
Steps to reproduce the issue:
- install a fresh drupal & activate views, cck & date
- add a date-field "field_date1" to one content-type (e.g. story)
- add a different date-field "field_date2" to a different content-type (e.g. page)
- create a view and add a date-filter with both of the fields.
The resulting error is:user warning: Unknown column 'node_data_field_date1.field_date2_value' in 'where clause' query:
The query is:SELECT node.nid AS nid, node.title AS node_title FROM datetest_node node LEFT JOIN datetest_content_type_story node_data_field_date1 ON node.vid = node_data_field_date1.vid LEFT JOIN datetest_content_type_page node_data_field_date2 ON node.vid = node_data_field_date2.vid WHERE (DATE_FORMAT(STR_TO_DATE(node_data_field_date1.field_date1_value, '%Y-%m-%dT%T'), '%Y-%m-%d') > '2009-09-17') AND (DATE_FORMAT(STR_TO_DATE(node_data_field_date1.field_date2_value, '%Y-%m-%dT%T'), '%Y-%m-%d') > '2009-09-17') LIMIT 0, 10 in .../sites/all/modules/views/includes/view.inc on line 755.
Apparently the query builder assigned the wrong table to the second date-field. the correct field should be "node_data_field_date2.field_date2_value" in this case.
This bug was NOT present in date 2.3 which is insecure, unfortunately. I marked this issue as critical because for now there is just the choice between a working but insecure or a secure but not-working module.
Comments
#1
After a little bit of research I found out that the patch/hack for issue #385688: Views filters & arguments not working when using a date relationship. caused the problem.
#2
Marking this issue as a duplicate of #385688: Views filters & arguments not working when using a date relationship. then...
#3
it's not really a duplicate of #385688: Views filters & arguments not working when using a date relationship., it's an new issue caused by an attempt to fix #385688: Views filters & arguments not working when using a date relationship.
#4
I agree that the solution committed in commit 263582 makes the original problem even worse.
#5
subscribing
#6
I think I have the same problem, too. Subscribing.
#7
I commented out that commit to fix this problem. Now I need to try to fix the original problem without creating this one. It's way late I know, sorry!
#8
I just upgraded from date 6.x-2.4 to 6.x-2.6 and I've got the same error on a view with 2 date fields passed as arguments. When I try with only 1 date field, it works, and everything was OK with date 6.x-2.4.
Here's the warning :
Any idea about what's wrong?
Thanks
#9
Got a similar problem with version 6.x-2.6 and the calendar 6.x-2.2 module. After updating to new version of date module, calendar views were broken and no dates were visible on the calendar. Reverting back to 6.x-2.4 of date fixed the issue.
#10
I had the same problem. Upgraded to 2.6 and all of my calendar views were blank. Reverted back to 2.4 fixed the issue.
#11
Same issue here with the newest version - will be forced to re-migrate to old version because it's for a critical feature of a customer's website!
#12
subscribing with identical scenario to chrixix.
Thanks to fmesco for suggesting the fix. Reverting back to date-6.x-2.4 worked but, interestingly, reverting to date-6.x-2.5 did not initially do the trick.
Thanks to the developers for working on a resolution to this issue.
#13
Subscribe. Reverting to older version of Date until this is fixed. Thanks for all of your work on this, Karen and company.
#14
Subscribing. Think I have the same problem, calendar events disappear when updating Date. I did not get any error messages, date links simply disappeared from the calendar.
Also getting this weird error but it does not appear to be causing problems. Who knows?
The date field 'Content: Plan Proposed (field_bk_plan_proposed_1)' used by the display 'Year view' cannot be set to 'Group multiple values'.
The error applies to Day/Week/etc. views but I can not tell if it is causing any actual problems.
#15
obviously, it's a date 6.x-2.5 issue ; I had the problem when I upgraded to 6.x-2.5, and when I saw a 6.X-2.6 release of the module a few hours later, I thought it would solve my problem... but this was an utopia. So, I'm like everybody here : I reverted to date 6.x-2.4. I hope there'll be an issue to this!
#16
OK, I thought I was going crazy, thinking something was wrong with my view. Glad to see there are other more knowledgeable giving feedback.
#17
Subscribe.
#18
subscribe
#19
Same problem - subscribe
#20
Subscribe. Any progress on fixing this other than reverting?
#21
Subscribe
#22
My first go at bug-fixing and patch submission. Patched it against the dev version but there is not much difference to 2.6 so try it out for me.
Tracked it down to date_api_argument_handler.inc
This section in particular:
<?phpforeach ($this->query_fields as $query_field) {
$field = $query_field['field'];
$this->related_table_alias = $this->query->queue_table($field['table_name'], $this->relationship);
}
?>
With multiple date fields being processed by this loop, only the last table alias is saved as related_table_alias and referenced later.
Hope it helps.
DONT use the patch in this comment, use the one further down. Realised it was wrong after I posted it.
#23
Changed component to Date API
#24
I've tested this patch and it works well (almost it resolves the issue problem, but I don't know if it has some controindication).
Saxx
#25
OK, after testing further I realised my changes never actually passed on the generated table alias. Don't know anything about when table aliases are used but its probably generated for a reason.
Use this patch instead.
#26
I can confirm that also this patch works correctly!
#27
I have no idea what this means, but sounds good.
Thank you!
#28
Works for me. Thanks Liam!
#29
Patch #25 seems to work here, too. Thanks a lot!
Liam, did you test it against issue #385688: Views filters & arguments not working when using a date relationship. (whose hack originally caused this bug)?
#30
No, I didn't see that thread. I'll post it there too.
You're right, thats where is all started. This is where the code I patched came from: http://drupal.org/node/385688#comment-2218938
#31
Thanks so much - this patch worked great! I was trying to display 2 CCK types in the Calendar (each had different date fields), and Views kept throwing an error when I tried to use both date fields in the Argument. This patch fixed it beautifully!! : )
#32
I really appreciate the efforts of Liam Mitchell to patch this problem. However, I am unclear as to how developers can recommend versions for installation if these versions require patches to function.
Would someone please change the the status of 'Date 6.x-2.6' from "recommended version" to "also available" as it appears in the update modules "Available updates" page?
I got tricked into updating to both Date 6.x-2.5 and -2.6 before these versions were ready for production sites. It's just lucky that I could copy over the directory with the last working version (-2.4) without reverting to a backup SQL database.
Much site content now depends upon this important module and developer efforts are very much appreciated!
Thanks!
#33
Thank you Liam, date_table_alias_bug2.patch worked great!
#34
Thanks Liam for the patch in #25. I just applied this to the latest Date 6.x-2.x-dev version dated 2010-10-11 and it applied without difficulty and solved my problems showing as errors in watchdog.
Since others have also found this patch works I am changing the status to RTBC. Hope that's ok.
Thanks very much,
Izzy
#35
Liam, do you have an update with this patch in it. I do not know how to apply a patch and I am scared to do it. I am not that much of a techie.
Thank you
James
#36
I have looked around a lot for details on how to apply patches.
There are plenty of directions such as "Apply Tortoise SVN" or such but nothing that makes it plain enough for newcomers.
If anyone knows of any step by step, current, tutorials, please post.
Thanks.
#37
There are instructions on drupal.org on how to apply patches or you can do it manually. This patch is only a few lines so you can do it manually. Open it in a text viewer or editor like notepad++ then identify which file(s) are being altered, look for the lines preceded by a "-" and remove those line. Add the lines preceded by a "+".
#38
Oh thanks, I was reacting generally.
I have applied manual patches.
It is the long ones that I can't get done.
And Notepad++ is great.
Thank you.
#39
For more details on patches you may have already seen the book page:
http://drupal.org/patch/apply
#40
#25 worked for me - thanks!
Does it become part of the module?
#41
Cross posting related issues: the patch here looks conceptually quite similar to the one in #385688-91: Views filters & arguments not working when using a date relationship. (using the related_table_alias for the query if it's set). Would be nice to get everyone in a single issue, agree on a patch, and get it commited.
#42
Subscribe
#43
#25 also worked for me. Thanks!!!!
#44
Fixed on a duplicate issue, #385688: Views filters & arguments not working when using a date relationship., but same idea. This should be working right now.
#45
I can also confirm that #25 worked for me to fix the issue in the views module with Relationships that weren't working for Views Arguments to Date Fields. Thanks! I had to ensure that I used date-6.x-2.x-dev for it to work.
#46
Thanks, #25 seems to have solved the problem for me too (6.x-2.6). It would be nice to see it in the next stable release!
#47
Good to know it works. If this is still an issue with the latest dev version, change the status from fixed back to reviewed and tested by community so people know it still isn't fixed.
#48
Actually, the situation is "complicated"... for example: the original issue of using date-fields in a filter (see title) was "fixed" in 6.x-2.6, but is again present in the latest dev-version (and patch #25 doesn't help there, too)! :(
To make the whole mess a little more transparent I tested some example views with a fresh installation of Drupal and different versions of date (2.6 and dev; unpatched and patched with #25). The results are attached.
So, neither patch #25 nor date-6.x-dev do improve the overall situation by much. :(
#49
Subscribe... reverting to the old version of the date and calendar module.
#50
This is still a big problem with the latest development versions as of 12/17/2010. Still getting an "Unknown column" error and my calendar is blank. I've cleared my caches and updated my database, etc... any clues for a temporary work around? The patch in 25 is apparently committed, but this has no effect on my install: Drupal 6.2, PHP 5.3.2-1ubuntu4.2, and MySQL 5.1.41.
#51
I cannot reproduce this error on the latest code. I have a view with three different date fields, 2 are CCK fields and one is a system date. There are no errors and the query is correct.
If you are still seeing problems I need more information about exactly how to reproduce an error. This means I need to know exactly how the date fields you are using are configured and how you have configured the view.
#52
OK, figured out how to reproduce -- you need different fields and the fields must NOT be in the view. The fix from #385688: Views filters & arguments not working when using a date relationship. seemed to fix that.
#53
Thanks Karen; I'm not sure why, but my calendar is still blank (with the latest development versions of calendar and date), and after flushing caches, resaving the content types for my calendar, etc.. Any ideas?
#54
Using the 6.x-2.x-dev from 12/24: The problem is given in the error message. For some reason, the field definitions in the where clause do not match those created in the select portion. In my case the query looks like this.
SELECT node.nid AS nid,node.title AS node_title,
node_data_field_event_date.field_event_date_value AS node_data_field_event_date_field_event_date_value,
node_data_field_event_date.field_event_date_value2 AS node_data_field_event_date_field_event_date_value2,
node.type AS node_type,
node.vid AS node_vid,
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
FROM node node
LEFT JOIN content_type_event node_data_field_event_date ON node.vid = node_data_field_event_date.vid
LEFT JOIN content_type_temple_service node_data_field_service_date ON node.vid = node_data_field_service_date.vid
WHERE
((node.type in ('event', 'annual', 'temple_service'))
AND (node.status <> 0))
AND (
(
( //..................................>
DATE_FORMAT(ADDTIME(node_data_field_service_date.field_event_date_value, SEC_TO_TIME(-18000)), '%Y-%m') <= '2010-12'
AND
DATE_FORMAT(ADDTIME(node_data_field_service_date.field_event_date_value2, SEC_TO_TIME(-18000)), '%Y-%m') >= '2010-12'
)
)
OR
(
(
DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_service_date.field_service_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-18000)), '%Y-%m') <= '2010-12'
AND
DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_service_date.field_service_date_value2, '%Y-%m-%dT%T'), SEC_TO_TIME(-18000)), '%Y-%m') >= '2010-12'
)
)
)
And the error message is
Unknown column 'node_data_field_service_date.field_event_date_value' in 'where clause' queryAs can be clearly seen that field is incorrect, it should be node_data_field_event_date.field_event_date_value but the view mixed in the second field definition in with the first.
#55
I then applied the patch in #25 which corrected the field names, but now I am getting all kinds of anomalies.
1. All my december dates are listed on a single date - today.
2. All my other entries in previous and subsequent months list themselves over 10 times in each date -- although at least they are on the right date.
Cleared caches - via devel and views cache.
View data is below.
$view = new view;$view->name = 'calendar';
$view->description = 'A multi-dimensional calendar view with back/next navigation.';
$view->tag = 'Calendar';
$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(
'title' => array(
'label' => '',
'link_to_node' => 1,
'exclude' => 0,
'id' => 'title',
'field' => 'title',
'table' => 'node',
'relationship' => 'none',
),
'field_event_date_value' => array(
'label' => 'Event Date',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'multiple_number' => '',
'multiple_from' => '',
'multiple_to' => '',
'group' => '',
),
'repeat' => array(
'show_repeat_rule' => '',
),
'fromto' => array(
'fromto' => 'both',
),
'exclude' => 0,
'id' => 'field_event_date_value',
'table' => 'node_data_field_event_date',
'field' => 'field_event_date_value',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
),
'field_service_date_value' => array(
'label' => '',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'link_to_node' => 0,
'label_type' => 'none',
'format' => 'default',
'multiple' => array(
'multiple_number' => '',
'multiple_from' => '',
'multiple_to' => '',
'group' => '',
),
'repeat' => array(
'show_repeat_rule' => '',
),
'fromto' => array(
'fromto' => 'both',
),
'exclude' => 0,
'id' => 'field_service_date_value',
'table' => 'node_data_field_service_date',
'field' => 'field_service_date_value',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
),
'field_birthday_value' => array(
'label' => 'Birthday',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'multiple_number' => '',
'multiple_from' => '',
'multiple_to' => '',
'group' => '',
),
'repeat' => array(
'show_repeat_rule' => '',
),
'fromto' => array(
'fromto' => 'both',
),
'exclude' => 0,
'id' => 'field_birthday_value',
'table' => 'node_data_field_birthday',
'field' => 'field_birthday_value',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
),
));
$handler->override_option('arguments', array(
'date_argument' => array(
'default_action' => 'default',
'style_plugin' => 'default_summary',
'style_options' => array(),
'wildcard' => 'all',
'wildcard_substitution' => 'All',
'title' => '',
'breadcrumb' => '',
'default_argument_type' => 'date',
'default_argument' => '',
'validate_type' => 'none',
'validate_fail' => 'not found',
'date_fields' => array(
'node_data_field_birthday.field_birthday_value' => 'node_data_field_birthday.field_birthday_value',
'node_data_field_event_date.field_event_date_value' => 'node_data_field_event_date.field_event_date_value',
'node_data_field_service_date.field_service_date_value' => 'node_data_field_service_date.field_service_date_value',
),
'year_range' => '-3:+3',
'date_method' => 'OR',
'granularity' => 'month',
'id' => 'date_argument',
'table' => 'node',
'field' => 'date_argument',
'relationship' => 'none',
'default_argument_user' => 0,
'default_argument_fixed' => '',
'default_argument_php' => '',
'validate_argument_node_type' => array(
'product' => 0,
'event' => 0,
'annual' => 0,
'media' => 0,
'online_presence' => 0,
'page' => 0,
'profile' => 0,
'residency' => 0,
'story' => 0,
'student' => 0,
'temple_service' => 0,
),
'validate_argument_node_access' => 0,
'validate_argument_nid_type' => 'nid',
'validate_argument_vocabulary' => array(
'1' => 0,
),
'validate_argument_type' => 'tid',
'validate_argument_php' => '',
'override' => array(
'button' => 'Override',
),
'default_options_div_prefix' => '',
'validate_user_argument_type' => 'uid',
'validate_user_roles' => array(
'2' => 0,
'3' => 0,
'4' => 0,
'5' => 0,
'6' => 0,
),
'validate_argument_transform' => 0,
'validate_user_restrict_roles' => 0,
'validate_argument_signup_status' => 'any',
'validate_argument_signup_node_access' => 0,
),
));
$handler->override_option('filters', array(
'type' => array(
'operator' => 'in',
'value' => array(
'event' => 'event',
'profile' => 'profile',
'temple_service' => 'temple_service',
),
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'type',
'table' => 'node',
'field' => 'type',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
),
'status' => array(
'operator' => '=',
'value' => 1,
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'status',
'table' => 'node',
'field' => 'status',
'relationship' => 'none',
),
));
$handler->override_option('access', array(
'type' => 'none',
'role' => array(),
'perm' => '',
));
$handler->override_option('cache', array(
'type' => 'none',
));
$handler->override_option('title', 'Calendar');
$handler->override_option('header_empty', 1);
$handler->override_option('items_per_page', 0);
$handler->override_option('use_more', 0);
$handler->override_option('style_plugin', 'calendar_nav');
$handler = $view->new_display('calendar', 'Calendar page', 'calendar_1');
$handler->override_option('row_plugin', '');
$handler->override_option('row_options', NULL);
$handler->override_option('path', 'calendar');
$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,
'name' => 'navigation',
));
$handler->override_option('calendar_colors', array(
'0' => array(),
));
$handler->override_option('calendar_colors_vocabulary', array());
$handler->override_option('calendar_colors_taxonomy', array());
$handler->override_option('calendar_colors_group', array());
$handler->override_option('calendar_popup', 0);
$handler->override_option('calendar_date_link', '');
$handler = $view->new_display('calendar_block', 'Calendar block', 'calendar_block_1');
$handler->override_option('row_plugin', '');
$handler->override_option('row_options', NULL);
$handler->override_option('block_description', 'Calendar');
$handler->override_option('block_caching', -1);
$handler = $view->new_display('calendar_period', 'Year view', 'calendar_period_1');
$handler->override_option('style_plugin', 'calendar_style');
$handler->override_option('style_options', array(
'display_type' => 'year',
'name_size' => 1,
'max_items' => 0,
));
$handler->override_option('attachment_position', 'after');
$handler->override_option('inherit_arguments', TRUE);
$handler->override_option('inherit_exposed_filters', TRUE);
$handler->override_option('inherit_pager', FALSE);
$handler->override_option('render_pager', TRUE);
$handler->override_option('displays', array(
'calendar_1' => 'calendar_1',
'default' => 0,
'calendar_block_1' => 0,
));
$handler->override_option('calendar_type', 'year');
$handler = $view->new_display('calendar_period', 'Month view', 'calendar_period_2');
$handler->override_option('style_plugin', 'calendar_style');
$handler->override_option('style_options', array(
'display_type' => 'month',
'name_size' => '99',
'with_weekno' => '1',
'date_fields' => NULL,
'max_items' => 0,
));
$handler->override_option('attachment_position', 'after');
$handler->override_option('inherit_arguments', TRUE);
$handler->override_option('inherit_exposed_filters', TRUE);
$handler->override_option('inherit_pager', FALSE);
$handler->override_option('render_pager', TRUE);
$handler->override_option('displays', array(
'calendar_1' => 'calendar_1',
'default' => 0,
'calendar_block_1' => 0,
));
$handler->override_option('calendar_type', 'month');
$handler = $view->new_display('calendar_period', 'Day view', 'calendar_period_3');
$handler->override_option('style_plugin', 'calendar_style');
$handler->override_option('style_options', array(
'name_size' => '99',
'with_weekno' => 0,
'max_items' => 0,
'max_items_behavior' => 'more',
'groupby_times' => 'hour',
'groupby_times_custom' => '',
'groupby_field' => '',
));
$handler->override_option('attachment_position', 'after');
$handler->override_option('inherit_arguments', TRUE);
$handler->override_option('inherit_exposed_filters', TRUE);
$handler->override_option('inherit_pager', FALSE);
$handler->override_option('render_pager', TRUE);
$handler->override_option('displays', array(
'calendar_1' => 'calendar_1',
'default' => 0,
'calendar_block_1' => 0,
));
$handler->override_option('calendar_type', 'day');
$handler = $view->new_display('calendar_period', 'Week view', 'calendar_period_4');
$handler->override_option('style_plugin', 'calendar_style');
$handler->override_option('style_options', array(
'name_size' => '99',
'with_weekno' => 0,
'max_items' => 0,
'max_items_behavior' => 'more',
'groupby_times' => 'hour',
'groupby_times_custom' => '',
'groupby_field' => '',
));
$handler->override_option('attachment_position', 'after');
$handler->override_option('inherit_arguments', TRUE);
$handler->override_option('inherit_exposed_filters', TRUE);
$handler->override_option('inherit_pager', FALSE);
$handler->override_option('render_pager', TRUE);
$handler->override_option('displays', array(
'calendar_1' => 'calendar_1',
'default' => 0,
'calendar_block_1' => 0,
));
$handler->override_option('calendar_type', 'week');
$handler = $view->new_display('calendar_period', 'Block view', 'calendar_period_5');
$handler->override_option('style_plugin', 'calendar_style');
$handler->override_option('style_options', array(
'display_type' => 'month',
'name_size' => '1',
));
$handler->override_option('attachment_position', 'after');
$handler->override_option('inherit_arguments', TRUE);
$handler->override_option('inherit_exposed_filters', TRUE);
$handler->override_option('inherit_pager', FALSE);
$handler->override_option('render_pager', TRUE);
$handler->override_option('displays', array(
'calendar_1' => 0,
'default' => 0,
'calendar_block_1' => 'calendar_block_1',
));
$handler->override_option('calendar_type', 'month');
$handler = $view->new_display('calendar_ical', 'iCal feed', 'calendar_ical_1');
$handler->override_option('arguments', array());
$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',
),
'date_filter' => array(
'operator' => '>=',
'value' => array(
'value' => NULL,
'min' => NULL,
'max' => NULL,
'default_date' => 'now',
'default_to_date' => '',
),
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'date_fields' => array(
'node.changed' => 'node.changed',
),
'granularity' => 'day',
'form_type' => 'date_select',
'default_date' => 'now',
'default_to_date' => '',
'id' => 'date_filter',
'table' => 'node',
'field' => 'date_filter',
'override' => array(
'button' => 'Use default',
),
'relationship' => 'none',
),
));
$handler->override_option('style_plugin', 'ical');
$handler->override_option('style_options', array(
'mission_description' => FALSE,
'description' => '',
'summary_field' => 'node_title',
'description_field' => '',
'location_field' => '',
));
$handler->override_option('row_plugin', '');
$handler->override_option('path', 'calendar/ical');
$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,
'name' => 'navigation',
));
$handler->override_option('displays', array(
'calendar_1' => 'calendar_1',
'default' => 0,
'calendar_block_1' => 'calendar_block_1',
));
$handler->override_option('sitename_title', FALSE);
$handler = $view->new_display('block', 'Upcoming', 'block_1');
$handler->override_option('fields', array(
'title' => array(
'label' => '',
'link_to_node' => 1,
'exclude' => 0,
'id' => 'title',
'field' => 'title',
'table' => 'node',
'relationship' => 'none',
'format' => 'default',
),
'changed' => array(
'label' => '',
'link_to_node' => 0,
'exclude' => 0,
'id' => 'changed',
'field' => 'changed',
'table' => 'node',
'relationship' => 'none',
'date_format' => 'small',
'format' => 'default',
),
));
$handler->override_option('arguments', array());
$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',
),
'date_filter' => array(
'operator' => '>=',
'value' => array(
'value' => NULL,
'min' => NULL,
'max' => NULL,
'default_date' => 'now',
'default_to_date' => '',
),
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'date_fields' => array(
'node.changed' => 'node.changed',
),
'granularity' => 'day',
'form_type' => 'date_select',
'default_date' => 'now',
'default_to_date' => '',
'id' => 'date_filter',
'table' => 'node',
'field' => 'date_filter',
'override' => array(
'button' => 'Use default',
),
'relationship' => 'none',
),
));
$handler->override_option('title', 'Upcoming');
$handler->override_option('items_per_page', 5);
$handler->override_option('use_more', 1);
$handler->override_option('style_plugin', 'list');
$handler->override_option('style_options', array(
'grouping' => '',
'type' => 'ul',
));
$handler->override_option('block_description', 'Upcoming');
$handler->override_option('block_caching', -1);
#56
#57
I finally fixed it by going back to date-6.x-2.6 and calendar-6.x-2.2 and applying the patch in #25.
I have no idea why it fixed my problems, but the new dev did not fix it for me.
#58
ambereyes, following in your footsteps: returning to date-6.x-2.6 and calendar-6.x-2.2 and applying the patch in #25 does get rid of the unknown column error, but I'm still having trouble getting two content types to display on the calendar... hmmm. I might have messed up something with my views arguments and fields, but they seem to be set properly.
#59
I've tried setting up my date:date (node) arguments several different ways (all in one argument, in separate arguments for each date field-- both "from" and "to" and the single date field for my other content type). I have the view filtering for the right content types, and the node title and date fields included as my fields for the view (everything else I've cloned from the default calendar view). However, I can only get one content type to display at a time. If I add the arguments in certain ways, the entire calendar goes blank. Otherwise, I can only get one content type to show at one time. Here is a copy of my view (I'm trying again with the latest development version of date and calendar):
$view = new view;$view->name = 'alpine_calendar';
$view->description = 'A multi-dimensional calendar view with back/next navigation.';
$view->tag = 'Calendar';
$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(
'title' => array(
'label' => '',
'link_to_node' => 1,
'exclude' => 0,
'id' => 'title',
'field' => 'title',
'table' => 'node',
'relationship' => 'none',
),
'field_date_of_meeting_value' => array(
'label' => 'Date of Meeting',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'multiple_number' => '',
'multiple_from' => '',
'multiple_to' => '',
'group' => '',
),
'repeat' => array(
'show_repeat_rule' => '',
),
'fromto' => array(
'fromto' => 'both',
),
'exclude' => 1,
'id' => 'field_date_of_meeting_value',
'table' => 'node_data_field_date_of_meeting',
'field' => 'field_date_of_meeting_value',
'relationship' => 'none',
),
'field_start_of_reservation_value' => array(
'label' => 'Date(s) and Time(s)',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'multiple_number' => '',
'multiple_from' => '',
'multiple_to' => '',
'group' => 0,
),
'repeat' => array(
'show_repeat_rule' => 'show',
),
'fromto' => array(
'fromto' => 'both',
),
'exclude' => 1,
'id' => 'field_start_of_reservation_value',
'table' => 'node_data_field_start_of_reservation',
'field' => 'field_start_of_reservation_value',
'relationship' => 'none',
),
'field_start_of_reservation_value2' => array(
'label' => 'Date(s) and Time(s)',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'multiple_number' => '',
'multiple_from' => '',
'multiple_to' => '',
'group' => 0,
),
'repeat' => array(
'show_repeat_rule' => 'show',
),
'fromto' => array(
'fromto' => 'both',
),
'exclude' => 1,
'id' => 'field_start_of_reservation_value2',
'table' => 'node_data_field_start_of_reservation',
'field' => 'field_start_of_reservation_value2',
'relationship' => 'none',
),
));
$handler->override_option('sorts', array(
'changed' => array(
'order' => 'ASC',
'delta' => '-1',
'id' => 'changed',
'table' => 'node',
'field' => 'changed',
'relationship' => 'none',
),
));
$handler->override_option('arguments', array(
'date_argument' => array(
'default_action' => 'default',
'style_plugin' => 'default_summary',
'style_options' => array(),
'wildcard' => 'all',
'wildcard_substitution' => 'All',
'title' => '',
'breadcrumb' => '',
'default_argument_type' => 'date',
'default_argument' => '',
'validate_type' => 'none',
'validate_fail' => 'not found',
'date_fields' => array(
'node_data_field_date_of_meeting.field_date_of_meeting_value' => 'node_data_field_date_of_meeting.field_date_of_meeting_value',
),
'year_range' => '-3:+3',
'date_method' => 'OR',
'granularity' => 'month',
'id' => 'date_argument',
'table' => 'node',
'field' => 'date_argument',
'relationship' => 'none',
'default_argument_user' => 0,
'default_argument_fixed' => '',
'default_argument_php' => '',
'validate_argument_node_type' => array(
'casetracker_basic_project' => 0,
'casetracker_basic_case' => 0,
'mass_contact' => 0,
'sitenotes' => 0,
'weblinks' => 0,
'panel' => 0,
'bio' => 0,
'book' => 0,
'contact_information' => 0,
'content_community_center_reserva' => 0,
'content_county_file_or_form_cck' => 0,
'content_file_index' => 0,
'content_group_home_page' => 0,
'content_minutes_or_agenda_cck' => 0,
'content_road_report_cck' => 0,
'county_facility_or_service' => 0,
'custom_adm_script' => 0,
'emerg_notice' => 0,
'node_4web_edit' => 0,
'page' => 0,
'profile' => 0,
'resuseable_location' => 0,
'service_stub' => 0,
'story' => 0,
'webform' => 0,
),
'validate_argument_node_access' => 0,
'validate_argument_nid_type' => 'nid',
'validate_argument_vocabulary' => array(
'30' => 0,
'3' => 0,
'18' => 0,
'9' => 0,
'21' => 0,
'24' => 0,
'25' => 0,
'4' => 0,
'27' => 0,
),
'validate_argument_type' => 'tid',
'validate_argument_php' => '',
'override' => array(
'button' => 'Override',
),
'default_options_div_prefix' => '',
'validate_user_argument_type' => 'uid',
'validate_user_roles' => array(
'2' => 0,
'4' => 0,
'39' => 0,
'10' => 0,
'44' => 0,
'14' => 0,
'9' => 0,
'6' => 0,
'15' => 0,
'11' => 0,
'17' => 0,
'46' => 0,
'45' => 0,
'7' => 0,
'8' => 0,
'42' => 0,
'41' => 0,
),
'validate_argument_node_access_op' => 'view',
'validate_argument_transform' => 0,
'validate_user_restrict_roles' => 0,
),
'date_argument_1' => array(
'default_action' => 'default',
'style_plugin' => 'default_summary',
'style_options' => array(),
'wildcard' => 'all',
'wildcard_substitution' => 'All',
'title' => '',
'breadcrumb' => '',
'default_argument_type' => 'date',
'default_argument' => '',
'validate_type' => 'none',
'validate_fail' => 'not found',
'date_fields' => array(
'node_data_field_start_of_reservation.field_start_of_reservation_value' => 'node_data_field_start_of_reservation.field_start_of_reservation_value',
),
'year_range' => '-3:+3',
'date_method' => 'OR',
'granularity' => 'month',
'id' => 'date_argument_1',
'table' => 'node',
'field' => 'date_argument',
'validate_user_argument_type' => 'uid',
'validate_user_roles' => array(
'2' => 0,
'4' => 0,
'39' => 0,
'10' => 0,
'44' => 0,
'14' => 0,
'9' => 0,
'6' => 0,
'15' => 0,
'11' => 0,
'17' => 0,
'46' => 0,
'45' => 0,
'7' => 0,
'8' => 0,
'42' => 0,
'41' => 0,
),
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
'default_options_div_prefix' => '',
'default_argument_fixed' => '',
'default_argument_user' => 0,
'default_argument_php' => '',
'validate_argument_node_type' => array(
'casetracker_basic_project' => 0,
'casetracker_basic_case' => 0,
'mass_contact' => 0,
'sitenotes' => 0,
'weblinks' => 0,
'panel' => 0,
'bio' => 0,
'book' => 0,
'contact_information' => 0,
'content_community_center_reserva' => 0,
'content_county_file_or_form_cck' => 0,
'content_file_index' => 0,
'content_group_home_page' => 0,
'content_minutes_or_agenda_cck' => 0,
'content_road_report_cck' => 0,
'county_facility_or_service' => 0,
'custom_adm_script' => 0,
'emerg_notice' => 0,
'node_4web_edit' => 0,
'page' => 0,
'profile' => 0,
'resuseable_location' => 0,
'service_stub' => 0,
'story' => 0,
'webform' => 0,
),
'validate_argument_node_access' => 0,
'validate_argument_node_access_op' => 'view',
'validate_argument_nid_type' => 'nid',
'validate_argument_vocabulary' => array(
'30' => 0,
'3' => 0,
'18' => 0,
'9' => 0,
'21' => 0,
'24' => 0,
'25' => 0,
'4' => 0,
'27' => 0,
),
'validate_argument_type' => 'tid',
'validate_argument_transform' => 0,
'validate_user_restrict_roles' => 0,
'validate_argument_php' => '',
),
'date_argument_2' => array(
'default_action' => 'default',
'style_plugin' => 'default_summary',
'style_options' => array(),
'wildcard' => 'all',
'wildcard_substitution' => 'All',
'title' => '',
'breadcrumb' => '',
'default_argument_type' => 'date',
'default_argument' => '',
'validate_type' => 'none',
'validate_fail' => 'not found',
'date_fields' => array(
'node_data_field_start_of_reservation.field_start_of_reservation_value2' => 'node_data_field_start_of_reservation.field_start_of_reservation_value2',
),
'year_range' => '-3:+3',
'date_method' => 'OR',
'granularity' => 'month',
'id' => 'date_argument_2',
'table' => 'node',
'field' => 'date_argument',
'validate_user_argument_type' => 'uid',
'validate_user_roles' => array(
'2' => 0,
'4' => 0,
'39' => 0,
'10' => 0,
'44' => 0,
'14' => 0,
'9' => 0,
'6' => 0,
'15' => 0,
'11' => 0,
'17' => 0,
'46' => 0,
'45' => 0,
'7' => 0,
'8' => 0,
'42' => 0,
'41' => 0,
),
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
'default_options_div_prefix' => '',
'default_argument_fixed' => '',
'default_argument_user' => 0,
'default_argument_php' => '',
'validate_argument_node_type' => array(
'casetracker_basic_project' => 0,
'casetracker_basic_case' => 0,
'mass_contact' => 0,
'sitenotes' => 0,
'weblinks' => 0,
'panel' => 0,
'bio' => 0,
'book' => 0,
'contact_information' => 0,
'content_community_center_reserva' => 0,
'content_county_file_or_form_cck' => 0,
'content_file_index' => 0,
'content_group_home_page' => 0,
'content_minutes_or_agenda_cck' => 0,
'content_road_report_cck' => 0,
'county_facility_or_service' => 0,
'custom_adm_script' => 0,
'emerg_notice' => 0,
'node_4web_edit' => 0,
'page' => 0,
'profile' => 0,
'resuseable_location' => 0,
'service_stub' => 0,
'story' => 0,
'webform' => 0,
),
'validate_argument_node_access' => 0,
'validate_argument_node_access_op' => 'view',
'validate_argument_nid_type' => 'nid',
'validate_argument_vocabulary' => array(
'30' => 0,
'3' => 0,
'18' => 0,
'9' => 0,
'21' => 0,
'24' => 0,
'25' => 0,
'4' => 0,
'27' => 0,
),
'validate_argument_type' => 'tid',
'validate_argument_transform' => 0,
'validate_user_restrict_roles' => 0,
'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(
'content_community_center_reserva' => 'content_community_center_reserva',
'content_minutes_or_agenda_cck' => 'content_minutes_or_agenda_cck',
),
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'type',
'table' => 'node',
'field' => 'type',
'relationship' => 'none',
'override' => array(
'button' => 'Override',
),
),
));
$handler->override_option('access', array(
'type' => 'none',
'role' => array(),
'perm' => '',
));
$handler->override_option('cache', array(
'type' => 'none',
));
$handler->override_option('title', 'Calendar');
$handler->override_option('header_empty', 1);
$handler->override_option('items_per_page', 0);
$handler->override_option('use_more', 0);
$handler->override_option('style_plugin', 'calendar_nav');
$handler = $view->new_display('calendar', 'Calendar page', 'calendar_1');
$handler->override_option('style_options', NULL);
$handler->override_option('row_plugin', '');
$handler->override_option('row_options', NULL);
$handler->override_option('path', 'calendar');
$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,
'name' => 'navigation',
));
$handler->override_option('calendar_colors', array(
'0' => array(),
));
$handler->override_option('calendar_colors_vocabulary', array());
$handler->override_option('calendar_colors_taxonomy', array());
$handler->override_option('calendar_popup', 0);
$handler->override_option('calendar_date_link', '');
$handler = $view->new_display('calendar_block', 'Calendar block', 'calendar_block_1');
$handler->override_option('style_options', NULL);
$handler->override_option('row_plugin', '');
$handler->override_option('row_options', NULL);
$handler->override_option('block_description', 'Calendar');
$handler->override_option('block_caching', -1);
$handler = $view->new_display('calendar_period', 'Year view', 'calendar_period_1');
$handler->override_option('style_plugin', 'calendar_style');
$handler->override_option('style_options', array(
'display_type' => 'year',
'name_size' => 1,
'max_items' => 0,
));
$handler->override_option('attachment_position', 'after');
$handler->override_option('inherit_arguments', TRUE);
$handler->override_option('inherit_exposed_filters', TRUE);
$handler->override_option('inherit_pager', FALSE);
$handler->override_option('render_pager', TRUE);
$handler->override_option('displays', array(
'calendar_1' => 'calendar_1',
'default' => 0,
'calendar_block_1' => 0,
));
$handler->override_option('calendar_type', 'year');
$handler = $view->new_display('calendar_period', 'Month view', 'calendar_period_2');
$handler->override_option('style_plugin', 'calendar_style');
$handler->override_option('style_options', array(
'display_type' => 'month',
'name_size' => '99',
'with_weekno' => '1',
'date_fields' => NULL,
'max_items' => 0,
));
$handler->override_option('attachment_position', 'after');
$handler->override_option('inherit_arguments', TRUE);
$handler->override_option('inherit_exposed_filters', TRUE);
$handler->override_option('inherit_pager', FALSE);
$handler->override_option('render_pager', TRUE);
$handler->override_option('displays', array(
'calendar_1' => 'calendar_1',
'default' => 0,
'calendar_block_1' => 0,
));
$handler->override_option('calendar_type', 'month');
$handler = $view->new_display('calendar_period', 'Day view', 'calendar_period_3');
$handler->override_option('style_plugin', 'calendar_style');
$handler->override_option('style_options', array(
'name_size' => '99',
'with_weekno' => 0,
'max_items' => 0,
'max_items_behavior' => 'more',
'groupby_times' => 'hour',
'groupby_times_custom' => '',
'groupby_field' => '',
));
$handler->override_option('attachment_position', 'after');
$handler->override_option('inherit_arguments', TRUE);
$handler->override_option('inherit_exposed_filters', TRUE);
$handler->override_option('inherit_pager', FALSE);
$handler->override_option('render_pager', TRUE);
$handler->override_option('displays', array(
'calendar_1' => 'calendar_1',
'default' => 0,
'calendar_block_1' => 0,
));
$handler->override_option('calendar_type', 'day');
$handler = $view->new_display('calendar_period', 'Week view', 'calendar_period_4');
$handler->override_option('style_plugin', 'calendar_style');
$handler->override_option('style_options', array(
'name_size' => '99',
'with_weekno' => 0,
'max_items' => 0,
'max_items_behavior' => 'more',
'groupby_times' => 'hour',
'groupby_times_custom' => '',
'groupby_field' => '',
));
$handler->override_option('attachment_position', 'after');
$handler->override_option('inherit_arguments', TRUE);
$handler->override_option('inherit_exposed_filters', TRUE);
$handler->override_option('inherit_pager', FALSE);
$handler->override_option('render_pager', TRUE);
$handler->override_option('displays', array(
'calendar_1' => 'calendar_1',
'default' => 0,
'calendar_block_1' => 0,
));
$handler->override_option('calendar_type', 'week');
$handler = $view->new_display('calendar_period', 'Block view', 'calendar_period_5');
$handler->override_option('style_plugin', 'calendar_style');
$handler->override_option('style_options', array(
'display_type' => 'month',
'name_size' => '1',
));
$handler->override_option('attachment_position', 'after');
$handler->override_option('inherit_arguments', TRUE);
$handler->override_option('inherit_exposed_filters', TRUE);
$handler->override_option('inherit_pager', FALSE);
$handler->override_option('render_pager', TRUE);
$handler->override_option('displays', array(
'calendar_1' => 0,
'default' => 0,
'calendar_block_1' => 'calendar_block_1',
));
$handler->override_option('calendar_type', 'month');
$handler = $view->new_display('calendar_ical', 'iCal feed', 'calendar_ical_1');
$handler->override_option('arguments', array());
$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',
),
'date_filter' => array(
'operator' => '>=',
'value' => array(
'value' => NULL,
'min' => NULL,
'max' => NULL,
'default_date' => 'now',
'default_to_date' => '',
),
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'date_fields' => array(
'node.changed' => 'node.changed',
),
'granularity' => 'day',
'form_type' => 'date_select',
'default_date' => 'now',
'default_to_date' => '',
'id' => 'date_filter',
'table' => 'node',
'field' => 'date_filter',
'override' => array(
'button' => 'Use default',
),
'relationship' => 'none',
),
));
$handler->override_option('style_plugin', 'ical');
$handler->override_option('style_options', array(
'mission_description' => FALSE,
'description' => '',
'summary_field' => 'node_title',
'description_field' => '',
'location_field' => '',
));
$handler->override_option('row_plugin', '');
$handler->override_option('path', 'calendar/ical');
$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,
'name' => 'navigation',
));
$handler->override_option('displays', array(
'calendar_1' => 'calendar_1',
'default' => 0,
'calendar_block_1' => 'calendar_block_1',
));
$handler->override_option('sitename_title', FALSE);
$handler = $view->new_display('block', 'Upcoming', 'block_1');
$handler->override_option('fields', array(
'title' => array(
'label' => '',
'link_to_node' => 1,
'exclude' => 0,
'id' => 'title',
'field' => 'title',
'table' => 'node',
'relationship' => 'none',
'format' => 'default',
),
'changed' => array(
'label' => '',
'link_to_node' => 0,
'exclude' => 0,
'id' => 'changed',
'field' => 'changed',
'table' => 'node',
'relationship' => 'none',
'date_format' => 'small',
'format' => 'default',
),
));
$handler->override_option('arguments', array());
$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',
),
'date_filter' => array(
'operator' => '>=',
'value' => array(
'value' => NULL,
'min' => NULL,
'max' => NULL,
'default_date' => 'now',
'default_to_date' => '',
),
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'date_fields' => array(
'node.changed' => 'node.changed',
),
'granularity' => 'day',
'form_type' => 'date_select',
'default_date' => 'now',
'default_to_date' => '',
'id' => 'date_filter',
'table' => 'node',
'field' => 'date_filter',
'override' => array(
'button' => 'Use default',
),
'relationship' => 'none',
),
));
$handler->override_option('title', 'Upcoming');
$handler->override_option('items_per_page', 5);
$handler->override_option('use_more', 1);
$handler->override_option('style_plugin', 'list');
$handler->override_option('style_options', array(
'grouping' => '',
'type' => 'ul',
));
$handler->override_option('block_description', 'Upcoming');
$handler->override_option('block_caching', -1);
#60
Since the "unknown column" error no longer seems to be the problem, I've created a new issue for my problem with multiple content types with date fields here:
http://drupal.org/node/1010848
#61
Amber Eyes, things appear to be working now that I've deleted my date: date arguments and rebuilt them in my view; Amber can you confirm this could resolve your problem? For now, I'm marking this issue as fixed, as the problem is no longer present after using the latest official release and deleting my date arguments.
#62
I have updated to newest dev version - but without patch from #25 I have still the sql errors
I have cleared caches, view attached, with #25 patch it is working
* user warning: Unknown column 'node_data_field_datum.field_datum_value' in 'where clause' query: SELECT DISTINCT node.nid AS nid, GREATEST(node.changed, node_comment_statistics.last_comment_timestamp) AS node_comment_statistics_last_updated, node.sticky AS node_sticky, node.type AS node_type FROM node node LEFT JOIN og_ancestry og_ancestry ON node.nid = og_ancestry.nid LEFT JOIN node node_og_ancestry ON og_ancestry.group_nid = node_og_ancestry.nid INNER JOIN term_node term_node ON node.vid = term_node.vid INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid WHERE ((node.status <> 0) AND (term_node.tid = 6075)) AND (((DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2011-01-04' AND DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value2, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-01-04')) OR ((DATE_FORMAT(STR_TO_DATE(node_data_field__kommerzielles_datum.field__kommerzielles_datum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2011-01-04' AND DATE_FORMAT(STR_TO_DATE(node_data_field__kommerzielles_datum.field__kommerzielles_datum_value2, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-01-04')) OR ((DATE_FORMAT(STR_TO_DATE(node_data_field_privatdatum.field_privatdatum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2011-01-04' AND DATE_FORMAT(STR_TO_DATE(node_data_field_privatdatum.field_privatdatum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-01-04'))) ORDER BY node_comment_statistics_last_updated DESC, node_sticky DESC in /www/htdocs/w00b40cd/com/at/sites/all/modules/views/includes/view.inc on line 775.
* user warning: Unknown column 'node_data_field__kommerzielles_datum.field__kommerzielles_datum_' in 'where clause' query: SELECT DISTINCT node.nid AS nid, node.title AS node_title, node.language AS node_language, node.type AS node_type, node.vid AS node_vid, og_ancestry.nid AS og_ancestry_nid, node_og_ancestry_node_data_field_gruppenbild.field_gruppenbild_fid AS node_og_ancestry_node_data_field_gruppenbild_field_gruppenbild_fid, node_og_ancestry_node_data_field_gruppenbild.field_gruppenbild_list AS node_og_ancestry_node_data_field_gruppenbild_field_gruppenbild_list, node_og_ancestry_node_data_field_gruppenbild.field_gruppenbild_data AS node_og_ancestry_node_data_field_gruppenbild_field_gruppenbild_data, node_og_ancestry.nid AS node_og_ancestry_nid, node_og_ancestry.language AS node_og_ancestry_language, node_og_ancestry.type AS node_og_ancestry_type, node_og_ancestry.vid AS node_og_ancestry_vid, 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_data_field_datum.field_datum_rrule AS node_data_field_datum_field_datum_rrule, node_data_field_datum.delta AS node_data_field_datum_delta, 0 AS all_tid_all_tids, 0 AS all_tid, GREATEST(node.changed, node_comment_statistics.last_comment_timestamp) AS node_comment_statistics_last_updated, node.sticky AS node_sticky FROM node node LEFT JOIN og_ancestry og_ancestry ON node.nid = og_ancestry.nid LEFT JOIN node node_og_ancestry ON og_ancestry.group_nid = node_og_ancestry.nid INNER JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN content_type_gruppe node_og_ancestry_node_data_field_gruppenbild ON node_og_ancestry.vid = node_og_ancestry_node_data_field_gruppenbild.vid LEFT JOIN content_field_datum node_data_field_datum ON node.vid = node_data_field_datum.vid INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid WHERE ((node.status <> 0) AND (term_node.tid = 6075)) AND (((DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2011-01-04' AND DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value2, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-01-04')) OR ((DATE_FORMAT(STR_TO_DATE(node_data_field__kommerzielles_datum.field__kommerzielles_datum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2011-01-04' AND DATE_FORMAT(STR_TO_DATE(node_data_field__kommerzielles_datum.field__kommerzielles_datum_value2, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-01-04')) OR ((DATE_FORMAT(STR_TO_DATE(node_data_field_privatdatum.field_privatdatum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2011-01-04' AND DATE_FORMAT(STR_TO_DATE(node_data_field_privatdatum.field_privatdatum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-01-04'))) ORDER BY node_comment_statistics_last_updated DESC, node_sticky DESC in /www/htdocs/w00b40cd/com/at/sites/all/modules/views/includes/view.inc on line 775.
#63
Mine is a very similar problem in which I have a single content type with multiple date fields (a date range field and a singular dates with times, both of which have two different tables: 'content_type_event' for the date range and 'content_field_event_date' for the singular dates. My view is set up to use the date range as the date argument handler but display the singular event dates to the user as part of the fields. My view looks like:
$view = new view;$view->name = 'calarts_calendar';
$view->description = 'A multi-dimensional calendar view with back/next navigation. CalArts specific';
$view->tag = 'Calendar';
$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(
'title' => array(
'label' => '',
'link_to_node' => 1,
'exclude' => 0,
'id' => 'title',
'field' => 'title',
'table' => 'node',
'relationship' => 'none',
),
'field_event_date_range_value2' => array(
'label' => '',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'link_to_node' => 0,
'label_type' => 'none',
'format' => 'default',
'multiple' => array(
'multiple_number' => '',
'multiple_from' => '',
'multiple_to' => '',
'group' => '',
),
'repeat' => array(
'show_repeat_rule' => '',
),
'fromto' => array(
'fromto' => 'both',
),
'exclude' => 0,
'id' => 'field_event_date_range_value2',
'table' => 'node_data_field_event_date_range',
'field' => 'field_event_date_range_value2',
'relationship' => 'none',
),
));
$handler->override_option('sorts', array(
'field_event_date_value' => array(
'order' => 'ASC',
'delta' => '-1',
'id' => 'field_event_date_value',
'table' => 'node_data_field_event_date',
'field' => 'field_event_date_value',
'relationship' => 'none',
),
));
$handler->override_option('arguments', array(
'date_argument' => array(
'default_action' => 'default',
'style_plugin' => 'default_summary',
'style_options' => array(),
'wildcard' => 'all',
'wildcard_substitution' => 'All',
'title' => '',
'breadcrumb' => '',
'default_argument_type' => 'date',
'default_argument' => '',
'validate_type' => 'none',
'validate_fail' => 'not found',
'date_fields' => array(
'node_data_field_event_date_range.field_event_date_range_value2' => 'node_data_field_event_date_range.field_event_date_range_value2',
),
'year_range' => '-3:+3',
'date_method' => 'OR',
'granularity' => 'month',
'id' => 'date_argument',
'table' => 'node',
'field' => 'date_argument',
'relationship' => 'none',
'default_argument_user' => 0,
'default_argument_fixed' => '',
'default_argument_php' => '',
'validate_argument_node_type' => array(
'audio_item' => 0,
'gallery' => 0,
'gallery_collection' => 0,
'photo_item' => 0,
'video_item' => 0,
'blog_entry' => 0,
'blog_feed' => 0,
'employment_listing' => 0,
'event' => 0,
'frontpage_link' => 0,
'news' => 0,
'page' => 0,
'program' => 0,
'quote' => 0,
'directory_profile' => 0,
'webform' => 0,
),
'validate_argument_node_access' => 0,
'validate_argument_nid_type' => 'nid',
'validate_argument_vocabulary' => array(
'1' => 0,
'2' => 0,
'3' => 0,
'4' => 0,
'5' => 0,
'6' => 0,
'7' => 0,
'8' => 0,
'11' => 0,
'9' => 0,
'12' => 0,
),
'validate_argument_type' => 'tid',
'validate_argument_php' => '',
'override' => array(
'button' => 'Override',
),
'default_options_div_prefix' => '',
'validate_user_argument_type' => 'uid',
'validate_user_roles' => array(
'2' => 0,
'3' => 0,
'5' => 0,
'4' => 0,
'6' => 0,
),
'validate_argument_transform' => 0,
'validate_user_restrict_roles' => 0,
'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',
),
));
$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',
),
'current_all' => array(
'operator' => '=',
'value' => '1',
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'current_all',
'table' => 'domain_access',
'field' => 'current_all',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
),
'field_event_categories_value' => array(
'operator' => 'or',
'value' => array(),
'group' => '0',
'exposed' => TRUE,
'expose' => array(
'use_operator' => 0,
'operator' => 'field_event_categories_value_op',
'identifier' => 'category',
'label' => 'Filter by:',
'optional' => 1,
'single' => 1,
'remember' => 0,
'reduce' => 0,
),
'id' => 'field_event_categories_value',
'table' => 'node_data_field_event_categories',
'field' => 'field_event_categories_value',
'relationship' => 'none',
'reduce_duplicates' => 0,
'override' => array(
'button' => 'Override',
),
),
));
$handler->override_option('access', array(
'type' => 'none',
'role' => array(),
'perm' => '',
));
$handler->override_option('cache', array(
'type' => 'none',
));
$handler->override_option('title', 'Calendar');
$handler->override_option('header_empty', 1);
$handler->override_option('empty', '<p>No events posted for this period</p>');
$handler->override_option('empty_format', '2');
$handler->override_option('items_per_page', 0);
$handler->override_option('use_more', 0);
$handler->override_option('distinct', 1);
$handler->override_option('style_plugin', 'calendar_nav');
$handler = $view->new_display('calendar', 'Calendar page', 'calendar_1');
$handler->override_option('fields', array(
'title' => array(
'label' => '',
'link_to_node' => 1,
'exclude' => 0,
'id' => 'title',
'field' => 'title',
'table' => 'node',
'relationship' => 'none',
),
'field_event_date_value2' => array(
'label' => '',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'link_to_node' => 0,
'label_type' => 'none',
'format' => 'default',
'multiple' => array(
'multiple_number' => '',
'multiple_from' => '',
'multiple_to' => '',
'group' => 0,
),
'repeat' => array(
'show_repeat_rule' => '',
),
'fromto' => array(
'fromto' => 'value',
),
'exclude' => 0,
'id' => 'field_event_date_value2',
'table' => 'node_data_field_event_date',
'field' => 'field_event_date_value2',
'relationship' => 'none',
'override' => array(
'button' => 'Use default',
),
),
));
$handler->override_option('style_options', NULL);
$handler->override_option('row_plugin', '');
$handler->override_option('row_options', NULL);
$handler->override_option('path', 'calendar');
$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,
'name' => 'navigation',
));
$handler->override_option('calendar_colors', array(
'0' => array(),
));
$handler->override_option('calendar_colors_vocabulary', array());
$handler->override_option('calendar_colors_taxonomy', array());
$handler->override_option('calendar_colors_group', array());
$handler->override_option('calendar_popup', '1');
$handler->override_option('calendar_date_link', '');
$handler = $view->new_display('calendar_block', 'Calendar block', 'calendar_block_1');
$handler->override_option('style_options', NULL);
$handler->override_option('row_plugin', '');
$handler->override_option('row_options', NULL);
$handler->override_option('block_description', 'Calendar');
$handler->override_option('block_caching', -1);
$handler = $view->new_display('calendar_period', 'Month view', 'calendar_period_2');
$handler->override_option('fields', array(
'title' => array(
'label' => '',
'link_to_node' => 1,
'exclude' => 0,
'id' => 'title',
'field' => 'title',
'table' => 'node',
'relationship' => 'none',
),
'field_event_date_value2' => array(
'label' => '',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'link_to_node' => 0,
'label_type' => 'none',
'format' => 'default',
'multiple' => array(
'multiple_number' => '',
'multiple_from' => '',
'multiple_to' => '',
'group' => 0,
),
'repeat' => array(
'show_repeat_rule' => '',
),
'fromto' => array(
'fromto' => 'both',
),
'exclude' => 0,
'id' => 'field_event_date_value2',
'table' => 'node_data_field_event_date',
'field' => 'field_event_date_value2',
'relationship' => 'none',
'override' => array(
'button' => 'Override',
),
),
'nid' => array(
'label' => 'Nid',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'link_to_node' => 0,
'exclude' => 0,
'id' => 'nid',
'table' => 'node',
'field' => 'nid',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
),
'field_content_description_value' => array(
'label' => '',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'link_to_node' => 0,
'label_type' => 'none',
'format' => 'default',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 1,
'id' => 'field_content_description_value',
'table' => 'node_data_field_content_description',
'field' => 'field_content_description_value',
'override' => array(
'button' => 'Use default',
),
'relationship' => 'none',
),
));
$handler->override_option('empty', 'There are no events of this type at this time.');
$handler->override_option('empty_format', '1');
$handler->override_option('style_plugin', 'default');
$handler->override_option('style_options', array(
'grouping' => '',
));
$handler->override_option('row_plugin', 'node');
$handler->override_option('row_options', array(
'relationship' => 'none',
'build_mode' => 'full',
'links' => 1,
'comments' => 0,
));
$handler->override_option('attachment_position', 'after');
$handler->override_option('inherit_arguments', TRUE);
$handler->override_option('inherit_exposed_filters', TRUE);
$handler->override_option('inherit_pager', FALSE);
$handler->override_option('render_pager', TRUE);
$handler->override_option('displays', array(
'calendar_1' => 'calendar_1',
'default' => 0,
'calendar_block_1' => 0,
));
$handler->override_option('calendar_type', 'month');
$handler = $view->new_display('calendar_period', 'Day view', 'calendar_period_3');
$handler->override_option('style_plugin', 'default');
$handler->override_option('style_options', array(
'grouping' => '',
));
$handler->override_option('row_plugin', 'node');
$handler->override_option('row_options', array(
'relationship' => 'none',
'build_mode' => 'full',
'links' => 1,
'comments' => 0,
));
$handler->override_option('attachment_position', 'after');
$handler->override_option('inherit_arguments', TRUE);
$handler->override_option('inherit_exposed_filters', TRUE);
$handler->override_option('inherit_pager', FALSE);
$handler->override_option('render_pager', TRUE);
$handler->override_option('displays', array(
'calendar_1' => 'calendar_1',
'default' => 0,
'calendar_block_1' => 0,
));
$handler->override_option('calendar_type', 'day');
$handler = $view->new_display('calendar_period', 'Block view', 'calendar_period_5');
$handler->override_option('style_plugin', 'calendar_style');
$handler->override_option('style_options', array(
'name_size' => '3',
'with_weekno' => '0',
'max_items' => '0',
'max_items_behavior' => 'more',
'groupby_times' => 'hour',
'groupby_times_custom' => '',
'groupby_field' => '',
));
$handler->override_option('attachment_position', 'after');
$handler->override_option('inherit_arguments', TRUE);
$handler->override_option('inherit_exposed_filters', TRUE);
$handler->override_option('inherit_pager', 1);
$handler->override_option('render_pager', 0);
$handler->override_option('displays', array(
'calendar_block_1' => 'calendar_block_1',
'default' => 0,
'calendar_1' => 0,
));
$handler->override_option('calendar_type', 'month');
The query it attempts to run
SELECT DISTINCT(node.nid) AS nid,node.title AS node_title,
node_data_field_event_date.field_event_date_value2 AS node_data_field_event_date_field_event_date_value2,
node_data_field_event_date.field_event_date_value AS node_data_field_event_date_field_event_date_value,
node_data_field_event_date.delta AS node_data_field_event_date_delta,
node.type AS node_type,
node.vid AS node_vid
FROM node node
LEFT JOIN domain_access domain_access ON node.nid = domain_access.nid
LEFT JOIN content_field_event_date node_data_field_event_date ON node.vid = node_data_field_event_date.vid
WHERE ((node.status <> 0) AND (((domain_access.realm = 'domain_id' AND domain_access.gid = ***CURRENT_DOMAIN***) OR (domain_access.realm = 'domain_site' AND domain_access.gid = 0))))
AND ((DATE_FORMAT(STR_TO_DATE(node_data_field_event_date_range.field_event_date_range_value, '%Y-%m-%%dT%T'), '%Y-%m') <= '2011-01' AND DATE_FORMAT(STR_TO_DATE(node_data_field_event_date_range.field_event_date_range_value2, '%Y-%m-%%dT%T'), '%Y-%m') >= '2011-01'))
GROUP BY nid
ORDER BY node_data_field_event_date_field_event_date_value ASC
When I upgraded from 2.6 to 2.7, the errors described above started to occur. I tried via rebuilding my view over and the problem still seemed to occur (I also tried by applying the patch and rebuilding the view - no luck). As you can see, it does not do a left join against the table that contains the date range (in my schema, it is named 'content_type_event'). When I reverted date to the 2.6 version, everything worked again (to be more specific the problem is stemming from the date_api_argument_handler.inc file though I'm unsure on which changeset might be a part of the issue. I verified this by reverting the include file to the 2.6 version after which things look to work).
#64
I've just upgrade from Date 2.6 to v2.7, and am also getting the same problem when I select the date_browser:
user warning: Unknown column 'node_data_field_date.field_date_value' in 'where clause' query: SELECT node.nid AS nid, node.type AS node_type FROM node node WHERE (DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), '%Y') <= '2011' AND DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), '%Y') >= '2011') in /mydomain/public_html/sites/all/modules/views/includes/view.inc on line 775.I've tried using alternative arguments, without success.
#65
I am also getting the same "Unknown column..." error in date_browser upon updating.
#66
Subscribe. Same problem here...
#67
My temporary workaround, since the page still displays, is to just hide the Warning message using the CSS Injector module to set:
body.page-date-browser div.error {display:none}#68
Same problem here, though in my case it's an argument rather than a filter -- worked fine with 6.2.6, upgraded to 6.2.7 and I get the "Unknown column" errors and no dates showing up. I've tried a bunch of patches that I found here and in other issue threads, but nothing fixes the issue.
UPDATE: I isolated the issue by reverting date_api_argument_handler.inc to the 6.2.6 version, then gradually changing it to match 6.2.7. The issue is at line 279:
if (!empty($this->relationship)) {if (!empty($this->query_fields)) {
foreach ($this->query_fields as $query_field) {
$field = $query_field['field'];
$this->related_table_alias = $this->query->queue_table($field['table_name'], $this->relationship);
}
}
}
The problem goes away when the outermost condition here is removed (i.e., if (!empty($this->relationship))) -- this condition is new to 6.2.7. I don't understand what's going on here enough to implement a real fix, but for now just commenting out this condition has made my view work again.
#69
I have the same problem with Durpal 7.
Make an content type with an date field (field_date)
In views under date argument i change the "Node: Update date" to "Fields: field_date value"
got this error
'Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column \'field_data_field_date.field_date_value\' in \'where clause\''
in views_plugin_query_default->execute() (line 1314 of /home/bfsk/public_html/devel/bfsk/sites/all/modules/views/plugins/views_plugin_query_default.inc).
#70
Noah's fix worked for me as well.
#71
Run into this problem by updating to 6.x-2.7 and the fix from #68 by noah worked.
#72
will this fiks work for the 7.x version`?
#73
same issue. updated to 2.7 to address a patch on relative dates and introduced this same problem. will try the fix #68.
#74
same error on 2.7 so using noah fix.
#75
Same issue,
I switch the file date_api_argument_handler.inc version 6.2.7. with the 6.2.6 version as indicated in #68.
Know it workrs
Ben
#76
Yes, reverting back
modules/date/includes/date_api_argument_handler.incfrom Date 6.x-2.7 to that from the previous version (6.x-2.6) also fixed the problem for me. Issue #1016396: Unknown column ... in ... modules/views/includes/view.inc on line 775 is a duplicate.#77
Similar issue here, but #68 did'nt work for me.
I've updated to Views 6.x-2.12, Date 6.x-2.7 and Calendar 6.x-2.4
but on the page /date-browser I'm getting the following error:
Unknown column 'node_data_field_datum.field_datum_value' in 'where clause' query: SELECT DISTINCT node.nid AS nid, node.type AS node_type FROM node node INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'content_access_author') OR (na.gid = 1 AND na.realm = 'content_access_rid') OR (na.gid = 0 AND na.realm = 'resume_owner'))) AND ( (DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value, '%Y-%m-%dT%T'), '%Y-%m') <= '2011-05' AND DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value2, '%Y-%m-%dT%T'), '%Y-%m') >= '2011-05') ) in modules/views/includes/view.inc in Zeile 775.
#78
quixxel, it looks like you might be using the date field in a filter rather than an argument, is that right? If that's the case try looking around line 415 of date_api_filter_handler.inc -- making the same adjustment to that chunk of code might work for a filter (i.e., commenting out the "if (!empty($this->relationship))" condition).
#79
Gah, not sure why the version number changed above. Issue is definitely with 6.x-2.7. Just setting it back.
#80
noah,
your assumption was correct, but if I comment out the "if (!empty($this->relationship))" condition) in line 415 of date_api_filter_handler.inc, I got a blank screen on the page /date-browser and the error changed to:
Unknown column 'node_data_field_datum.field_datum_value' in 'where clause' query: SELECT DISTINCT node.nid AS nid, node.type AS node_type FROM node node WHERE (DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value, '%Y-%m-%dT%T'), '%Y-%m') <= '2011-01' AND DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value2, '%Y-%m-%dT%T'), '%Y-%m') >= '2011-01') in modules/views/includes/view.inc in Zeile 775
#81
noah,
my patch in #25 addresses the problem of multiple date fields in arguments, looking at
date_api_filter_handler.incit looks like exactly the same problem with filters as well. The same fix should work for both arguments and filters with multiple fields.There is a lot I don't understand about the inner workings of relationships, aliases etc but from what i gather the bug comes down to this:
The code that was written to handle table aliases was not written to handle aliases for multiple date fields.
set_relationship()it loops over each field and sets a single variable$this->related_table_alias.query(),$this->related_table_aliasis used to reference the date fields but of course it will fail because it is using the same alias for different fields.My patch (submitted again) is to turn
$this->related_table_aliasinto an array with$field['table_name']as the key, allowing it to handle multiple fields.This is patched against the latest dev version because looks like issue still exists but should work on 2.7 too. Filter patch included but not tested. Please test and let everyone know your results.
#82
i applied your patch Liam which didn' t resolve the problem, after i removed the outer condition like suggested in 68# the view works again. my view does not have a relation so
that (!empty($this->relationship)) results to false allways in my case.
But the code capsuled by the condition needs to run, because when i deactivate the outer condition to let the inner code set $this->related_table_alias to
$this->query->queue_table($field['table_name'], $this->relationship) everything looks good.
i don´t know enough to explain this behavior...
#83
Subscribe
#84
Has someone already created a patch for this critical issue?
#85
I used these two patches on 6.x 2.7 version and it fixed my problems.
Everything seems to be working fine now.
Thanks
#86
I think I found the best way to fix this, different than the patch. Just committed it to cvs. The tarball won't be updated until tonight.
#87
I did some module updates this morning and of course one of the updates was this Date module. Do we have a new version that will work? I haven't played with patches before so I don't know how to make these patches work.
#88
With the last dev, the problem dissapears :-)
#89
Just upgraded to latest .dev.
Changing the option action to take if argument is not present to display all values worked for me.
#90
I also have 2.7 and as per message #82, commenting out the lines as per #68 did the trick for me whereas the patches wouldn't apply (note, I tried to apply via patch manager, so that may have been something to do with it)
#91
I just tried the latest dev version after noticing the problem with 2.7 and I still run into the "Unknown column" issue when trying to use a date argument. Reverting back to 2.7 and commenting out the code as per #82 did work for me though.
#92
Confirmed #68 works around the issue.
#93
Subscribing
#94
#68 works for me too. Had an issue where i had a CCK image field and date field that I wanted to use in a Calendar view, and was getting the "unknown column" error.
Shawn
#95
#68 fixed me right up too :) thanks Noah.
Franco
#96
That fixed my problem.
Thanks a lot Noah.
Ahmed.
#97
Please Note: according to KarenS in #86, she applied a fix for this on January 17, 2011.
The Date 6x-2.7 release was created on January 3, 2011.
So, until 6.x-2.8 is released, this issue should be fixed in -dev and you should try that to verify.
#88 & #89 have verified that the -dev has fixed this.
But, #90 indicates this is not fixed in -dev.
#98
I had the same error and tried using the patch, but it didn't work for me. It turns out the issue was resolved by modifying permissions for affected users in the content_permissions module. I knew it was a permissions issue because it didn't affect all users but I'm new to Drupal so it took me a while to figure out where to look.
#99
Updating to date-6.x-2.x-dev worked for me. (6.2.8 dev)
#100
Automatically closed -- issue fixed for 2 weeks with no activity.
#101
Ok had the same error and #68 fixed it but now my Events are not showing up on the public side, only the admin side, I checked all the permissions and the Anonymous should be able to see the Date and Events but it does not show up, how do I fix this?
Nevermind, I found it, oversight on the permissions.
#102
I am having the same issue, except it's an argument with a default summary action and a relationship; remove the relationship and it works fine. Does this sound related to this issue or should I post a new issue? Thanks.
#103
#68 workaround works for me too
#104
Tons of people seem to be confirming that #68 works. Can we please get a release for this soon? :)
#105
#68 did not fix my issue, and neither did the changes made to -dev (see #86). I was able to duplicate the issue on a fresh install using -dev but will wait until a new release before I submit an issue, as I'm not entirely sure it's related to this issue.
#106
newest dev version fixed it for me
#107
#68 fixed it for me using 2.7 on a view using a single date field as an argument.
Also can confirm that "upgrading" to the dev version fixed the "Unknown column" error.
Now I'm torn, what to do in production? Run with a hacked 2.7 module or run a dev module release on our live site (which I'd prefer not to do).
#108
IMO, 2.8 should be released asap to get rid of this critical bug in the current official "stable" version.
#109
We need a like button so we can approve without posting again. :P Wait. Did I *really* recommend Facebook? ::face palm::
#110
subscribe
#111
My very similar issue:
(user warning: Unknown column 'node_data_field_event_date_and_time.delta' in 'field list' query: SELECT node.nid AS nid, node.title AS node_title, node_data_field_event_date_and_time.field_event_date_and_time_value AS node_data_field_event_date_and_time_field_event_date_and_time_value, bla bla bla...)
Was resolved by disabling the calendar module which I actually don't need because I just present list of events with views.
But is it the same issue? I just searched with "user warning: Unknown column AND views" and finally ended up here.
EDIT: never mind - back to square one. Stupid me.
#112
I can confirm this in current stable and dev releases.
I have a view with an argument set as the "Content: Year"
a:4:{s:6:"%error";s:12:"user warning";s:8:"%message";s:724:"Unknown column 'node_data_field_year.field_year_value' in 'where clause'query: SELECT node.nid AS nid,
node.type AS node_type,
node.vid AS node_vid,
node.title AS node_title,
DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL 36000 SECOND), '%Y%m%d') AS node_created_day
FROM node node
WHERE ((node.status <> 0) AND (node.type in ('tshirt')))
AND ((DATE_FORMAT(STR_TO_DATE(node_data_field_year.field_year_value, '%Y-%m-%dT%T'), '%Y') <= '1991' AND DATE_FORMAT(STR_TO_DATE(node_data_field_year.field_year_value, '%Y-%m-%dT%T'), '%Y') >= '1991'))
ORDER BY node_created_day DESC
LIMIT 0, 32";s:5:"%file";s:63:"/var/www/tshirtslayer/sites/all/modules/views/includes/view.inc";s:5:"%line";i:771;}
#113
[deleted]
#114
Patch at comment #68 failed to resolve this issue for me (latest stable release, wont work on latest dev release)
So kind of frustrating, I can't do a site that has an argument for a content-type as a date.
#115
Fortunately for me I'm just using the date field for year selection (so far)
Here's a snippet for your own update hook how i migrated away from date module and to a plain integer field.
$ret[] = update_sql("alter table content_type_tshirt add column year int(5);");$ret[] = update_sql("update content_type_tshirt set year = substr(field_year_value, 1,4)");
$ret[] = update_sql("alter table content_type_tshirt drop column field_year_value");
$ret[] = update_sql("ALTER TABLE content_type_tshirt CHANGE year field_year_value INT(5) NOT NULL;");
#116
user warning: Unknown column 'node_data_field_date.field_date_value' in 'where clause' query: SELECT node.nid AS nid, node.type AS node_type FROM node node WHERE (DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), '%Y-%m') <= '2011-06' AND DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), '%Y-%m') >= '2011-06') in C:\wamp\www\sites\all\modules\views\includes\view.inc on line 775.
What is the solution to this my problem ?
#117
I am having this same issue in the latest stable 6.x version. I am using a date field as an argument on my view.
WORKAROUND:
I was finally able to find that if I added the field being used as an argument (or presumably filter) to the fields being loaded for display, my error went away and everything worked as expected. It should work without that, but in the meantime this workaround fixed the problem for me. I set that field to be excluded from display so it is loaded but now shown.
#118
That'd be a workaround, given that using the field explicitly adds the table to the query.
Afaik the filters also add the required tables to the query in that same way.
But i'm not sure about the arguments or what causes the table to be missing.
#119
Issue still exists under current dev version (6.x-2.x-dev 2011-May-27) for me. Applying the fix supplied in #68 to version 6.x-2.7 resolves the error but I can't confirm that doing so doesn't raise some other as-of-yet undetected problem.
#120
We also used the fixed listed in post #68 and it worked for us.
#121
This might be related: http://drewish.com/content/2010/06/correctly_accessing_cck_fields_in_sql...
The table name changes when the field is used on multiple content types. I'm not sure if date corrects for this but it's something to look into.
#122
Yeah, I'm also getting this in 2.7. Fix in #68 works.
#123
From comment #117:
WORKAROUND:
I was finally able to find that if I added the field being used as an argument (or presumably filter) to the fields being loaded for display, my error went away and everything worked as expected. It should work without that, but in the meantime this workaround fixed the problem for me. I set that field to be excluded from display so it is loaded but now shown.
I was getting this error even though I had that date field in the view as a field, argument (summary listing) and sort.
Upgrading to the dev version resolved the error for me.
#124
Thank you Stella.
Your workaround works for me in OA 1. I used date 2.7 with patch #68 and copying this date_api_argument_handler.inc into date 2.8 also works. I prefer your solution.
#125
Subscribing
#126
My current situation... and my two cents:
modules: Date 2.7, Views 2.12, Calendar 2.4
patches: applied manually both from #68 and Liam's from #25
No improvement. I have the date field in my fields list, argument set up normally, filtering only on node type and a "status" cck field value.
Observation: When I created a new date format to eliminate the date, and only went with time (g:i a) showing as 4:45 pm, then assigned that to the "Medium" date format, then assigned "Medium" to the field output format in the view, the problem went away.
This created a new problem in on my site, in that I was using the normal "medium" format elsewhere in my site, and suddenly it showed only the time (not captured in the other locations due to setting date field granularity to day, rather than minute or second). So, I set it back and suddenly the problem returned. What I can't seem to do is get views fields to accept my custom date format of "time", and both Short and Medium formats are used elsewhere, so I can't use those, and it seems silly to make my "long" date format show time. That may be exactly what I have to do, as a work-around, but here's my question:
Is there currently a way (I'm not aware of) to show my custom (Time) date format in the field display drop-down in Views? This will solve my problem, though clearly not everyone else's.
I hope this helps illustrate a possible fix that is real and works for everyone, though I'm not technical enough to know what that might be... Good Luck, all!
********
UPDATE:
I upgraded to the .dev version of Date (2.8) and that shows the custom time format Time {YAY!!!} (which I changed to "h:i A" to match with the changes I note below) and I added the mysql changes manually to the Views/includes/handlers.inc file as recommended in the patch on an issue I can't find now... here is what I changed the file to (starting on line 931 in function views_date_sql_format for Views 2.12):
case 'mysqli':$replace = array(
'Y' => '%Y',
'y' => '%y',
'M' => '%%b',
'm' => '%m',
'n' => '%c',
'F' => '%M',
'D' => '%a',
'd' => '%%d',
'l' => '%W',
'j' => '%e',
'W' => '%v',
'H' => '%H',
'h' => '%h',
'i' => '%i',
's' => '%s',
'A' => '%p',
);
$format = strtr($format, $replace);
return "DATE_FORMAT($field, '$format')";
case 'pgsql':
$replace = array(
'Y' => 'YYYY',
'y' => 'YY',
'M' => 'Mon',
'm' => 'MM',
'n' => 'MM', // no format for Numeric representation of a month, without leading zeros
'F' => 'Month',
'D' => 'Dy',
'd' => 'DD',
'l' => 'Day',
'j' => 'DD', // no format for Day of the month without leading zeros
'W' => 'WW',
'H' => 'HH24',
'h' => 'HH12',
'i' => 'MI',
's' => 'SS',
'A' => 'AM',
);
I hope this helps somebody! It fixed my problem...
~Cheers!
Bootstrap Johnny
#127
Comment #68 fixed the issue for me! Thanks!
My problem was I got the same SQL error (not from a different content type) when using an argument for getting content of a particular date from the a CCK date field.
#128
Comment #68 fixed the issue for me as well. Thanks, Noah.
#129
I just ran into this during a Date module upgrade from 2.6 to 2.7. The method in #117 of adding the list of fields to display (but excluding from display) worked for me. In my opinion, much better than #68 - which also worked for me - but required hacking a contrib module.
Regardless, this still isn't fixed in the latest stable release.
#130
I saw that the Views query is not including the join to the table with the date content, so I resolved this by adding the argument field to the display field, display excluded. Apologies if one of the 100+ comments above already says this since I don't have time to read them all.
#131
None of the above solutions appear to be working for me. I tried the solutions in comment #25, and #68 applying the patch, but to no avail. The argument field is set as Date: Date (node) and is set to filter by the current date, I don't see a way to add this and 'exclude it from display' as mentioned in #117.
I am using date-6.x-2.7 and views-6.x-2.16, this is the error I receive on my calendar page:
user warning: Unknown column 'node_data_field_date.field_date_value' in 'where clause' query: SELECT node.nid AS nid, node.type AS node_type FROM node node WHERE ((node.type in ('event')) AND (node.status = 1)) AND ((DATE_FORMAT(ADDTIME(node_data_field_date.field_date_value, SEC_TO_TIME(-21600)), '%Y-%m') <= '2011-11' AND DATE_FORMAT(ADDTIME(node_data_field_date.field_date_value2, SEC_TO_TIME(-21600)), '%Y-%m') >= '2011-11')) in //sites/all/modules/views/includes/view.inc on line 814.
Any help is greatly appreciated!
#132
I got the same error on an imported view. I enabled the default "calendar" view and had no problems with that, so I just used that instead.
#133
This should no longer be a problem with Date 6.x-2.8.
#134
@roball Why should it no longer be a problem? what changed? what was comitted? so more information would be great!
#135
The commit mentioned in #86 is now in a release (2.8) and solved the problem for some users, including myself. If others still have problems I would find it better to open a new issue for that since it does not seem to be directly related to this already so long issue. Thanks.
#136
ok great :)
#137
Automatically closed -- issue fixed for 2 weeks with no activity.