Problems using 2 date filters in same view (when both filters use a CCK Date field).

The date filter has some problems, renaming this to reflect the issue.

I also saw the same error in the old http://drupal.org/node/260550 ("Cannot add date filter") issue, and still have it today after uninstalling, purging, and reinstalling the latest date-dev and views-dev.

I also have this problem with latest dev of views and beta2 of date. I have attached an xdebug call stack if this helps?

Thanks for the trace, but I actually needed to re-write the way it works, so it didn't apply.

I have the new filters marginally working again. More work is needed, but they are at least basically functional.

Thanks KarenS for your dedication to this critical module!

With new date, views, and cck devs installed, I can add a date filter now— but only one that doesn't actually filter, which I'd consider still "not working."

For example, if I try to select all nodes whose "eventdate" field is greater than "now," I get all nodes regardless of the value of eventdate. If I try to filter on a specific date (e.g. 2008-06-13), I get the error "[blank] is invalid." The generated query is

SELECT DISTINCT(node.nid) AS nid,
   node_data_field_eventdate.field_eventdate_value AS node_data_field_eventdate_field_eventdate_value,
   node.title AS node_title,
   node.type AS node_type,
   node_data_field_eventdate.field_eventdate_value2 AS node_data_field_eventdate_field_eventdate_value2,
   node_data_field_eventdate.nid AS node_data_field_eventdate_nid
 FROM node node 
 LEFT JOIN content_type_event node_data_field_eventdate ON node.vid = node_data_field_eventdate.vid
 WHERE (node.type in ('event')) AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_data_field_eventdate.field_eventdate_value), SEC_TO_TIME(-14400)), '%Y-%m-%d') > '2008-06-13')
   ORDER BY node_data_field_eventdate_field_eventdate_value ASC

The query looks fine and should work. Are you using MYSQL? What kind of field is field_eventdate?

I also have the same problem (using the views and date dev versions as suggested).
I have a field Publish Date (fieldtype: Date) where I am trying to display only if Publish Date <= Now... but all items are displayed.
When I try to enter a particular date to filter by, I get "is invalid" (so views doesn't accept the argument to see what result it produces)

This is the query generated:

SELECT node.nid AS nid,
   node_data_field_publish_date.field_publish_date_value AS node_data_field_publish_date_field_publish_date_value,
   node.type AS node_type,
   node_data_field_publish_date.nid AS node_data_field_publish_date_nid,
   node.title AS node_title,
   node_revisions.body AS node_revisions_body,
   node_revisions.format AS node_revisions_format
 FROM node node 
 LEFT JOIN content_type_news node_data_field_publish_date ON node.vid = node_data_field_publish_date.vid
 LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
 WHERE (node.type in ('news')) AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_data_field_publish_date.field_publish_date_value), SEC_TO_TIME(19800)), '%Y-%m-%d') <= '2008-06-14')
   ORDER BY node_data_field_publish_date_field_publish_date_value DESC 

I am using MYSQL.

I am very much new to Drupal, so I maybe totally off-track here... but the FROM_UNIXTIME bit looks suspicious given that I am not using the Datetime fieldtype?

@7, the field is a datetime field and we are using MySQL 4.1.22.

I also tried adding a date (as opposed to datetime) field. Here again, I can add a filter, but there is no apparent effect. The query itself is essentially identical (replacing the "eventdate" datetime field with the "eventstart" date field),

SELECT DISTINCT(node.nid) AS nid,
   node_data_field_eventstart.field_eventstart_value AS node_data_field_eventstart_field_eventstart_value,
   node.title AS node_title,
   node.type AS node_type,
   node_data_field_eventstart.field_eventstart_value2 AS node_data_field_eventstart_field_eventstart_value2,
   node_data_field_eventstart.nid AS node_data_field_eventstart_nid
 FROM node node 
 LEFT JOIN content_type_event node_data_field_eventstart ON node.vid = node_data_field_eventstart.vid
 WHERE (node.type in ('event')) AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_data_field_eventstart.field_eventstart_value), SEC_TO_TIME(-14400)), '%Y-%m-%d') >= '2008-06-14')
   ORDER BY node_data_field_eventstart_field_eventstart_value DESC

This may be the same thing as http://drupal.org/node/261818. Do you have a file in the date/date folder called date_views.inc? If so, that's the problem. That file is no longer needed and was removed, but your copy is still there creating havoc.

Delete any date filters you created (they have the wrong names), remove that file, empty your caches, and start over to create your filters.

There is now just one Date filter, called Date: Date, that will work for ANY date field, including system dates.

If that wasn't the problem, you can re-open this issue.

Hi!

I didn't have the file date_views.inc - a few days back there was instruction to delete the date folder before reinstalling, and I had done that... and was already using the Date:Date filter.

However, I did notice that I was using the 6.x-2.0-beta3, and now tried it with 6.x-2.x-dev, with exactly the same results (no filtering is happening with now, and entering a specific date results in is invalid... and the query looks identical (at least, the dodgy FROM_UNIXTIME is still there))

I am using Views 6.x-2.x-dev (2008-Jun-14), and notice there is 6.x-2.0-beta4 (2008-Jun-14) and 6.x-2.x-dev (2008-Jun-15) - would either of them help?

btw I have not activated caching in drupal either.

I also have the problems, and no "date_views.inc" file.
I think there are several errors in the date_api_filter_handler class, but I haven't managed to find out exactly what and how to fix them.

One small thing that I have fixed is to make the filter be able to match any node at all. The attached patch fixes so that STR_TO_DATE is used rather than FROM_UNIXTIME in the SQL query. On a View that I created to filter on a Date field (using Site-timezone) there was initially no nodes matched (before patch), but with patch the one that should be matched is found.

The bug about not being able to select which date to base the filtering on still remains. My filter is set up for "Date only" (no "adjustment" but trying them dit not help a few days ago). On the configuration page, I can only use 'now' as date for the filter. (Or the empty string to disable the filter). It is not possible to enter a date as it says in the help text "Date format YYYY-MM-DD. Leave date blank to do no filtering, use 'now' to filter for the current date."
Entering anything else than 'now' will give the validation failure "is invalid." (would be nice with a mesage there!) with red focus on the textfileld where a date was entered.
I tried to look into the validator date_api_filter_handler#options_validate() and it seems very weird, with unused variable $parts and a str_replace that seems to have no effect on the contents present in my case. But during the few minutes that I looked at it I did not understand what should be changed. EDIT: Possibly the str_replace should use $name as input (not $value), then the field name appears in the validation error message. See the next comment for info on what variables exist in my case.

If it would be of any help, then I suggest copying code from the filters that operate on Node dates, e.g. "Node: Post date". They have the same kind of configuration of "Operator" and "Value" being either a fixed date or an offset from the current date. I think their interface is simpler (no extra configuration between "Date only", "Date and adjustment" or "Adjustment only") and they work. As far I can tell they give equivalent filtering possibilities. The "Date and adjustment" seems superfluous, since instead of entering a fixed date + offset, you can enter the resulting date directly.

It seemed I could not attach a text file with my debugging attempt. So here it is as a comment instead. Comment #12 contains what I wanted to say, this is just some kind of appendix/attachment.

Debugging options_validate in the class date_api_filter_handler, on the beta3 release:
My date field filter has granularity month, so there is no day or hour expected, the help text says "Date format YYYY-MM. Leave date blank to do no filtering, use 'now' to filter for the current date."

 //To make the validator work a tad better, first change the line
$part = str_replace(array('min', 'max', 'value'), '', $value);
//into
$part = str_replace(array('min', 'max', 'value'), '', $name);

When I submit the string "2008-06" on ?q=admin/build/views/nojs/config-item/datetest/default/filter/date_filter
the options_validate function runs with these variables:

 $values = Array
(
    [valuedate] => 2008-06
    [mindate] => 
    [maxdate] => 
)
$parts = Array
(
    [date] => Datum
)
$min = Array
(
    [year] => 100
    [month] => 1
    [day] => 1
    [hour] => 0
    [minute] => 0
    [second] => 0
)
$max = Array
(
    [year] => 4000
    [month] => 12
    [day] => 31
    [hour] => 23
    [minute] => 59
    [second] => 59
)

//On the first run of the foreach-loop the inner variables are
$part = "date"
$value = "2008-06" //(what I entered in the form)
$min[$part] //(is not set)
$max[$part] //(is not set)

Probably the last if-clause: $value > $max[$part] is the one that triggers the validation error.
Since the date is input as a text field (not a select field) the whole "validating part by part" concept seems strange, and obviously does not work.
Should the validator be rewritten to split the text into year, month(, day, ...) and compare againt min/max? Is there some Date functiono to do it for us? Or could one simply compare strings instead, like compare $value > $max['year'].'-'.$max['month'] ?

Here is a patch that may work for the validation of the date. It does NOT work for the adjustment field, so adjustment values (if present) will falsely be considered invalid!

I'm still confused by the variable $values. Perhaps the only interresting contents of $values are ever $values['valuedate'] and $values['valueadjustment'], so that the foreach ($values ... loop can be replaced by simply grabbing the content of these known keys?

I'm re-working the filter, I'll file a patch soon. It wasn't picking up the right date type so it was treating all dates as unix timestamps, but as I looked at it I saw other ways to improve it by making it behave the same as the date argument (which is really just another filter).

The patch in #12 will only work on ISO dates, it will have the opposite problem and will break on timestamps, but don't worry, I already know where the problem is.

I've committed fixes for this to cvs. I think we can improve on the efficiency of the code in the future, but this should get the filters working.

Using the latest -dev releases for date (2008-Jun-17) and views (2008-Jun-16)

It didn't work with the filter I had set up on the earlier -dev release (which was perhaps to be expected).. so I removed the earlier filter, and added a new one

After selecting "Date: Date" as the filter type, the following warning appeared 11 times:
warning: preg_match() expects parameter 2 to be string, array given in <i>my_path_to_drupal</i>/includes/bootstrap.inc on line 718.
(this warning appears whenever I add a date filter; can't remember seeing it before, but I may be wrong...)

Chose type "Date only" and granularity "day".

Entering a specific date for the filter still produces is invalid

However, entering a specific date is not important for me - what I want to do is to filter for publish_date <= now and expiry_date >= now

I tried publish_date <= now, and this is working.
I tried expiry_date >= now and I get 2 warnings (no output):

    * user warning: Unknown column 'node_data_field_expiry_date.field_expiry_date_value' in 'field list' query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS nid, node_data_field_publish_date.field_publish_date_value AS node_data_field_publish_date_field_publish_date_value, node_data_field_expiry_date.field_expiry_date_value AS node_data_field_expiry_date_field_expiry_date_value, node.type AS node_type, node_data_field_publish_date.nid AS node_data_field_publish_date_nid, node.title AS node_title, node_revisions.body AS node_revisions_body, node_revisions.format AS node_revisions_format FROM node node LEFT JOIN content_type_news node_data_field_publish_date ON node.vid = node_data_field_publish_date.vid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE (node.type in ('news')) AND (DATE_FORMAT(STR_TO_DATE(node_data_field_publish_date.field_publish_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2008-06-17') AND (DATE_FORMAT(STR_TO_DATE(node_data_field_expiry_date.field_expiry_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2008-06-17') ORDER BY node_data_field_publish_date_field_publish_date_value DESC ) AS count_alias in <i>my_path_to_drupal</i>/modules/views/includes/view.inc on line 613.

    * user warning: Unknown column 'node_data_field_expiry_date.field_expiry_date_value' in 'field list' query: SELECT DISTINCT(node.nid) AS nid, node_data_field_publish_date.field_publish_date_value AS node_data_field_publish_date_field_publish_date_value, node_data_field_expiry_date.field_expiry_date_value AS node_data_field_expiry_date_field_expiry_date_value, node.type AS node_type, node_data_field_publish_date.nid AS node_data_field_publish_date_nid, node.title AS node_title, node_revisions.body AS node_revisions_body, node_revisions.format AS node_revisions_format FROM node node LEFT JOIN content_type_news node_data_field_publish_date ON node.vid = node_data_field_publish_date.vid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE (node.type in ('news')) AND (DATE_FORMAT(STR_TO_DATE(node_data_field_publish_date.field_publish_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2008-06-17') AND (DATE_FORMAT(STR_TO_DATE(node_data_field_expiry_date.field_expiry_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2008-06-17') ORDER BY node_data_field_publish_date_field_publish_date_value DESC LIMIT 0, 5 in <i>my_path_to_drupal</i>/modules/views/includes/view.inc on line 638. 

(the warnings appear identical except for the line numbers at the end.)

As the publish_date works and expiry date does not, then maybe it means some kind of corruption in my tables, so if you think this is the case, I'll remove the field from the CCK type, add it again, and try again with the views filter.

btw I am filtering on node type and published status, and sorting by publication date - all of this is fine.

I need to know what kind of fields the two dates are and an export of your view would help.

Both fieldtypes are configured as Date (note Datestamp or datetime) in CCK
(which is why it's odd that filtering works on one, but not the other!)

View export is attached.

Thanks, and sorry for being such a pain ;)

I updated to latest versions of date and views, problem persists

I deleted the expiry_date field in the content type, added it again (as expirydate), entered the dates in the example content (4 nodes) and tried again - and get the same unknown column error as before (this time, line 615 and 640).

OK, think I'm getting somewhere with this... wondered "what if the problem is not with the expirydate field, but the fact that I'm using 2 filters?"

So, I deleted both date filters, and add the expirydate > now filter, and it works as expected.
Then tried to add the publishdate filter, and it throws up the same error as before (except now it is user warning: Unknown column 'node_data_field_publish_date.field_publish_date_value' in 'field list' query: )

=> Date Views filter is working (at least, within the parameters I am using) for 1 filter, but not for 2.

Ah! Maybe we're getting somewhere. You're saying you can't add two date filters to the same view (I assume other filters are OK, just not another date filter). I can't think of any reason why that wouldn't work, but I haven't tested it.

What kind of date fields are you filtering on for the two (so I can set up something similar to test)? [Edit you already told me that, never mind this question]

I'm curious why you set the view in #19 up the way you did. You have the same date filter in the page and then again in the block as an override instead of using the same filter in both places with no override. I'm pretty sure if you just get rid of the override and use the same filter in both places things will work fine, but I still need to test the use of different filters in overrides because people will be overriding things.

At the time I was trying to setup something where the block would only display news for publish_date<=now and expiry_date>now, whereas the page would be an archive and only filter on publish_date<=now (i.e. even expired news would be available in the archive).

I was experimenting quite a bit, so perhaps the end result was redundant. However, I had since cleaned things up (together with updating to latest -devs) and for the post where I identified that the challenge was in handling 2 filters both the block and the page were using default settings.

Also, other filters (published=true, type=news) are co-existing peacefully... it's only with 2 date filters that the issue comes up.

I don't know enough about SQL to analyse this, but could it be that there's a typo in the sql query generation for 2 or more filters that is generating the error?

Renaming this for clarity.

Hiya :)

As far as I can tell, the problem is that when the table is joined the second time (for the second date field), the views' query object realises it doesn't need to be joined again and thus returns the same alias as the first time, rather than the one that was asked for.

The date filter ignores the alias returned by the views query, and tries to use the one it wanted. The following patch (against beta 3) fixes this (at least for the filter ; I guess if you use dates as arguments you might need a similar fix for that) :

--- date_api.views.inc  (revision 1162)
+++ date_api.views.inc  (working copy)
@@ -780,8 +780,9 @@

     $field = $this->options['date_field'];
     $bits = explode('.', $field);
-    $this->query->ensure_table($bits[0]);
-    $this->query->add_field($bits[0], $bits[1]);
+    $table_alias = $this->query->ensure_table($bits[0]);
+    $this->query->add_field($table_alias, $bits[1]);
+    $field = $table_alias.'.'.$bits[1];

     if (!empty($this->value[$prefix .'adjustment'])) {
       $adjustment = strtotime($this->value[$prefix .'adjustment'], 0);

Hope this helps ! Thanks for this very,very,very useful module !

Hi.
I'm running a theatre web site, and I've installed the latest Date module 6.x-2.x-dev version (2008-07-03), and also have version 6.x-2.0-beta of the CCK module, and version 6.x-2.0-beta4 of the Views module.

I've defined a content type ("play") with two Date fields ("premier", "last_performance"), and would like to filter so that a view shows all plays currently in production. Thus I've defined the following filters

Node: Type = play
Date: Date (node_data_field_premier.field_premier_value) <= now
Date: Date (node_data_field_last_performance.field_last_performance_value) > now

I've added a few play content nodes, and set the two dates in each so that "now" is between the premier date, and the last_performance date. Yet I do not see any node in the view. Instead, it shows the "Empty text" message, and the following error message before it:

user warning: Unknown table 'node_data_field_last_performance' in field list query: SELECT node.nid AS nid, node_data_field_premier.field_premier_value AS node_data_field_premier_field_premier_value, node_data_field_premier.field_premier_value2 AS node_data_field_premier_field_premier_value2, node_data_field_last_performance.field_last_performance_value AS node_data_field_last_performance_field_last_performance_value, node_data_field_last_performance.field_last_performance_value2 AS node_data_field_last_performance_field_last_performance_value2 FROM node node LEFT JOIN content_type_play node_data_field_premier ON node.vid = node_data_field_premier.vid WHERE (node.type in ('play')) AND (DATE_FORMAT(STR_TO_DATE(node_data_field_premier.field_premier_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2008-07-04') AND (DATE_FORMAT(STR_TO_DATE(node_data_field_last_performance.field_last_performance_value, '%Y-%m-%dT%T'), '%Y-%m-%d') > '2008-07- in /.../sites/all/modules/views/includes/view.inc on line 642.

If I remove one of the Date filters, the view shows as expected (but of course not yielding the original desired result, as one of the filters is not in effect).

The fix mentioned in comment #26 did not help me, as that was applied to a much older dev-version, so I wonder is there anything else I could try to do to get the desired view, please? (Other than using Drupal 5.7, where a similar setup works quite ok).

The export of the view attached.

Hannu

reported again at http://drupal.org/node/285946.

Working on this now. #26 is very helpful, I have to see if that will work in the latest code, which has changed quite a bit. Currently the second date filter pretty much trashes the first one, leaving lots of error messages all over the place.

I just committed a few more fixes to the date filter, including making sure more than one date filter will work on the same view. I can now put more than one date filter into a view and it filters correctly.

I still need to figure out how to get the default value showing up in the exposed filter, but that seems to be the way many Views filters behave, so we may be down to a Views issue, and that's a separate issue anyway.

Well...

(See #27 for background).

I installed the latest dev-tarball (Jul-24), and also have CCK 6.x-2.0-rc4, and Views 6.x-2.0-rc1.

I deleted the earlier view, and tried to create another so that it would filter all nodes of type 'play'. That went well. Then I tried to add a filter to display all type 'play' nodes, whose premier_date field is less or equal to 'now'. That went not so well. It said "date is invalid". (See a screenshot).

So, I would appreaciate any advice, still. My goal is to create a view that would display all nodes of type 'play' where 'now' is between the values of the two date fields of the content type.

@Hannu : What you report in #31 is obviously a different issue. Please post a new issue ; you can do so on this page : http://drupal.org/node/add/project_issue/date/bug . Select the version number as the 'dev' one and use a meaningful title such as '"now" reported as invalid date".

Please don't post anything else here unless it's directly related to the issue at hand ("Problems using 2 date filters in same view") ; it spams a number of people and the developers are more likely to ignore it.

The ability to display two filters is fixed. The other issue is that you tried to put 'now' in the date field instead of the adjustment field. The filters are still evolving, so that may not have been clear.

Thanks for the tip. I had not realized the adjustment thing. I got further with this, but I still can't get two date filters to work together. When I add the first one to show only nodes whose CCK Date field is less than equal to 'now', it works. But when I add another filter to show only nodes whose another CCK Date field is greater that equal to 'now', I get the following error message:

user warning: Unknown column 'node_data_field_last_performance.field_last_performance_value' in 'field list' query: SELECT COUNT(*) FROM (SELECT node.nid AS nid, node_data_field_premier.field_premier_value AS node_data_field_premier_field_premier_value, node_data_field_premier.field_premier_value2 AS node_data_field_premier_field_premier_value2, node_data_field_last_performance.field_last_performance_value AS node_data_field_last_performance_field_last_performance_value, node_data_field_last_performance.field_last_performance_value2 AS node_data_field_last_performance_field_last_performance_value2, node.title AS node_title FROM node node LEFT JOIN content_type_play node_data_field_premier ON node.vid = node_data_field_premier.vid WHERE (node.type in ('play')) AND (STR_TO_DATE(node_data_field_premier.field_premier_value, '%Y-%m-%dT%T') <= NOW()) AND (STR_TO_DATE(node_data_field_last_performance.field_last_performance_value, '%Y-%m-%dT%T') >= NOW()) ) AS count_alias in /.../sites/all/modules/views/includes/view.inc on line 652.

user warning: Unknown column 'node_data_field_last_performance.field_last_performance_value' in 'field list' query: SELECT node.nid AS nid, node_data_field_premier.field_premier_value AS node_data_field_premier_field_premier_value, node_data_field_premier.field_premier_value2 AS node_data_field_premier_field_premier_value2, node_data_field_last_performance.field_last_performance_value AS node_data_field_last_performance_field_last_performance_value, node_data_field_last_performance.field_last_performance_value2 AS node_data_field_last_performance_field_last_performance_value2, node.title AS node_title FROM node node LEFT JOIN content_type_play node_data_field_premier ON node.vid = node_data_field_premier.vid WHERE (node.type in ('play')) AND (STR_TO_DATE(node_data_field_premier.field_premier_value, '%Y-%m-%dT%T') <= NOW()) AND (STR_TO_DATE(node_data_field_last_performance.field_last_performance_value, '%Y-%m-%dT%T') >= NOW()) LIMIT 0, 10 in /.../sites/all/modules/views/includes/view.inc on line 677.

Then if I remove the first date filter, the second filter filters as expected. But I'm still in the situation where I cannot get the view to show only those nodes, where 'now' is between the two values in the two CCK Date fields.

Here's the export of the view:

$view = new view;
$view->name = 'current_repertoire';
$view->description = 'Tulevaa ohjelmistoa';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('fields', array(
  'title' => array(
    'label' => '',
    'link_to_node' => 1,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'play' => 'play',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
  ),
  'date_filter_1' => array(
    'operator' => '>=',
    'value' => array(
      'valueadjustment' => 'now',
      'minadjustment' => '',
      'maxadjustment' => '',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'date_fields' => array(
      'node_data_field_last_performance.field_last_performance_value' => 'node_data_field_last_performance.field_last_performance_value',
    ),
    'granularity' => 'day',
    'adjustment_field' => '2',
    'id' => 'date_filter_1',
    'table' => 'node',
    'field' => 'date_filter',
    'relationship' => 'none',
  ),
  'date_filter' => array(
    'operator' => '<=',
    'value' => array(
      'valueadjustment' => 'now',
      'minadjustment' => '',
      'maxadjustment' => '',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'date_fields' => array(
      'node_data_field_premier.field_premier_value' => 'node_data_field_premier.field_premier_value',
    ),
    'granularity' => 'day',
    'adjustment_field' => '2',
    'id' => 'date_filter',
    'table' => 'node',
    'field' => 'date_filter',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
  'role' => array(),
  'perm' => '',
));
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'ohjelmisto');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'weight' => 0,
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'weight' => 0,
));

I've got PHP 5.2.5, and the latest Date tarball (July 25), and CCK 6.x-2.0-rc4, and Views 6.x-2.0-rc1.

And just for the record, I truly appreciate all the good work done for this project.

Hannu

Doesn't the -dev version of date require the 2.x-dev version of views?

I wouldn't know, but Views 6.x-2.0-rc1 was the only one available at http://drupal.org/project/views.

views 2.x-dev version is at http://drupal.org/node/95897

(I don't know if this is the issue; but I think I remember at some point Karen recommending the 2.x-dev version of Views rather than the 2.0 branch)

Ok, found it, thanks. I tried the Views dev version, but the got same error message when I re-created the view from the scratch, so I guess that wasn't the issue.

The date filters have been reworked (see http://drupal.org/node/287437 for more details) and I am now able to combine more than one date filter in the same view. It is a complete rework of the filters, so you'll need to delete any existing date filters and create them over again to pick up the new parameters.

This was just committed, so if you get your code from the tarball, wait until tomorrow to test this.

You can use the latest official release of Views now, it has all the things we need. But if you're using CCK, you need the latest -dev version of Views anyway.

Good morning.

What I reported i #34 still happens to me. I believe I've got all the latest stuff, specifically Drupal 6.3, latest dev of Views (from http://drupal.org/node/95897), latest dev of Date (from last night ie. http://drupal.org/node/263344), and latest dev of CCK (from http://drupal.org/node/266142). Also PHP 5.2.5, and MySQL 5.0.51.

Right after adding the second date filter, I get the following error message (see also the attached screenshot):

user warning: Unknown column 'node_data_field_last_performance.field_last_performance_value' in 'field list' query: SELECT COUNT(*) FROM (SELECT node.nid AS nid, node_data_field_premier.field_premier_value AS node_data_field_premier_field_premier_value, node_data_field_premier.field_premier_value2 AS node_data_field_premier_field_premier_value2, node_data_field_last_performance.field_last_performance_value AS node_data_field_last_performance_field_last_performance_value, node_data_field_last_performance.field_last_performance_value2 AS node_data_field_last_performance_field_last_performance_value2, node.title AS node_title FROM node node LEFT JOIN content_type_play node_data_field_premier ON node.vid = node_data_field_premier.vid WHERE (DATE_FORMAT(STR_TO_DATE(node_data_field_premier.field_premier_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2008-07-31') AND (DATE_FORMAT(STR_TO_DATE(node_data_field_last_performance.field_last_performance_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2008-07-31') ) AS count_alias in /var/www/vhosts/aavista.net/subdomains/kaarina/httpdocs/sites/all/modules/views/includes/view.inc on line 652.
user warning: Unknown column 'node_data_field_last_performance.field_last_performance_value' in 'field list' query: SELECT node.nid AS nid, node_data_field_premier.field_premier_value AS node_data_field_premier_field_premier_value, node_data_field_premier.field_premier_value2 AS node_data_field_premier_field_premier_value2, node_data_field_last_performance.field_last_performance_value AS node_data_field_last_performance_field_last_performance_value, node_data_field_last_performance.field_last_performance_value2 AS node_data_field_last_performance_field_last_performance_value2, node.title AS node_title FROM node node LEFT JOIN content_type_play node_data_field_premier ON node.vid = node_data_field_premier.vid WHERE (DATE_FORMAT(STR_TO_DATE(node_data_field_premier.field_premier_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2008-07-31') AND (DATE_FORMAT(STR_TO_DATE(node_data_field_last_performance.field_last_performance_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2008-07-31') LIMIT 0, 10 in /var/www/vhosts/aavista.net/subdomains/kaarina/httpdocs/sites/all/modules/views/includes/view.inc on line 677.

The export of the view:

$view = new view;
$view->name = 'current_repertoire';
$view->description = 'Tulevaa ohjelmistoa';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('fields', array(
  'title' => array(
    'label' => '',
    'link_to_node' => 1,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'date_filter' => array(
    'operator' => '<=',
    'value' => array(
      'valuedate' => NULL,
      'mindate' => NULL,
      'maxdate' => NULL,
      'default_date' => 'now',
      'default_to_date' => '',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'date_fields' => array(
      'node_data_field_premier.field_premier_value' => 'node_data_field_premier.field_premier_value',
    ),
    'granularity' => 'day',
    'form_type' => 'date_select',
    'default_date' => 'now',
    'default_to_date' => '',
    'id' => 'date_filter',
    'table' => 'node',
    'field' => 'date_filter',
    'relationship' => 'none',
  ),
  'date_filter_1' => array(
    'operator' => '>=',
    'value' => array(
      'valuedate' => NULL,
      'mindate' => NULL,
      'maxdate' => NULL,
      'default_date' => 'now',
      'default_to_date' => '',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'date_fields' => array(
      'node_data_field_last_performance.field_last_performance_value' => 'node_data_field_last_performance.field_last_performance_value',
    ),
    'granularity' => 'day',
    'form_type' => 'date_select',
    'default_date' => 'now',
    'default_to_date' => '',
    'id' => 'date_filter_1',
    'table' => 'node',
    'field' => 'date_filter',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
  'role' => array(),
  'perm' => '',
));
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'ohjelmisto');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'weight' => 0,
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'weight' => 0,
));

I've cleared the views' cache, removed the earlier filters (as adviced), and only then added the new filters via the new UI (which is much clearer now than before, thanks).

So, what should I try next? My goal is to get the view to show only those nodes, where 'now' is between the values of the two Date CCK fields.

Did you delete your old filters and start over and then save your view with the new values? The new code is different and the values from your old filters won't work. Plus I made some more commits this morning to finish up getting this working correctly. I've extensively tested it with multiple date filters and the latest code works fine.

If you have trouble with the latest code (including this morning's commits) after deleting your old filters and creating new ones and saving your view with the new filters, then you can reopen.

Yes. Like I said, I deleted the old filters, and started over. In fact, I went further, and deleted the whole view, cleared caches, and started by creating a new one. And got the error message shown in #40 when I added the second Date filter.

I had the tarball of Date created last night, so I did not have the commits made after that. I will try again tomorrow morning with the next tarball.

Ok, I'll reopen as I still have problems. I can see that I can have two date filters in the same view, if there is at most one CCK Date field involved. But when there are two CCK date fields, I get the same error message I've reported many times already.

I started all over from the scratch. I have the same setup described in #40, with the exception that I now have last nights tarball from http://drupal.org/node/263344

Here's the log of my actions. No old content types, no old fields, no old views. And I still get the error message:

1. Created a new content type (Name: Test content, Type: testcontent) at /admin/content/types/add and saved it.
2. Edited the newly created content type at /admin/content/node-type/testcontent and added a field at /admin/content/node-type/testcontent/add_field. Filled the form thus: Field_name: beginning (field_beginning), Label: Beginning, Field type: Date. Clicked "Continue".
3. Selected Widget type "Select List", and clicked "Continue".
4. Specified "Custom input format": %d.%m.%Y, and checked the "Required" checkbox, chose "Granularity" to include only Year, Month, and Day. (Left all other options as they were). Saved field settings.
5. Repeated steps 2-4 for another field; Field_name: ending (field_ending), Label: Ending, Field type: Date.
6. Created a node titled "Test node" at node/add/testcontent, and selected the Beginning to be 1-Jun-2008, and the Ending to be 31-Aug-2008. (Saved, and saw that Beginning is "01.06.2008, and Ending is 31.08.2008 (as expected).
7. Started to create a view at /admin/build/views/add
8. Filled the form: View name: testview, View Description: Test View, View type: Node. Clicked "Next".
9. Set the following Default settings: Fields: Node Title (set an empty Label, and checked the "Link this field to its node" checkbox. Clicked "Update" (and the Live preview gave me all node titles (as expected)). Clicked "Save".
10. Added a filter "Node: Type" where Operator: "Is one of", and Node type: "Test content". Clicked "Update", and the Live preview showed only the title of the node created in step 6 (as expected). Clicked "Save".
11. Added another filter "Date: Date", where Date form type: Select, Granularity: Day, and Date field: Content: Date: Beginning (field_beginning). Clicked "Update".
12. Selected Operator: "Is less than equal to", and Date default: now. Clicked "Update", and the Live preview showed only the node created in step 6 (as expected).
13. Clicked "Save".
14. Added yet another filter "Date: Date", where Date form type: Select, Granularity: Day, and Date field: Content: Date: Ending (field_ending). Clicked "Update".
15. Selected Operator: "Is greater than or equal to", and Date default: now. Clicked "Update", and got the following error message:

    user warning: Unknown column 'node_data_field_ending.field_ending_value' in 'field list' query: SELECT COUNT(*) FROM (SELECT node.nid AS nid, node_data_field_beginning.field_beginning_value AS node_data_field_beginning_field_beginning_value, node_data_field_beginning.field_beginning_value2 AS node_data_field_beginning_field_beginning_value2, node_data_field_ending.field_ending_value AS node_data_field_ending_field_ending_value, node_data_field_ending.field_ending_value2 AS node_data_field_ending_field_ending_value2, node.title AS node_title FROM node node LEFT JOIN content_type_testcontent node_data_field_beginning ON node.vid = node_data_field_beginning.vid WHERE (node.type in ('testcontent')) AND (DATE_FORMAT(STR_TO_DATE(node_data_field_beginning.field_beginning_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2008-08-01') AND (DATE_FORMAT(STR_TO_DATE(node_data_field_ending.field_ending_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2008-08-01') ) AS count_alias in /var/www/vhosts/aavista.net/subdomains/kaarina/httpdocs/sites/all/modules/views/includes/view.inc on line 652.
    user warning: Unknown column 'node_data_field_ending.field_ending_value' in 'field list' query: SELECT node.nid AS nid, node_data_field_beginning.field_beginning_value AS node_data_field_beginning_field_beginning_value, node_data_field_beginning.field_beginning_value2 AS node_data_field_beginning_field_beginning_value2, node_data_field_ending.field_ending_value AS node_data_field_ending_field_ending_value, node_data_field_ending.field_ending_value2 AS node_data_field_ending_field_ending_value2, node.title AS node_title FROM node node LEFT JOIN content_type_testcontent node_data_field_beginning ON node.vid = node_data_field_beginning.vid WHERE (node.type in ('testcontent')) AND (DATE_FORMAT(STR_TO_DATE(node_data_field_beginning.field_beginning_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2008-08-01') AND (DATE_FORMAT(STR_TO_DATE(node_data_field_ending.field_ending_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2008-08-01') LIMIT 0, 10 in /var/www/vhosts/aavista.net/subdomains/kaarina/httpdocs/sites/all/modules/views/includes/view.inc on line 677.
    

So, the problem exists still. Any advice would be greatly appreciated.

Looking at the query in #43, this is similar to the problem I fixed in #26 ; the table is only joined once (as 'node_data_field_beginning') but used with two different names (node_data_field_beginning and node_data_field_ending).

It is correct to only join this table once, therefore the problem is that it shouldn't use the name 'node_data_field_ending' in the parameter list.

I haven't updated to the last version of Date yet, so can't look at the code ; but I have a silly question (and please, forgive me for asking but you never know!) : How did you unpack the new Date module ? In particular, what did you do with the old one ?

This is something that happened to me once : I wanted to keep a copy of the old module, just in case, so under the /modules directory I created an 'attic' directory, and moved it in there. Then I unpacked the new one ; so in effect I had /modules/attic/themodule and /modules/themodule. Well guess what, Drupal was reading the data and files from /modules/attic/themodule and not from /modules/themodule ! Took me a while to work it out.... I know it's silly, and it's probably not the problem you are seeing, but it doesn't hurt to mention it !

Hi.

I unpacked the .gz with Stuffit Expander, and then untarred the tar. (Or rather, my Mac OS X did that all for me). Then I had just a "date" folder, which I ftp'd to the test site. Before that I had deleted the previous date folder. (I've learned to do that after reading solutions to other issues).

I'll look into this. I was testing date fields from the same table which don't have any problem, I missed the note about fields from different tables. This is probably because of new Views handling that changes the table names behind the scenes if they are the same table to eliminate a join. I have to figure out how to get the right results when that happens since I can't tell ahead of time if the table will or won't change.

Actually it was the other way around. This bug only surfaces if you have more than one non-multiple date field in the same content type and you try to add both of them to the same view, which is why I hadn't stumbled across the problem.

I found a way to override the default Views method of joining tables. Normally Views which won't join in the second table if it's a duplicate but we have to join both tables because we use the table names in our WHERE clauses. We can't predict when one of the tables might be dropped, changing the table name of the field, so we have to be able to count on both those tables being in the query.

I just committed this fix and tested it by creating a scenario that produces the problem and then fixing it with my patch.

The same issue will be true for two date arguments in the same view, so I applied the same fix there.

Thank you very much! I appreciate your fast reply. I downloaded the updated files, and the problem is gone. Thanks again!

I just committed this fix and tested it by creating a scenario that produces the problem and then fixing it with my patch.

Please excuse my ignorance: It sounds like Karen committed a patch in CVS today? I'd love to try it out. Do I need to get the new revisions out of CVS HEAD for this project?

Never mind:

This must be the new code. http://ftp.drupal.org/files/projects/date-6.x-2.x-dev.tar.gz

I'm having this proble on D5 so I'll try this: http://ftp.drupal.org/files/projects/date-5.x-2.x-dev.tar.gz (Currently I'm using http://ftp.drupal.org/files/projects/date-5.x-2.0-rc4.tar.gz)

This particular problem is specific to the way that Views 2 works in Drupal 6, so this won't be applicable to the D5 code. The links you posted are the right tarballs, but note that the tarballs only get updated every 12 hours or so, so they won't have changes that have been committed since that.