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

after digging into the issue, i've found that because of the default value in $this->table of the handler, the views query->ensure_table returns at the wrong place. following is a fix for the date filter handler query() function, but i'm sure it is not the right way to do it, but it works.
this probably also needs to be fixed in the argument handler code.
function query() {
//first get our field data, so we can get our correct table name.
//the default $this->table=node and this causes the query->ensure_table to return in the wrong place when we have a relationship
//i guess it thinks this field is form the main node table.
//for other handlers te $this->table is not default to node when using relationships.
$this->get_query_fields();
//a hack to cause query->ensure_table to perform the correct join.
$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'];
//when multiple filters of date exist, again the ensure table fails,it stops when checking if we already used such a relationship
//and since we already did, we must correct the filter main table, to the first filter main table, which is set by the ensure_table
//in table alias
//this is the hack to ensure a correct full name when using relationships
$query_field['field']['fullname'] = $this->table_alias.".".$field['field_name'];
$sql = '';
switch ($this->operator) {
case 'between':
$min = $this->date_filter('min', $query_field, '>=');
$max = $this->date_filter('max', $query_field, '<=');
$sql = !empty($min) && !empty($max) ? "($min AND $max)" : '';
break;
case 'not between':
$min = $this->date_filter('min', $query_field, '<');
$max = $this->date_filter('max', $query_field, '>');
$sql = !empty($min) && !empty($max) ? "($min OR $max)" : '';
break;
default:
$sql = $this->date_filter('value', $query_field, $this->operator);
break;
}
if (!empty($sql)) {
// Use set_where_group() with the selected date_method
// of 'AND' or 'OR' to combine the field WHERE clauses.
$this->query->set_where_group($this->options['date_method'], 'date');
$this->query->add_where('date', $sql);
}
}
}
}

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

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

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.

Attachment	Size
date_api_filter_handler.inc_.patch	1.59 KB

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

Attachment	Size
ags_filters_relationship_hack.patch	2.4 KB

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.

Attachment	Size
calendar_relationship_date_hack.patch	644 bytes

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):

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:

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

Attachment	Size
views.txt	35.89 KB

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.

Attachment	Size
date_relationship_arg.patch.txt	3.62 KB

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.