Views filters & arguments not working when using a date relationship.

I think this is a duplicate issue that has been fixed in the latest dev:
#386406: After module upgrade, Views filter value "now" incorrectly outputs 2009-01-01

Reopening. This issue does not appear to be a duplicate of #386406.

As sirpy noted in the description, there is an error in the SQL. Essentially, the Date module ignores the Relationship setting in the Views Filter. I have not tried the fix in #2.

This is a legitimate issue, the problem is that the date filter and argument are quite complicated so the right tables don't get joined in automatically. I've been playing with various options and was going down a path similar to what is suggested in #2 to reset the values to force the right tables to get joined in.

This is on my radar, but I don't yet have a working fix.

@sirpy, it would be easier to review this if you made it into an actual patch.

Subscribing.

sirpy - thanks for the fix, this is a lifesaver! I've rolled it into a patch to help move things along.

Well, the hack does work. (Even with the missing quotes :) ) However it feels very dirty. After talking w/ merlin on IRC it seems the real solution should come well before the query() call here, as $this->table shouldn't be "node" at that point. His initial thought was that "date should be resetting that to the right value in its init hook or something."

subscribing! been looking for this page for a while. many thanks advance to the real fix.

Marked #445216: Problem with Arguments and relationships and #365756: Filter doesn't work on fields in relationships as duplicates.

so this problem affects both arguments and filters with relationships. the patch in #9 fixes the problem for filters but does nothing for arguments. i posted #445216: Problem with Arguments and relationships and it got marked as a duplicate for this post. So can someone look into getting arguments working with relationships. thanks.

I've tested the patch from comment #9 with Date 6.x-2.x-dev (2009-Apr-01) and it seems to be working well for me. I hope something like this can be included in the next official release.

Semi copied from the thread http://drupal.org/node/432368.

I think perhaps this no longer works with the latest dev release. Or rather, it conflicts with the bug fix in http://drupal.org/node/432368. I've got a date filter on a related node, and when I load the view it throws a big ugly sql error and returns nothing:

Unknown column 'node_data_field_graduation.field_graduation_value' in 'where clause' query: SELECT COUNT(*) FROM (SELECT users.uid AS uid FROM users users INNER JOIN node node_users ON users.uid = node_users.uid AND node_users.type = 'userprofile' LEFT JOIN users_roles users_roles_value_0 ON users.uid = users_roles_value_0.uid AND users_roles_value_0.rid = 7 LEFT JOIN content_type_userprofile node_data_field_committed ON node_users.vid = node_data_field_committed.vid LEFT JOIN content_field_firstname node_data_field_firstname ON node_users.vid = node_data_field_firstname.vid LEFT JOIN content_field_lastname node_data_field_lastname ON node_users.vid = node_data_field_lastname.vid WHERE ((users_roles_value_0.rid = 7) AND (node_data_field_committed.field_committed_value IS NULL)) AND (DATE_FORMAT(STR_TO_DATE(node_data_field_graduation.field_graduation_value, '%Y-%m-%dT%T'), '%Y') = '2009') ) count_alias

That's with the patch not installed. If the patch is installed, the sql error does not occur, but the filter winds up with a hidden default value -- that is to say, the date select does not have a value selected, but the records are being filtered by the current year.

Here's the view (field_graduation_value is the field in question):

$view = new view;
$view->name = 'player_search';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'users';
$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', 'Player Search', 'default');
$handler->override_option('relationships', array(
'content_profile_rel' => array(
'label' => 'Content Profile',
'required' => 1,
'type' => 'userprofile',
'id' => 'content_profile_rel',
'table' => 'users',
'field' => 'content_profile_rel',
'relationship' => 'none',
),
));
$handler->override_option('fields', array(
'uid' => array(
'label' => 'Uid',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'link_to_user' => 0,
'exclude' => 0,
'id' => 'uid',
'table' => 'users',
'field' => 'uid',
'relationship' => 'none',
),
'picture' => array(
'label' => '',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'exclude' => 0,
'id' => 'picture',
'table' => 'users',
'field' => 'picture',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
),
'field_firstname_value' => array(
'label' => '',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 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_firstname_value',
'table' => 'node_data_field_firstname',
'field' => 'field_firstname_value',
'relationship' => 'content_profile_rel',
'override' => array(
'button' => 'Override',
),
),
'field_lastname_value' => array(
'label' => '',
'alter' => array(
'alter_text' => 1,
'text' => '[field_firstname_value] [field_lastname_value]',
'make_link' => 1,
'path' => 'user/[uid]',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'link_to_node' => 0,
'label_type' => 'none',
'format' => 'default',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_lastname_value',
'table' => 'node_data_field_lastname',
'field' => 'field_lastname_value',
'relationship' => 'content_profile_rel',
'override' => array(
'button' => 'Override',
),
),
'field_graduation_value' => array(
'label' => 'High School Graduation Year',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'multiple_number' => '',
'multiple_from' => '',
'multiple_to' => '',
'group' => TRUE,
),
'repeat' => array(
'show_repeat_rule' => '',
),
'fromto' => array(
'fromto' => 'both',
),
'exclude' => 0,
'id' => 'field_graduation_value',
'table' => 'node_data_field_graduation',
'field' => 'field_graduation_value',
'relationship' => 'content_profile_rel',
),
'field_primaryposition_value' => array(
'label' => 'Primary Position',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_primaryposition_value',
'table' => 'node_data_field_primaryposition',
'field' => 'field_primaryposition_value',
'relationship' => 'content_profile_rel',
),
'field_secondaryposition_value' => array(
'label' => 'Secondary Position',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_secondaryposition_value',
'table' => 'node_data_field_secondaryposition',
'field' => 'field_secondaryposition_value',
'relationship' => 'content_profile_rel',
'override' => array(
'button' => 'Override',
),
),
'field_highschool_value' => array(
'label' => 'High School',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'link_to_node' => 0,
'label_type' => 'custom',
'format' => 'default',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_highschool_value',
'table' => 'node_data_field_highschool',
'field' => 'field_highschool_value',
'relationship' => 'content_profile_rel',
'override' => array(
'button' => 'Override',
),
),
'field_gpa_value' => array(
'label' => 'GPA',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'us_1',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_gpa_value',
'table' => 'node_data_field_gpa',
'field' => 'field_gpa_value',
'relationship' => 'content_profile_rel',
'override' => array(
'button' => 'Override',
),
),
'field_satscore_value' => array(
'label' => 'SAT Score',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_satscore_value',
'table' => 'node_data_field_satscore',
'field' => 'field_satscore_value',
'relationship' => 'content_profile_rel',
),
'field_actscore_value' => array(
'label' => 'ACT',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'link_to_node' => 0,
'label_type' => 'custom',
'format' => 'default',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_actscore_value',
'table' => 'node_data_field_actscore',
'field' => 'field_actscore_value',
'relationship' => 'content_profile_rel',
'override' => array(
'button' => 'Override',
),
),
'field_travelteam_value' => array(
'label' => 'Travel Team',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'link_to_node' => 0,
'label_type' => 'custom',
'format' => 'default',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_travelteam_value',
'table' => 'node_data_field_travelteam',
'field' => 'field_travelteam_value',
'relationship' => 'content_profile_rel',
'override' => array(
'button' => 'Override',
),
),
'field_bat_hand_value' => array(
'label' => 'Bats',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_bat_hand_value',
'table' => 'node_data_field_bat_hand',
'field' => 'field_bat_hand_value',
'relationship' => 'content_profile_rel',
'override' => array(
'button' => 'Override',
),
),
'field_throwing_hand_value' => array(
'label' => 'Throws',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_throwing_hand_value',
'table' => 'node_data_field_throwing_hand',
'field' => 'field_throwing_hand_value',
'relationship' => 'content_profile_rel',
'override' => array(
'button' => 'Override',
),
),
'field_slapper_value' => array(
'label' => 'Slap Hitter',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_slapper_value',
'table' => 'node_data_field_slapper',
'field' => 'field_slapper_value',
'relationship' => 'content_profile_rel',
),
'field_badges_value' => array(
'label' => '',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'link_to_node' => 0,
'label_type' => 'none',
'format' => 'default',
'multiple' => array(
'group' => 1,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => 0,
),
'exclude' => 0,
'id' => 'field_badges_value',
'table' => 'node_data_field_badges',
'field' => 'field_badges_value',
'relationship' => 'content_profile_rel',
'override' => array(
'button' => 'Override',
),
),
));
$handler->override_option('sorts', array(
'field_lastname_value' => array(
'order' => 'ASC',
'delta' => -1,
'id' => 'field_lastname_value',
'table' => 'node_data_field_lastname',
'field' => 'field_lastname_value',
'relationship' => 'content_profile_rel',
),
'field_firstname_value' => array(
'order' => 'ASC',
'delta' => -1,
'id' => 'field_firstname_value',
'table' => 'node_data_field_firstname',
'field' => 'field_firstname_value',
'relationship' => 'content_profile_rel',
),
));
$handler->override_option('arguments', array(
'null' => array(
'default_action' => 'ignore',
'style_plugin' => 'default_summary',
'style_options' => array(),
'wildcard' => 'all',
'wildcard_substitution' => 'All',
'title' => '',
'default_argument_type' => 'fixed',
'default_argument' => '',
'validate_type' => 'node',
'validate_fail' => 'not found',
'must_not_be' => 0,
'id' => 'null',
'table' => 'views',
'field' => 'null',
'validate_user_argument_type' => 'uid',
'validate_user_roles' => array(
'2' => 0,
'4' => 0,
'6' => 0,
'5' => 0,
'8' => 0,
'7' => 0,
'3' => 0,
'9' => 0,
),
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
'default_options_div_prefix' => '',
'default_argument_user' => 0,
'default_argument_fixed' => '',
'default_argument_php' => '',
'validate_argument_node_type' => array(
'university' => 'university',
'webform' => 0,
'product' => 0,
'ua_combine' => 0,
'showcase' => 0,
'masterlesson' => 0,
'bio' => 0,
'camp' => 0,
'coach_profile' => 0,
'event' => 0,
'flv' => 0,
'group_lesson' => 0,
'half_hour_lesson' => 0,
'home' => 0,
'hour_lesson' => 0,
'instructor_application' => 0,
'lesson' => 0,
'lesson_old' => 0,
'location' => 0,
'notification' => 0,
'page' => 0,
'pressrelease' => 0,
'showcase_participant' => 0,
'story' => 0,
'ua_combine_participant' => 0,
'university_application' => 0,
'userphoto' => 0,
'userprofile' => 0,
'uservideo' => 0,
),
'validate_argument_node_access' => 0,
'validate_argument_nid_type' => 'nid',
'validate_argument_vocabulary' => array(),
'validate_argument_type' => 'tid',
'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',
'validate_argument_is_member' => 0,
'validate_argument_signup_status' => 'any',
'validate_argument_signup_node_access' => 0,
'validate_argument_php' => '',
),
));
$handler->override_option('filters', array(
'rid' => array(
'operator' => 'or',
'value' => array(
'7' => '7',
),
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'rid',
'table' => 'users_roles',
'field' => 'rid',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
'reduce_duplicates' => 1,
),
'field_committed_value' => array(
'operator' => 'empty',
'value' => '',
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'case' => 1,
'id' => 'field_committed_value',
'table' => 'node_data_field_committed',
'field' => 'field_committed_value',
'relationship' => 'content_profile_rel',
'override' => array(
'button' => 'Override',
),
),
'field_firstname_value' => array(
'operator' => 'contains',
'value' => '',
'group' => '0',
'exposed' => TRUE,
'expose' => array(
'use_operator' => 0,
'operator' => 'field_firstname_value_op',
'identifier' => 'field_firstname_value',
'label' => 'First Name contains',
'optional' => 1,
'remember' => 0,
),
'case' => 1,
'id' => 'field_firstname_value',
'table' => 'node_data_field_firstname',
'field' => 'field_firstname_value',
'relationship' => 'content_profile_rel',
),
'field_lastname_value' => array(
'operator' => 'contains',
'value' => '',
'group' => '0',
'exposed' => TRUE,
'expose' => array(
'use_operator' => 0,
'operator' => 'field_lastname_value_op',
'identifier' => 'field_lastname_value',
'label' => 'Last Name contains',
'optional' => 1,
'remember' => 0,
),
'case' => 1,
'id' => 'field_lastname_value',
'table' => 'node_data_field_lastname',
'field' => 'field_lastname_value',
'relationship' => 'content_profile_rel',
),
'field_badges_value_many_to_one' => array(
'operator' => 'or',
'value' => array(),
'group' => '0',
'exposed' => TRUE,
'expose' => array(
'use_operator' => 0,
'operator' => 'field_badges_value_many_to_one_op',
'identifier' => 'field_badges_value_many_to_one',
'label' => 'Badges',
'optional' => 1,
'single' => 1,
'remember' => 0,
'reduce' => 0,
),
'id' => 'field_badges_value_many_to_one',
'table' => 'node_data_field_badges',
'field' => 'field_badges_value_many_to_one',
'relationship' => 'content_profile_rel',
'override' => array(
'button' => 'Override',
),
'reduce_duplicates' => 0,
),
'field_primaryposition_value_many_to_one' => array(
'operator' => 'or',
'value' => array(),
'group' => '0',
'exposed' => TRUE,
'expose' => array(
'use_operator' => 0,
'operator' => 'field_primaryposition_value_many_to_one_op',
'identifier' => 'field_primaryposition_value_many_to_one',
'label' => 'Primary Position',
'optional' => 1,
'single' => 1,
'remember' => 0,
'reduce' => 0,
),
'id' => 'field_primaryposition_value_many_to_one',
'table' => 'node_data_field_primaryposition',
'field' => 'field_primaryposition_value_many_to_one',
'relationship' => 'content_profile_rel',
'reduce_duplicates' => 0,
),
'field_throwing_hand_value_many_to_one' => array(
'operator' => 'or',
'value' => array(),
'group' => '0',
'exposed' => TRUE,
'expose' => array(
'use_operator' => 0,
'operator' => 'field_throwing_hand_value_many_to_one_op',
'identifier' => 'field_throwing_hand_value_many_to_one',
'label' => 'Throws',
'optional' => 1,
'single' => 1,
'remember' => 0,
'reduce' => 0,
),
'id' => 'field_throwing_hand_value_many_to_one',
'table' => 'node_data_field_throwing_hand',
'field' => 'field_throwing_hand_value_many_to_one',
'relationship' => 'content_profile_rel',
'override' => array(
'button' => 'Override',
),
'reduce_duplicates' => 0,
),
'field_bat_hand_value_many_to_one' => array(
'operator' => 'or',
'value' => array(),
'group' => '0',
'exposed' => TRUE,
'expose' => array(
'use_operator' => 0,
'operator' => 'field_bat_hand_value_many_to_one_op',
'identifier' => 'field_bat_hand_value_many_to_one',
'label' => 'Bats',
'optional' => 1,
'single' => 1,
'remember' => 0,
'reduce' => 0,
),
'id' => 'field_bat_hand_value_many_to_one',
'table' => 'node_data_field_bat_hand',
'field' => 'field_bat_hand_value_many_to_one',
'relationship' => 'content_profile_rel',
'override' => array(
'button' => 'Override',
),
'reduce_duplicates' => 0,
),
'field_slapper_value_many_to_one' => array(
'operator' => 'or',
'value' => array(),
'group' => '0',
'exposed' => TRUE,
'expose' => array(
'use_operator' => 0,
'operator' => 'field_slapper_value_many_to_one_op',
'identifier' => 'field_slapper_value_many_to_one',
'label' => 'Slap Hitter',
'optional' => 1,
'single' => 1,
'remember' => 0,
'reduce' => 0,
),
'id' => 'field_slapper_value_many_to_one',
'table' => 'node_data_field_slapper',
'field' => 'field_slapper_value_many_to_one',
'relationship' => 'content_profile_rel',
'reduce_duplicates' => 0,
),
'field_state_value_many_to_one' => array(
'operator' => 'or',
'value' => array(),
'group' => '0',
'exposed' => TRUE,
'expose' => array(
'use_operator' => 0,
'operator' => 'field_state_value_many_to_one_op',
'identifier' => 'field_state_value_many_to_one',
'label' => 'State',
'optional' => 1,
'single' => 1,
'remember' => 0,
'reduce' => 0,
),
'id' => 'field_state_value_many_to_one',
'table' => 'node_data_field_state',
'field' => 'field_state_value_many_to_one',
'relationship' => 'content_profile_rel',
'reduce_duplicates' => 0,
),
'date_filter' => array(
'operator' => '=',
'value' => array(
'value' => NULL,
'min' => NULL,
'max' => NULL,
'default_date' => '',
'default_to_date' => '',
),
'group' => '0',
'exposed' => TRUE,
'expose' => array(
'use_operator' => 0,
'operator' => 'date_filter_op',
'identifier' => 'date_filter',
'label' => 'Graduation',
'optional' => 1,
'remember' => 0,
),
'date_fields' => array(
'node_data_field_graduation.field_graduation_value' => 'node_data_field_graduation.field_graduation_value',
),
'date_method' => 'OR',
'granularity' => 'year',
'form_type' => 'date_select',
'default_date' => '',
'default_to_date' => '',
'year_range' => '-0:+8',
'id' => 'date_filter',
'table' => 'node',
'field' => 'date_filter',
'relationship' => 'content_profile_rel',
'override' => array(
'button' => 'Override',
),
),
));
$handler->override_option('access', array(
'type' => 'none',
));
$handler->override_option('title', 'Player Search');
$handler->override_option('empty', 'Sorry, there are no players that fit your search options.');
$handler->override_option('empty_format', '1');
$handler->override_option('use_pager', '1');
$handler->override_option('row_options', array(
'inline' => array(
'field_firstname_value' => 'field_firstname_value',
'field_lastname_value' => 'field_lastname_value',
),
'separator' => ' ',
));
$handler = $view->new_display('page', 'Page (tab)', 'page_1');
$handler->override_option('access', array(
'type' => 'role',
'role' => array(
'5' => 5,
),
));
$handler->override_option('items_per_page', 20);
$handler->override_option('path', 'node/%/player-search');
$handler->override_option('menu', array(
'type' => 'tab',
'title' => 'Player Search',
'description' => '',
'weight' => '1',
'name' => 'navigation',
));
$handler->override_option('tab_options', array(
'type' => 'none',
'title' => '',
'description' => '',
'weight' => 0,
));
$handler = $view->new_display('page', 'Page', 'page_2');
$handler->override_option('path', 'player-search');
$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,
));

Arguments still don't seem to be working with relationships. Should I create a new post? or move this post back to active?

I second that question. I've got a client bugging the daylights out of me regarding something this affects. Here's hoping someone has a clever solution soon soon soon.

anything new? is it worth testing patch @ #9 now?

subscribing

I'd like to track the outcome of this issue, as it relates to #361459: Date argument in a view with a relationship .

More descriptive title.

Something new about this? I can not use a date in a referenced node as an argument to the view.

A workaround or solution?

thanks in advance

Nothing new about this? I think it is important. I cannot believe the intended behaviour is not to be able to use a date from a referenced node as a view parameter...

Is anybody reading?

Thanks. Regards

yes, sure we are waiting and following, but since we are not coders there is not much we can do :) there are 3 things we can do: 1) wait, 2) get dirty with code or 3) hire php/sql someone to find bug for you and rest of us

A patch? A workaround?

I cannot believe nobody uses a view with a date argument that is a CCK date field in a referenced node...

How?

Thanks in advance

Please, can anybody help?

I think it is the biggest (and most blocking) bug ever discovered in date/views.

Queries are malformed when including a date field from referenced nodes!

What do date/views developers think about it? Is it expected somebody doing something?

It's completely frustrating

i have some free time now on my hands. can i be helpful in fixing this issue? since im not coder i will need someone to direct me to root of problem and just maybe i can try to fix it with trial and error method.

Had applied the patch and seems to be working for my filters... at least for now I am safe. Thank you!

yes, patch #9 works, didnt have time to test it properly before, but now that i applied it things turns out all right. What is needed? some more testing?

Subscribing ... patch #9 worked for me. Thank You very much :)

And for arguments?

Any news, not only for filters but also for arguments?

Is everybody blind except #13?

The (so dirty) patch only "solves" the issue for filters, but not for arguments

Any help?

The question is quite clear: how to use a date argument in a view when the date is a CCK date field in a referenced node

Views fails. A solution? A workaround?

Cannot believe nobody has done it...

just calm down

I am calmed, but I think it is critical for module functioning.

Relationships are core to Views, date arguments also. And query construction is clearly erroneus when both are present.

It is not a minor bug, it is a critical misfunctioning.

Subscribing

The attached patch attempts to use the same hackish solution (as previously outlined) to get both filters and arguments working with relationships... For sure it's not the best way to to go about fixing this but thought at least it would help people who needed (related) arguments to work.

@KarenS
I tried endlessly to figure out how to get this done in pre_query(), but adding the table there resulted in the joins ending up in the wrong order... I've spent a lot of time with this now (and investigated a number of potentially related issues from the views issue queue), and if you have some suggestions as to how to better solve this, I'm happy to help further.

Potentially connected?
#317271: ensure_my_table() Limited to One Relationship Per Table
#371348: Incorrect logic in query::ensure_table

thank you mate! lets test this.

Thanks a lot #40!

The patch works!

I'm sure this patch needs work, but setting to needs review in order to get more people to test.

ags_filters_relationship_hack.patch doesn't seem to work for me.

noahb's ags_filter_relationship_hack.patch does work. My problem has to do with how the Calendar view plugin retrieves date field data from date_api_fields() and doesn't taking into consideration how the use of relationships changes field names. I've moved my discoveries and solution over to the Calendar queue.

The new post can be found here - http://drupal.org/node/544706

(post moved to http://drupal.org/node/544706 - No content when using date argument data from relationship)

This patch does not belong here. If I could delete it I would. :S It's been copied over to the Calendar queue, where it should be.

Is this fixed in the latest dev? I am using Drupal 6.13 with Date 2.3 and I am having what I think is the same problem.

I am doing a student directory. One of the fields is a date node (graduating class year) in advanced profile kit. I need to be able to filter using that in a user type view (ie. using a Content Profile relationship). If I set up a user view with such a relationship, everything works perfectly except when I then try to filter by that Date node.

Such filtering gives:

user warning: Unknown column 'node_data_field_class.field_class_value' in 'where clause' query: SELECT users.uid AS uid, node_users__users.picture AS node_users__users_picture, node_users__users.uid AS node_users__users_uid, node_users__users.name AS node_users__users_name, node_users_node_data_field_class.field_class_value AS node_users_node_data_field_class_field_class_value, node_users.nid AS node_users_nid, node_users.type AS node_users_type, node_users.vid AS node_users_vid, node_users_node_data_field_class.field_prog_language_value AS node_users_node_data_field_class_field_prog_language_value, node_users_node_data_field_class.field_gender_value AS node_users_node_data_field_class_field_gender_value FROM users users INNER JOIN node node_users ON users.uid = node_users.uid AND node_users.type = 'uprofile' INNER JOIN content_type_uprofile node_users_node_data_field_prog_language ON node_users.vid = node_users_node_data_field_prog_language.vid INNER JOIN users node_users__users ON node_users.uid = node_users__users.uid LEFT JOIN content_type_uprofile node_users_node_data_field_class ON node_users.vid = node_users_node_data_field_class.vid WHERE ((users.uid not in ('0')) AND (node_users_node_data_field_prog_language.field_prog_language_value = 'French')) AND (DATE_FORMAT(STR_TO_DATE(node_data_field_class.field_class_value, '%Y-%m-%dT%T'), '%Y') = '2011') ORDER BY node_users__users_name ASC in /home/uomeds/public_html/sites/all/modules/views/includes/view.inc on line 759.

which looks a fair bit like #15 above who seems to be doing the same kind of site too. http://drupal.org/node/385688#comment-1585014

Should I try the patch from noahb in #40 or the most recent dev? Or does my issue sound unrelated?

Thanks.

Well Noah, you can add me to the list of grateful users because your patch here:
http://drupal.org/node/385688#comment-1839132

seems to have worked and I can now filter by date.

Thanks.

Patch in #40 works great

Still using patch #9 against 6.x-2.3.

Just wanted to re-iterate everyone elses sentiments...

#40's patch works perfectly! Thank you!!

Well hacky or not it works, and I don't have a better solution, so this is committed. Thanks!

Sorry, to dissapoint but it rewrites the field['fullname'] with only table_alias of the first field. This may help when referencing, but is a disaster when table names should be kept.

In my case I was filtering on 'last update (node.changed)' or 'last comment (node_comment_statistics.last_comment_timestamp)', which resulted in an unknown column in the where-clause, stating 'node_comment_statistics.changed' instead of 'node.changed'.

I don't think this patch ought to have been committed.

Koen

KoCo, if you're going to report that something doesn't work it would be more useful to provide the specific steps to reproduce your problem. I have spent an hour now trying various combinations of views using those fields trying to find some way to create a view that has an error and have not yet found one.

After an hour of playing around with this report I am unable to produce an error, and the Acquia team has also been trying to produce a view with an error for me to test and they couldn't find anything that generated an error either, so without more information on exactly how to reproduce your critical error there is nothing I can do.

Sorry, If I was a bit too cryptic.
When using the updated code (line 407 in date_api_filter_handler.inc):

$this->get_query_fields();
$this->table = $this->query_fields[0]['field']['table_name'];
$this->ensure_my_table();
  if (!empty($this->query_fields)) {
   foreach ((array) $this->query_fields as $query_field) {
    $field = $query_field['field'];
    $query_field['field']['fullname'] = $this->table_alias. '.' .$field['field_name'];

Whenever I want to filter between two dates, using more than one date field from two different tables (e.g. last updated/commented date OR last update & a cck date field).
The table name gets overwritten, thus giving a sql warning:

user warning: Unknown column 'node_comment_statistics.changed' in 'where clause' query: SELECT node.nid AS nid, GREATEST(node.changed, node_comment_statistics.last_comment_timestamp) AS node_comment_statistics_last_updated, node.title AS node_title, node_data_field_onderwerp.field_onderwerp_value AS node_data_field_onderwerp_field_onderwerp_value, node.type AS node_type, node.vid AS node_vid FROM node node INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT JOIN content_field_onderwerp node_data_field_onderwerp ON node.vid = node_data_field_onderwerp.vid 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 = 2 AND na.realm = 'content_access_author') OR (na.gid = 2 AND na.realm = 'content_access_rid') OR (na.gid = 3 AND na.realm = 'content_access_rid') OR (na.gid = 8 AND na.realm = 'content_access_rid'))) AND ( ((node.type in ('page')) AND (node.status <> 0)) AND ((DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_comment_statistics.last_comment_timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d') >= '2009-06-17' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_comment_statistics.last_comment_timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d') <= '2009-09-17') OR (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_comment_statistics.changed), SEC_TO_TIME(7200)), '%Y-%m-%d') >= '2009-06-17' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_comment_statistics.changed), SEC_TO_TIME(7200)), '%Y-%m-%d') <= '2009-09-17')) )ORDER BY node_comment_statistics_last_updated DESC LIMIT 0, 2 in X:\...\public_html\Drupal6\sites\all\modules\views\includes\view.inc on line 755.

I included a view with this problem, please look at the Titlebar block for an example. There the last update or comment date is taken between an interval of two months.
Hopefully this is a bit helpfull. I too keep on looking, but must admit having a hard time to debug a code this complex.

Koen

Note: Marked #580178: Views: SQL Error "Unknown column" when using fields from different content-types in a filter as a duplicate of this issue (#52/55 at least).

I'm also getting this SQL-error in the latest version of the date module. I opened a new issue #580178: Views: SQL Error "Unknown column" when using fields from different content-types in a filter for this bug (there is also described how to reproduce it).

Nevermind, I'm an idiot. The date field has to be a part of the node as all the rest of the fields that I'm using to filter.

I'd really like to be able to reference a different node's date field, so I'll search and see if I can find if that can work.

any progress on this issue?

I've set it to "needs work" because in #580178: Views: SQL Error "Unknown column" when using fields from different content-types in a filter there seems to be enough information (at least to reproduce the bug).

Subscribing!

I am receiving this error but only when I am filtering by two date fields in the same filter.

I wasn't experiencing this problem until an update or two ago I can't recall the exact point.

Is anyone else experiencing "Unknown Column" when they have two date fields selected under the filter?

I agree with @tyr that the solution committed in commit 263582 makes the problem even worse. His/her #580178: Views: SQL Error "Unknown column" when using fields from different content-types in a filter sums it up.

Here's my version of a fix, which seems to correct the problem in a more elegant way that does not lead to incorrect SQL or the JOINs occurring in the wrong order.

I have not tested it beyond my own use case, so your mileage may vary. At the very least it may serve as a basis for someone to produce a more universally acceptable patch.

I just came upon this bug. Essentually, I am using a nodereferrer relationship to access nodes that refer to my node filter. Since I have many nodes referring to the main node filtered, I am trying to limit the result to only nodes created today. Thus, I am using a date argument with relationship to nodereferrer.

The error..

user warning: Unknown column 'node_node_node.created' in 'where clause' query: SELECT DISTINCT(node.nid) AS nid, node.title AS node_title, node_node_node_data_field_collab_note_time.field_collab_note_time_nid AS node_node_node_data_field_collab_note_time_field_collab_note_time_nid, node_node.nid AS node_node_nid, node_node.type AS node_node_type, node_node.vid AS node_node_vid, node_node.title AS node_node_title FROM node node LEFT JOIN content_type_collaboration_time node2 ON node.nid = node2.field_collab_time_patient_nid LEFT JOIN node node_node ON node2.nid = node_node.nid LEFT JOIN workflow_node workflow_node ON node.nid = workflow_node.nid LEFT JOIN content_type_collaboration_time node_node_node_data_field_collab_note_time ON node_node.vid = node_node_node_data_field_collab_note_time.vid WHERE ((node.type in ('patient_profile')) AND (workflow_node.sid = 12)) AND ((DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_node_node.created), SEC_TO_TIME(-21600)), '%Y-%m-%d') <= '2009-12-09' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_node_node.created), SEC_TO_TIME(-21600)), '%Y-%m-%d') >= '2009-12-09')) LIMIT 0, 10 in /mnt/stor2-wc1-dfw1/409706/www.mytestsystem.com/web/content/sites/all/modules/views/includes/view.inc on line 755.

It appears to me that the date is trying to filter on patient_profile which is my main node filter NOT the nodereferrer relationship. This is otherwise way beyond my skill level to debug but it does seem to be consistent with the original date argument not working on relationships issue that has appeared to fizzled with the exception of patch #14 which now seems to also have problems with AND/OR multiple date fields.

I have much appreciation for those trying to figure this out and hoping we can get it into a stable release soon? In the meantime, I guess I will try out the dev and see if it fixes my problems.

I updated to dev . Still same problem.

user warning: Unknown column 'node_node_node.created' in 'where clause' query: SELECT node.nid AS nid, node_node_1_node_data_field_hospitalization_location.field_hospitalization_location_nid AS node_node_1_node_data_field_hospitalization_location_field_hospitalization_location_nid, node_node_1.nid AS node_node_1_nid, node_node_1.type AS node_node_1_type, node_node_1.vid AS node_node_1_vid, node.title AS node_title, node_node.title AS node_node_title, node_node.nid AS node_node_nid, node_node_1__workflow_states.state AS node_node_1__workflow_states_state, node_node_node_data_field_collab_note_time.field_collab_note_time_nid AS node_node_node_data_field_collab_note_time_field_collab_note_time_nid, node_node.type AS node_node_type, node_node.vid AS node_node_vid FROM node node LEFT JOIN content_type_collaboration_time node2 ON node.nid = node2.field_collab_time_patient_nid LEFT JOIN node node_node ON node2.nid = node_node.nid LEFT JOIN content_type_encounter node3 ON node.nid = node3.field_hospitalized_patient_nid LEFT JOIN node node_node_1 ON node3.nid = node_node_1.nid LEFT JOIN workflow_node workflow_node ON node.nid = workflow_node.nid LEFT JOIN content_type_encounter node_node_1_node_data_field_hospitalization_location ON node_node_1.vid = node_node_1_node_data_field_hospitalization_location.vid LEFT JOIN workflow_node node_node_1__workflow_node ON node_node_1.nid = node_node_1__workflow_node.nid LEFT JOIN workflow_node node_node_1_node_node_1__workflow_node ON node_node_1.nid = node_node_1_node_node_1__workflow_node.nid LEFT JOIN workflow_states node_node_1__workflow_states ON node_node_1__workflow_node.sid = node_node_1__workflow_states.sid LEFT JOIN content_type_collaboration_time node_node_node_data_field_collab_note_time ON node_node.vid = node_node_node_data_field_collab_note_time.vid WHERE ((node.type in ('patient_profile')) AND (workflow_node.sid = 12) AND (node_node_1_node_data_field_hospitalization_location.field_hospitalization_location_nid IS NOT NULL)) AND ((DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_node_node.created), SEC_TO_TIME(-21600)), '%Y-%m-%d') <= '2009-12-10' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_node_node.created), SEC_TO_TIME(-21600)), '%Y-%m-%d') >= '2009-12-10')) LIMIT 0, 10 in /mnt/stor2-wc1-dfw1/409706/www.mysite.com/web/content/sites/all/modules/views/includes/view.inc on line 755.

Again, it appears the date check is against the wrong node.. The filtered and not the relationship.

@tpainton: Please try my patch in #62. It will probably work for your case.

@gribnif: I will give it a try. I am going to apply it to latest dev. I hope that is okay?

I did a little more testing without your patch.

Node A
Node B references Node A

Node B has field Date, granularity Day

Construct view of Node Type A.
Relationship Node referrer using the reference field on Node B.

Fields Node:Title, Content:date using relationship Node referrer.

Argument: Date, default today, using field content:date, relationship node referrer.

Result works listing Node A, with dates found on Node B, matching today.

If you change the argument to Date, default today, using Node Post Date, you get missing column error everyone seems to be getting.

Patch #62, tested and working. Thanks!

Is this the same problem?

user warning: Unknown column 'node_data_field_game_date.field_practice_date_value' in 'where clause' query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS nid FROM node node INNER JOIN og_ancestry og_ancestry ON node.nid = og_ancestry.nid LEFT JOIN content_type_game node_data_field_game_date ON node.vid = node_data_field_game_date.vid LEFT JOIN content_type_practice node_data_field_practice_date ON node.vid = node_data_field_practice_date.vid WHERE ((node.status <> 0) AND (node.type in ('game', 'practice')) AND (og_ancestry.group_nid = 176)) AND ((DATE_FORMAT(STR_TO_DATE(node_data_field_game_date.field_game_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2009-12-13') OR (DATE_FORMAT(STR_TO_DATE(node_data_field_game_date.field_practice_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2009-12-13')) ) count_alias in /home/.jojo/[username]/[sitename]/sites/all/modules/views/includes/view.inc on line 739.

I believe I am having the reported problem with node.created after upgrading to 2.4. It worked fine in 2.3.

I am filtering based on two dates, the node created date and a cck date field. In 2.4 the where clause refers to node_data_field_date.created, while in 2.3 it referred to node.created. Note that the select clause is fine. The relevant line is the last of each of the following sql statements.

******2.4 SQL******

SQL
SELECT node.nid AS nid,
node.uid AS node_uid,
node.type AS node_type,
node_revisions.format AS node_revisions_format,
node_data_field_attachment.field_attachment_fid AS node_data_field_attachment_field_attachment_fid,
node_data_field_attachment.field_attachment_list AS node_data_field_attachment_field_attachment_list,
node_data_field_attachment.field_attachment_data AS node_data_field_attachment_field_attachment_data,
node.vid AS node_vid,
node_data_field_link.field_link_url AS node_data_field_link_field_link_url,
node_data_field_link.field_link_title AS node_data_field_link_field_link_title,
node_data_field_link.field_link_attributes AS node_data_field_link_field_link_attributes,
history_user.timestamp AS history_user_timestamp,
history_user.nid AS history_user_nid,
node.created AS node_created,
node.changed AS node_changed,
term_image.tid AS term_image_tid,
node.title AS node_title,
node_data_field_date.field_date_value AS node_data_field_date_field_date_value,
node_revisions.body AS node_revisions_body,
term_data.weight AS term_data_weight,
DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL -18000 SECOND), '%Y%m%d') AS node_created_day
FROM drup_node node
LEFT JOIN drup_content_field_date node_data_field_date ON node.vid = node_data_field_date.vid
LEFT JOIN drup_node_revisions node_revisions ON node.vid = node_revisions.vid
LEFT JOIN drup_content_type_news node_data_field_attachment ON node.vid = node_data_field_attachment.vid
LEFT JOIN drup_content_field_link node_data_field_link ON node.vid = node_data_field_link.vid
LEFT JOIN drup_history history_user ON node.nid = history_user.nid AND history_user.uid = 1
LEFT JOIN drup_term_node term_node ON node.vid = term_node.vid
LEFT JOIN drup_term_image term_image ON term_node.tid = term_image.tid
LEFT JOIN drup_term_data term_data ON term_node.tid = term_data.tid
WHERE ((node.type in ('news')) AND (node.status <> 0))
AND ((DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2009-10-04') AND (DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(node_data_field_date.created), 'UTC', 'America/New_York'), '%Y-%m-%d') >= '2009-10-04'))
ORDER BY term_data_weight ASC, node_data_field_date_field_date_value DESC, node_created_day ASC

*****2.3 SQL****
SELECT node.nid AS nid,
node.uid AS node_uid,
node.type AS node_type,
node_revisions.format AS node_revisions_format,
node_data_field_attachment.field_attachment_fid AS node_data_field_attachment_field_attachment_fid,
node_data_field_attachment.field_attachment_list AS node_data_field_attachment_field_attachment_list,
node_data_field_attachment.field_attachment_data AS node_data_field_attachment_field_attachment_data,
node.vid AS node_vid,
node_data_field_link.field_link_url AS node_data_field_link_field_link_url,
node_data_field_link.field_link_title AS node_data_field_link_field_link_title,
node_data_field_link.field_link_attributes AS node_data_field_link_field_link_attributes,
history_user.timestamp AS history_user_timestamp,
history_user.nid AS history_user_nid,
node.created AS node_created,
node.changed AS node_changed,
term_image.tid AS term_image_tid,
node.title AS node_title,
node_data_field_date.field_date_value AS node_data_field_date_field_date_value,
node_revisions.body AS node_revisions_body,
term_data.weight AS term_data_weight,
DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL -18000 SECOND), '%Y%m%d') AS node_created_day
FROM drup_node node
LEFT JOIN drup_content_field_date node_data_field_date ON node.vid = node_data_field_date.vid
LEFT JOIN drup_node_revisions node_revisions ON node.vid = node_revisions.vid
LEFT JOIN drup_content_type_news node_data_field_attachment ON node.vid = node_data_field_attachment.vid
LEFT JOIN drup_content_field_link node_data_field_link ON node.vid = node_data_field_link.vid
LEFT JOIN drup_history history_user ON node.nid = history_user.nid AND history_user.uid = ***CURRENT_USER***
LEFT JOIN drup_term_node term_node ON node.vid = term_node.vid
LEFT JOIN drup_term_image term_image ON term_node.tid = term_image.tid
LEFT JOIN drup_term_data term_data ON term_node.tid = term_data.tid
WHERE ((node.type in ('news')) AND (node.status <> 0))
AND ((DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2009-10-04') AND (DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(node.created), 'UTC', 'America/New_York'), '%Y-%m-%d') >= '2009-10-04'))
ORDER BY term_data_weight ASC, node_data_field_date_field_date_value DESC, node_created_day ASC

I got what I believe is a related error with date 2.4 when trying to use a cck date field as an argument. I'm using a relationship to reference another node type, but the date field is not brought in via a relationship.

Error message:

user warning: Unknown column '_node_data_field_show_dates.field_show_dates_value' in 'where clause' query: SELECT node.nid AS nid, node.title AS node_title, node.language AS node_language, node_data_field_show_dates.field_show_dates_value AS node_data_field_show_dates_field_show_dates_value, node.type AS node_type, node.vid AS node_vid, node_data_field_show_dates.field_show_external_boxoffice_url AS node_data_field_show_dates_field_show_external_boxoffice_url, node_data_field_show_dates.field_show_external_boxoffice_title AS node_data_field_show_dates_field_show_external_boxoffice_title, node_data_field_show_dates.field_show_external_boxoffice_attributes AS node_data_field_show_dates_field_show_external_boxoffice_attributes, node_data_field_show_dates.field_show_jpr_boxoffice_url AS node_data_field_show_dates_field_show_jpr_boxoffice_url, node_data_field_show_dates.field_show_jpr_boxoffice_title AS node_data_field_show_dates_field_show_jpr_boxoffice_title, node_data_field_show_dates.field_show_jpr_boxoffice_attributes AS node_data_field_show_dates_field_show_jpr_boxoffice_attributes, node_data_field_show_venues.field_show_venues_nid AS node_data_field_show_venues_field_show_venues_nid, node_data_field_venue_name.field_venue_name_value AS node_data_field_venue_name_field_venue_name_value, node_data_field_venue_name.field_venue_name_format AS node_data_field_venue_name_field_venue_name_format, node_node_data_field_show_venues__location.city AS node_node_data_field_show_venues__location_city, node_data_field_show_dates.field_cancelled_value AS node_data_field_show_dates_field_cancelled_value FROM node node LEFT JOIN content_type_representation node_data_field_show_dates ON node.vid = node_data_field_show_dates.vid LEFT JOIN content_type_representation node_data_field_show_venues ON node.vid = node_data_field_show_venues.vid LEFT JOIN node node_node_data_field_show_venues ON node_data_field_show_venues.field_show_venues_nid = node_node_data_field_show_venues.nid LEFT JOIN content_type_venue node_data_field_venue_name ON node.vid = node_data_field_venue_name.vid LEFT JOIN location_instance node_node_data_field_show_venues__location_instance ON node_node_data_field_show_venues.vid = node_node_data_field_show_venues__location_instance.vid LEFT JOIN location_instance node_node_data_field_show_venues_node_node_data_field_show_venues__location_instance ON node_node_data_field_show_venues.vid = node_node_data_field_show_venues_node_node_data_field_show_venues__location_instance.vid LEFT JOIN location node_node_data_field_show_venues__location ON node_node_data_field_show_venues__location_instance.lid = node_node_data_field_show_venues__location.lid WHERE ((node.status <> 0) AND (node.type in ('representation'))) AND ((DATE_FORMAT(ADDTIME(_node_data_field_show_dates.field_show_dates_value, SEC_TO_TIME(-18000)), '%Y-%m') <= '2010-02' AND DATE_FORMAT(ADDTIME(_node_data_field_show_dates.field_show_dates_value, SEC_TO_TIME(-18000)), '%Y-%m') >= '2010-02')) in /var/aegir/drupal-6.14/sites/all/modules/views/includes/view.inc on line 769.

@drupalsav and @mvc: Please try my patch in #62. I have only tried it against the "dev" version, but it may work with 2.4.

@Gribnif: sorry, forgot to comment here again. i tried your patch with 2.4 and it worked when using a cck date field of the primary node as an argument, thanks! i still wasn't able to use a date field from a relationship as an argument, but i was able to produce the output i needed via views_customfield (a little ugly, but it works).

may i ask what did you do with views_customfield to make it work? Ive been tinkering with this damn problem all day and still cant get it to work with arg' and relationships

Subscribe.

Possibly related:

• #580546: Date 6.x-2.4 maybe broke CCK date Views2 filter
• #543424: Date: Date (node) filter not applied in Views

@tommeir: views_customfield didn't make the filters or arguments work. it's a module which allows you to write arbitrary PHP and display the output as a views field. so, i used this to call node_load() directly instead of bringing in the data i wanted via a relationship and displaying it via views in that way. with views_customfield, you have access to the entire $view object, so the possibilities are endless. but as i said, it's more of a hack workaround than an elegant solution.

subscribe

I fixed this on my site: it turns out that, since we were using the built-in 'Calendar' view supplied by the Calendar module, the Date argument was being applied to our modified 'Upcoming Events' block (seen on the home page of archstl.org).

We removed the Date: Date (node) argument from the Upcoming Block display (only), and set the Date: Date (node) filter to show event nodes greater than or equal to today. Now the view's working great!

(cross posted from http://archstldev.com/node/469#comment-557)

OK, I'm reverting the fix that seemed to break things and trying the patch in #62 (and applying it to date filters as well as date arguments.) This looks promising. It definitely fixes the errors, not sure yet if it handles all the relationship stuff correctly but I'm working through that too. Thanks gribnif!

It seems date 6.x-2.6 isn't working for my view (with patch #62, it looks like) with a relationship, as you said. Basically I have a view for displaying upcoming events that users have been invited too. Its base table is provided by the rsvp module. I have the node set up as a relationship and I'm showing all invitations with an end time greater than or equal to "now" in the date field of that node using a filter. Here's the SQL error:

user warning: Unknown column 'node_data_field_date.field_date_value2' in 'where clause' query: SELECT rsvp.rid AS rid, rsvp_invite.hash AS rsvp_invite_hash, node_rsvp.title AS node_rsvp_title, node_rsvp.nid AS node_rsvp_nid, node_rsvp__node_data_field_date.field_location_value AS node_rsvp__node_data_field_date_field_location_value, node_rsvp.type AS node_rsvp_type, node_rsvp.vid AS node_rsvp_vid, node_rsvp__node_data_field_date.field_date_value AS node_rsvp__node_data_field_date_field_date_value, node_rsvp__node_data_field_date.field_date_value2 AS node_rsvp__node_data_field_date_field_date_value2, rsvp_invite.response AS rsvp_invite_response, node_rsvp_node_data_field_date.field_date_value AS node_rsvp_node_data_field_date_field_date_value FROM rsvp rsvp LEFT JOIN node node_rsvp ON rsvp.nid = node_rsvp.nid LEFT JOIN rsvp_invite rsvp_invite ON rsvp.rid = rsvp_invite.rid LEFT JOIN content_type_event node_rsvp__node_data_field_date ON node_rsvp.vid = node_rsvp__node_data_field_date.vid LEFT JOIN content_type_event node_rsvp_node_data_field_date ON node_rsvp.vid = node_rsvp_node_data_field_date.vid WHERE (rsvp_invite.uid = 1) AND (DATE_FORMAT(CONVERT_TZ(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), 'UTC', 'America/New_York'), '%Y-%m-%d\T%H:%i:%s') >= '2010-08-19T23:10:48') ORDER BY node_rsvp_node_data_field_date_field_date_value ASC LIMIT 0, 6 in C:\Users\David\Projects\caldwellconnect2\sites\default\modules\views\includes\view.inc on line 771.

Attached is the exported view.

Hello,
Since I updated the module date version 2.6, the calendar view that it had amended returns me an error on a field in another table. In this update already applied the patch # 62 but my error persists.

user warning: Unknown column 'node2.field_fechaevento_value' in 'where clause' query: SELECT node.nid AS nid, node.title AS node_title, node.language AS node_language, node_data_field_fechaevento.field_fechaevento_value AS node_data_field_fechaevento_field_fechaevento_value, node_data_field_fechaevento.field_fechaevento_value2 AS node_data_field_fechaevento_field_fechaevento_value2, node.type AS node_type, node.vid AS node_vid, node.created AS node_created, node.changed AS node_changed FROM node node LEFT JOIN content_type_event node_data_field_fechaevento ON node.vid = node_data_field_fechaevento.vid LEFT JOIN content_type_event node_data_field_fechaevento2 ON node.vid = node_data_field_fechaevento2.vid LEFT JOIN node node2 ON node.tnid = node2.tnid WHERE ((node.status <> 0) AND (node.type in ('event', 'page', 'story'))) AND (((DATE_FORMAT(ADDTIME(STR_TO_DATE(node2.field_fechaevento_value, '%Y-%m-%dT%T'), SEC_TO_TIME(7200)), '%Y-%m') <= '2010-08' AND DATE_FORMAT(ADDTIME(STR_TO_DATE(node2.field_fechaevento_value2, '%Y-%m-%dT%T'), SEC_TO_TIME(7200)), '%Y-%m') >= '2010-08')) OR ((DATE_FORMAT(ADDTIME(STR_TO_DATE(node2.field_fechaevento_value, '%Y-%m-%dT%T'), SEC_TO_TIME(7200)), '%Y-%m') <= '2010-08' AND DATE_FORMAT(ADDTIME(STR_TO_DATE(node2.field_fechaevento_value2, '%Y-%m-%dT%T'), SEC_TO_TIME(7200)), '%Y-%m') >= '2010-08')) OR ((DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node2.created), SEC_TO_TIME(7200)), '%Y-%m') <= '2010-08' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node2.created), SEC_TO_TIME(7200)), '%Y-%m') >= '2010-08'))) ORDER BY node_changed ASC in /srv/data/var/www/covam/sites/all/modules/views/includes/view.inc on line 775.

Any ideas? Thanks.

subscribing

subscribing too, as this is really crucial for some sections of our website.

subscribing

subscribing

Subscribed.

Subscribing.

Note: Whatever caused this is solved by a reversion from 2.6 to 2.4, but not 2.5. In case that helps any!

I managed to fix the problem for my use case with this patch. Basically it just replaces the table names in the relevant places in each field and date_handler with the alias defined in the handler (filter handler only). (Date 2.6 defines the related_table_alias property but doesn't seem to do anything with it in the filter handler.)

I'm not sure if this is a clean and acceptable solution to the problem that fits all use cases. I can try to move this logic into the query() method if that's better (this seems to be what the argument handler is doing, are arguments working?).

After updating to 2.x-dev, applying davemicc's #87 patch seems to have resolved my failing filters since upgrading from 2.4.

From the related commit history, it seems only the first half of the argument handler patch from Gribnif in #62 was ported to the filter handler.

Hi, I came across this (or similar) bug a week ago and think I fixed it. Only just found this thread.

See patch here: http://drupal.org/node/580178#comment-3408740

The patch modifies Gribnif's patch (http://drupal.org/node/385688#comment-2218938). It makes date table aliases work with more than one date field at a time. Not sure if this is what everyone else is having problems with.

This problem seems the *most* related as the problem I have is where the related table that is used in the join is appended with extra information (using a user views query on (node) content profile).

...
LEFT JOIN content_field_birth_date node_users__node_data_field_birth_date ON node_users.vid = node_users__node_data_field_birth_date.vid
...
AND (DATE_FORMAT(STR_TO_DATE(node_data_field_birth_date.field_birth_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') < '1776')
...

It seems that the $query_field from the views handler has the wrong value for "fullname" as it is using its internal "table_name"...

Please see attached patch for a proposed solution.

oops - there were some other patches in that file...

Please see just the proposed fix.

subscribing

#91 seems to work.

Thx

#91 Works for me too.

The wrong alias was used in my where clause when filtering events on a cck date field, when date was part of table joined by a relationship, #91 fixed that.

EDIT : this is actually a very annoying bug : it breaks a lot of our views...

#91 Doesn't work for me... :(

@CalvinMcGee
can you copy paste here the sql query of your views (AFTER the patch have been applying ) ?

With the 6.x-2.x-dev version from 2010-Oct-12 and applied the patch given in #91, this is my output:

    * user warning: Unknown column 'node_data_field_datetime.field_datetime_offset' in 'where clause' query: SELECT COUNT(*) FROM (SELECT signup_log.sid AS sid FROM signup_log signup_log LEFT JOIN users users_signup_log ON signup_log.uid = users_signup_log.uid LEFT JOIN node node_signup_log ON signup_log.nid = node_signup_log.nid LEFT JOIN content_field_datetime node_signup_log__node_data_field_datetime ON node_signup_log.vid = node_signup_log__node_data_field_datetime.vid LEFT JOIN content_field_datetime node_signup_log__node_data_field_datetime2 ON node_signup_log.vid = node_signup_log__node_data_field_datetime2.vid INNER JOIN term_node node_signup_log__term_node ON node_signup_log.vid = node_signup_log__term_node.vid LEFT JOIN profile_values users_signup_log_profile_values_profile_first_name ON users_signup_log.uid = users_signup_log_profile_values_profile_first_name.uid AND users_signup_log_profile_values_profile_first_name.fid = '2' LEFT JOIN profile_values users_signup_log_profile_values_profile_last_name ON users_signup_log.uid = users_signup_log_profile_values_profile_last_name.uid AND users_signup_log_profile_values_profile_last_name.fid = '3' LEFT JOIN content_field_datetime node_signup_log_node_data_field_datetime ON node_signup_log.vid = node_signup_log_node_data_field_datetime.vid WHERE ((DATE_FORMAT(ADDTIME(node_signup_log__node_data_field_datetime.field_datetime_value, SEC_TO_TIME(node_data_field_datetime.field_datetime_offset)), '%Y-%m-%d') >= '2010-10-06') AND (DATE_FORMAT(ADDTIME(node_signup_log__node_data_field_datetime2.field_datetime_value, SEC_TO_TIME(node_data_field_datetime.field_datetime_offset)), '%Y-%m-%d') <= '2010-10-14')) AND ((node_signup_log.status <> 0) AND (node_signup_log.type in ('event')) AND (node_signup_log__term_node.tid IN (29, 30, 28))) ) count_alias in /var/www/choir_web/sites/all/modules/views/includes/view.inc on line 745.
    * user warning: Unknown column 'node_data_field_datetime.field_datetime_offset' in 'where clause' query: SELECT signup_log.sid AS sid, signup_log.attended AS signup_log_attended, users_signup_log.uid AS users_signup_log_uid, users_signup_log_profile_values_profile_first_name.value AS users_signup_log_profile_values_profile_first_name_value, users_signup_log_profile_values_profile_first_name.uid AS users_signup_log_profile_values_profile_first_name_uid, users_signup_log_profile_values_profile_last_name.value AS users_signup_log_profile_values_profile_last_name_value, users_signup_log_profile_values_profile_last_name.uid AS users_signup_log_profile_values_profile_last_name_uid, node_signup_log.title AS node_signup_log_title, node_signup_log.nid AS node_signup_log_nid, node_signup_log.language AS node_signup_log_language, node_signup_log.type AS node_signup_log_type, node_signup_log.vid AS node_signup_log_vid, node_signup_log_node_data_field_datetime.field_datetime_value AS node_signup_log_node_data_field_datetime_field_datetime_value FROM signup_log signup_log LEFT JOIN users users_signup_log ON signup_log.uid = users_signup_log.uid LEFT JOIN node node_signup_log ON signup_log.nid = node_signup_log.nid LEFT JOIN content_field_datetime node_signup_log__node_data_field_datetime ON node_signup_log.vid = node_signup_log__node_data_field_datetime.vid LEFT JOIN content_field_datetime node_signup_log__node_data_field_datetime2 ON node_signup_log.vid = node_signup_log__node_data_field_datetime2.vid INNER JOIN term_node node_signup_log__term_node ON node_signup_log.vid = node_signup_log__term_node.vid LEFT JOIN profile_values users_signup_log_profile_values_profile_first_name ON users_signup_log.uid = users_signup_log_profile_values_profile_first_name.uid AND users_signup_log_profile_values_profile_first_name.fid = '2' LEFT JOIN profile_values users_signup_log_profile_values_profile_last_name ON users_signup_log.uid = users_signup_log_profile_values_profile_last_name.uid AND users_signup_log_profile_values_profile_last_name.fid = '3' LEFT JOIN content_field_datetime node_signup_log_node_data_field_datetime ON node_signup_log.vid = node_signup_log_node_data_field_datetime.vid WHERE ((DATE_FORMAT(ADDTIME(node_signup_log__node_data_field_datetime.field_datetime_value, SEC_TO_TIME(node_data_field_datetime.field_datetime_offset)), '%Y-%m-%d') >= '2010-10-06') AND (DATE_FORMAT(ADDTIME(node_signup_log__node_data_field_datetime2.field_datetime_value, SEC_TO_TIME(node_data_field_datetime.field_datetime_offset)), '%Y-%m-%d') <= '2010-10-14')) AND ((node_signup_log.status <> 0) AND (node_signup_log.type in ('event')) AND (node_signup_log__term_node.tid IN (29, 30, 28))) ORDER BY node_signup_log_node_data_field_datetime_field_datetime_value ASC, users_signup_log_profile_values_profile_last_name_value ASC, users_signup_log_profile_values_profile_first_name_value ASC LIMIT 0, 42 in /var/www/choir_web/sites/all/modules/views/includes/view.inc on line 771.

And I'm starting to feel really uncomfortable, because switching to 6.x-2.4 doesn't seem to help me. I would REALLY appreciate some help with this.

#91 works for me.

Cross posting related issues: the patch here looks conceptually quite similar to the one in #580178-25: Views: SQL Error "Unknown column" when using fields from different content-types in a filter (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.

I think #91 is the right idea for a fix. Committed something similar. See if that takes care of the issues.

I have the same: user warning: Unknown column errors than #97.
I am using a view without relationships and a Date: Date (node) filter with two date fields and method OR.
The problem is in the new code from the last commit:

$table_alias = !empty($this->related_table_alias) ? $this->related_table_alias : $field['table_name'];
$query_field['field']['fullname'] = $table_alias .'.'. $query_field['field']['field_name'];

That is adding the $table_alias to the variable name with the $this->related_table_alias value even without relationships.
If you are filtering with Date: Date (node) with just a field then you get for the full name:
node_data_field_promotion_date.field_promotion_date_value (that's an example, in my case)
but filtering with two fields then you are using the same table alias for both fields:
node_data_field_promotion_date.field_event_date_value
node_data_field_promotion_date.field_promotion_date_value

This value come from

  function set_relationship() {
    parent::set_relationship();
    $this->get_query_fields();
    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);
      }
    }
  }

There are no restrictions when $this->related_table_alias is added, it is added not only with relationships, it is added always.
So adding a restriction for only get query fields and add the related_table_alias variable when there is any defined relationship it solves the problem and start using the $field['table_name'] for the fullname:
node_data_field_event_date.field_event_date_value
node_data_field_promotion_date.field_promotion_date_value

Whatever you did in dev version (I assume that commit) seems to have fixed the problem for me.

@jkristos
The question is that this patch for views/date relationships/date filters,
breaks the case: views/no date relationships/date filters with multiple fields.

Try to add a second field in a date filter without relationships and verify the bug, please.

@jcmaro

I came across this a while ago, see patch at: http://drupal.org/node/580178#comment-3408740
It fixed the two date problem for me. Haven't tested the latest version with the same problem so don't know if the problem is still there.

I don't think there is a problem with having table aliases when there is no relationships.

subscribing

subscribing

I just upgraded to the latest development versions of Calendar and Date, and the unknown column error is still present (making my calendar blank). Based on KarenS's comment in #100, I gathered this was fixed. I can confirm the problem still exists in my install.

Yep, that looks like the right patch. Committed. Thanks!

I can confirm the problem still exists in my install.

Perhaps, check this out

http://drupal.org/node/1027752#comment-3987994