Not sure if this is a bug or just pilot error. Suspect the later but will take any advice.

(as of Feb 16, 2009) With latest versions of date, calendar, views, cck and using D6.9 (and php 5), I have a view of type "views_calc" with multiple exposed filters. One of the exposed filters is a date with settings of "is equal to, optional, granularity = month, method="and"". The view displays as I'd hope - with options to select a year, and a month. In other words, in certain situations I need to be able to show all my past events for a year, but in other situations I need to be able to show past events just month by month. This is for statistical tracking purposes, some of which are reported monthly, others yearly.

So - if I select a year, say 2009, and leave month set at "month" I don't get all my 2009 events, January is missing. So if I then set it to 2009 + January I can get that month, but not February (which makes sense).

I expected that if I selected a year but ignored the month settings that I'd get all the events for the selected year. Am I missing something or is this not working as it should?

Apologies if starting with the Date Module support is not the place to be - and thanks!

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Toddv’s picture

Title: problem/question re: having multiple exposed date filters » Exposed date filters not filtering properly when granularity is set to "month"
Project: Date » Calendar
Component: Date CCK Field » Code
Category: support » bug

Moved this from the Date Queue to Calendar

Elaborating on my initial post:

Using Date/Calendar 6.x-2.0-rc6, drupal 6.9, views 6.x-2.3, views calc 6.x-2.4 and php 5.2.8 on a shared web host (hostgator).

I have a content type used for calendering events and tracking associated statistics about these events. I use various cck for the various tracking fields, for instance, how many people came to an event, and various metrics about what was done at the event.

I would like to be able to have views calc total by month by year, and just by year (including all months) so I have an exposed date field in my view with granularity of month. As a test I have six events entered, spanning 2008 and 2009, 3 events in each year. When I set the exposed date filter to "2008" year and "any" for month the page gives one event for the wrong year, 2009.

If I return the granularity to "month", and sort for 2009/February my event in February does not display. But if I return the filter settings to 2009/any my February event displays, but my January one does not.

It seems that when the granularity is set to "month" and the view is exposed that the date filter does not work properly.
If I edit the view, setting granularity to year it filters properly by year.

Thank you for all your excellent work on these modules

adet’s picture

Project: Calendar » Date

Hello,

I have the same problem when having a exposed date filter with a "month" granularity: if I choose "All" value for the Year OR for the Month, the SQL query search for the current Year and Month values: "2009-03", instead of filtering on all years or all months.

This is a critical problem for me. Do you have any clue?

I tested this with de 6x2-rc6, 6x2-dev and 6x2 versions, and I have the same problem.

Thank you much in advance!

A.D

KarenS’s picture

Status: Active » Fixed

There is no such value as 'All', this makes no sense. There is a way to select a year, month, and day, but it expects that you are using that to select a specific date to filter by. It does not work the way you are trying to use it. If you change the filter type to be a popup instead of a select you'll see it is expecting a complete date.

There are also lots and lots of changes since rc6, so if you're using that version you're way out of date. You need the latest code for both Date and Calendar.

Status: Fixed » Closed (fixed)

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

BarisW’s picture

Status: Closed (fixed) » Active

Dear KarenS,

It's not fixed. And I do agree with ToddV and adet that this is a bug. I'll explain why.

When I set granularity to Month, I see two select options: Year and Month. So far so good.
If I select 2010 as year and january as month, I'd expect to see all nodes from this month only (which works).

When I select 2010 and leave the month unselected I expect to see all nodes with the date 2010, whatever the month. This does NOT work.
Instead, it shows me all nodes from january 2010 (the first month of the selected year) but the month value doesn't reflect this. This really is a WTF.

There is currently NO way to use a date filter to select nodes from a specific month or a full year.
Isn't it an easy fix to leave out the month when it is not selected?

SELECT node.nid AS nid,
   node.title AS node_title,
   node.language AS node_language,
   node.type AS node_type,
   node.vid AS node_vid,
   node.created AS node_created,
   node_revisions.teaser AS node_revisions_teaser,
   node_revisions.format AS node_revisions_format,
   node.sticky AS node_sticky
 FROM node node 
 LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
 WHERE ((node.status <> 0) AND (node.type in ('news')) AND (node.language in ('***CURRENT_LANGUAGE***', '***NO_LANGUAGE***')))
    AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node.created), SEC_TO_TIME(7200)), '%Y-%m') = '2010-01')
   ORDER BY node_sticky DESC, node_created DESC

Thus:

AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node.created), SEC_TO_TIME(7200)), '%Y') = '2010')
Beatle88’s picture

Hi,

I have the same issue - I can filter by year and month at the same time, but if I select only year I only get the nodes from january of that year. There is no way to view all the nodes from one year while the granularity is set to month. Is there any way to fix this?

Cheers
Beatle88

fastangel’s picture

Status: Active » Needs review
FileSize
1.5 KB

Hi,

I attach path that solve this error. I've tried on 6.x-2.x-dev.

fastangel’s picture

Version: 6.x-2.0-rc6 » 6.x-2.x-dev
KarenS’s picture

Status: Needs review » Needs work

I don't think this is going to work the way you expect it to. The module was constructed to use a full date, sending it something other than a full date will produce different, possibly incorrect, results.

You have a patch to omit parts of the date but I have no idea what that is actually going to do to the view. This needs testing. I am fairly sure it is not as simple as you think it is, that passing incomplete results is not going to work right.

And I insist it is not a bug if you don't like the way something was designed. It does what it was designed to do. We could add a validation error to provide more information if you leave part of a date off -- *that* might be a bug, that there is no error. But it is not a 'bug' because you can't leave part of a date out when that is the way it was designed to work.

fastangel’s picture

Hi,

I think that if a bug. If in a view select date select with granularity month the options in filter exposed are:
Year: None, 2011, 2010 ....
Month: None, January, February, ....

If I select Year 2011 and Month None One should also understand that show items for 2011 y not items for 2011 and January that's how it works now.

BarisW’s picture

Karen, if the way it has to work should be that you have to select all options, then make the interface reflect this. It is confusing that the end user can leave the month field set to 'All', to only get results of the first month.

So: or make the code work and make it possible to select only a month or a year (yes, please!) OR make all dropdowns required and remove the 'All' option.

The way it is designed now is confusing and not useful.

capellic’s picture

I'd like to see, at least, a validation error added here. Same with when someone defines the month and not the year. The result is that the filter is not applied at all. An error would be appreciated.

nikitas’s picture

hi .
have you find anything ?
i'm having issues with date + filterings but with months-year .
my post is here http://drupal.org/node/1120738
it might helps

capellic’s picture

Here's what I did for a "soft landing" to at least explain to users what was going on.

Add a "Global: Null" argument to your view. In that view, "Provide default argument" and then select "PHP code". In the "PHP argument code" text area, enter:

return TRUE;

Then add a validator in that argument, "PHP Code". Your variables will vary, but the structure should be the same. Here is the code that I used. It considers that you may be using date ranges.

if ($_REQUEST['date-op'] == 'between') {
	$date_range_values[] = $_REQUEST['date']['min']['year'];
	$date_range_values[] = $_REQUEST['date']['min']['month'];
	$date_range_values[] = $_REQUEST['date']['max']['year'];
	$date_range_values[] = $_REQUEST['date']['max']['month'];
	
	$values = 0;
	foreach ($date_range_values as $v) {
		if ($v != '') {
			$values++;
		}
	}
	
	if (($values > 0) && ($values < 4)) {
		drupal_set_message(t('You must define the year and month for the "From Date" and "To Date" when defining a date range.'), 'error');
		return FALSE;
	}
} else {
	if (($_REQUEST['date']['value']['year'] != '') && ($_REQUEST['date']['value']['month'] == '')) {
		drupal_set_message(t('You must select a month'), 'error');
		return FALSE;
	} else if (($_REQUEST['date']['value']['year'] == '') && ($_REQUEST['date']['value']['month'] != '')) {
		drupal_set_message(t('You must select a year'), 'error');
		return FALSE;
	}
}
return TRUE;

For the "Action to take if argument does not validate", set that to "Display empty text" to avoid showing only January nodes if only year is selected.

Rob_Feature’s picture

I agree with alot of the above comments. I think:

  1. If it is NOT a bug, and it's intended functionality, it should tell the user they just chose an invalid selection
  2. If it IS a bug, then it should work when only one date item is chosen with the others left at defaults.

I'm not sure it's a good option to allow it to provide no results but no tell the user they did anything wrong (and continue to let them think they did it correctly and just got no results).

rvilar’s picture

I have the same problem. Someone have a workaround for this problem? Maybe a custom views handler that you can share?

brunorios1’s picture

same here...

bartclarkson’s picture

Whether bug or expected, it's certainly a limitation. Desirable functionality is ability to allow an IMPLIED December of the same year to be set in the To Date when no month is specified in the From Date information.

You may find yourself on deadline, with this requirement. For those of you that have made your peace with presentation-layer fixes to server-side limitations, here's an approach that may help you.

On the exposed filter settings, select "Is between" for your operator. Set all the defaults for the four dropdown menus to Year and Month, respectively. Then enter this or similar javascript into the view header or whatever.

In plain English, this approach says that we're going to hide the To Dates on the user form. But we're going to set these To fields to December of the same year as the From dates if no From month has been chosen. If a month is chosen, then the year and month menus are simply synched.

Your #selectors will be based on your filter name setting. I set mine to 'month' for whatever reason.

<script type="type/javascript">
	$('.container-inline-date:even').hide();
	$('#edit-month-min-wrapper label').hide();
	
	$('#edit-month-min-year').change(function() {
		$('#edit-month-max-year')[0].selectedIndex = $(this)[0].selectedIndex;
		if ($('#edit-month-min-month')[0].selectedIndex == 0)
			$('#edit-month-max-month')[0].selectedIndex = 12;
		else
			$('#edit-month-max-month')[0].selectedIndex = $('#edit-month-min-month')[0].selectedIndex;
	});
	$('#edit-month-min-month').change(function() {
		if ($(this)[0].selectedIndex == 0)
			$('#edit-month-max-month')[0].selectedIndex = 12;
		else
			$('#edit-month-max-month')[0].selectedIndex = $(this)[0].selectedIndex;
	});	
</script>
liquidcms’s picture

Version: 6.x-2.x-dev » 7.x-2.x-dev

bumping this to D7

i would also like to NOT be forced to enter the month - in D7, i do get a validation error msg stating i must enter a month

the sql spit out by Views for my example case has this as part of the WHERE:

AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(field_data_field_event_date.field_event_date_value), SEC_TO_TIME(-14400)), '%Y-%m') = '2012-05') 

simply altering this to:

AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(field_data_field_event_date.field_event_date_value), SEC_TO_TIME(-14400)), '%Y') = '2012') 

i think gives me what i want; but sadly; i think i will hit validation error first.

reysharks’s picture

I'll try to explain how I managed to fix this issue...

first of all, i need to skip the validation error:

function mymodule_form_alter(&$form, &$form_state, $form_id){
	switch($form_id){
		case 'views_exposed_form':
			if(isset($form["field_article_date_value"])): // the id of the field 
				array_unshift($form["field_article_date_value"]["#element_validate"],"mymodule_date_views_select_validate");
			endif;
		break;

With this method i prepend my own validation function before the default "date_views_select_validate" (date/date_views/date_views.module)

with my own validation function i put some dummy content in the month field

function mymodule_date_views_select_validate(&$form, &$form_state) {
	
	if(empty($form_state['input']['field_article_date_value']['value']['month']) && !empty($form_state['input']['field_article_date_value']['value']['year']))
		$form_state['input']['field_article_date_value']['value']['month'] = "13"; // 
}

In this way I can skip the validation check so the form submits.

After this I've to alter the query, fortunately we have track of what we submitted with the filter form, so we take it and generate a where condition for the query.

function mymodule_views_query_alter(&$view, &$query){
	if($view->name=='viewname' && $view->current_display == 'displayname'){
				if(isset($view->exposed_input['field_article_date_value']['value']['month'])){

		if($view->exposed_input['field_article_date_value']['value']['month']=='' && $view->exposed_input['field_article_date_value']['value']['year']!=''){
			$whereCond = array(
				"field" => "DATE_FORMAT(field_data_field_article_date.field_article_date_value, '%Y') = :field_data_field_article_date_field_article_date_value",
				"value" => array(":field_data_field_article_date_field_article_date_value" => $view->exposed_input['field_article_date_value']['value']['year']),
				"operator" => "formula"
				);
			$view->query->where[1]['conditions'][] = $whereCond;
		}
	}
	}
}

This should be enough...
Hope is what you were looking for.

tostinni’s picture

@reysharks thanks for sharing your script, it works almost perfectly for me.

The only problem was that the pager was not retrieving the filter's values and keep losing the year for the pages other than the first one.
Each page link of the pager would only get an empty field_article_date_value['value'] parameter instead of 2 field_article_date_value['value']['month'] and field_article_date_value['value']['year'].

Adding this at the end of your views_query_alter() got me the correct behavior for the pages after the first one:

$view->exposed_raw_input['field_article_date_value'] = $view->exposed_input['field_article_date_value'];
sushantpaste’s picture

Issue summary: View changes

#20 works well,but issues comes when we have pager.

mcrittenden’s picture

Here's a slightly cleaned up version of #20. Note that you'll need to find/replace "field_news_post_date" with your actual field name.

I also had to add a FROM_UNIXTIME() to the last function to get it to work correctly, since in my case the values are stored as timestamps.

/**
 * Implements hook_form_FORM_ID_alter().
 *
 * @param $form
 * @param $form_state
 * @param $form_id
 */
function yourmodule_form_views_exposed_form_alter(&$form, &$form_state, $form_id) {
  if (isset($form["field_news_post_date_value"])) {
    // Add validation to the News Promo view so that we can support filtering by Year only,
    // as opposed to it having to be Month + Year.
    array_unshift($form["field_news_post_date_value"]["#element_validate"], "yourmodule_date_views_select_validate");
  }
}

/**
 * Validation callback for the News Promo view exposed filters form.
 *
 * @param $form
 * @param $form_state
 */
function yourmodule_date_views_select_validate(&$form, &$form_state) {
  if(empty($form_state['input']['field_news_post_date_value']['value']['month']) &&
    !empty($form_state['input']['field_news_post_date_value']['value']['year'])) {
    // If the "Month" filter was left empty, add a dummy value so that validation
    // passes and the form can submit.
    $form_state['input']['field_news_post_date_value']['value']['month'] = "13";
  }
}

/**
 * Implements hook_views_query_alter().
 *
 * If the Month filter was left empty, then just add a new condition to filter
 * by the Year only, instead of Month + Year.
 *
 * @param $view
 * @param $query
 */
function yourmodule_views_query_alter(&$view, &$query){
  if ($view->name=='news_promo' && $view->current_display == 'default'){
    if (isset($view->exposed_input['field_news_post_date_value']['value']['month'])){
      if ($view->exposed_input['field_news_post_date_value']['value']['month']=='' 
        && $view->exposed_input['field_news_post_date_value']['value']['year']!=''){
        $whereCond = array(
          "field" => "DATE_FORMAT(FROM_UNIXTIME(field_data_field_news_post_date.field_news_post_date_value), '%Y') = :year",
          "value" => array(":year" => $view->exposed_input['field_news_post_date_value']['value']['year']),
          "operator" => "formula"
        );
        $view->query->where[1]['conditions'][] = $whereCond;
        $view->exposed_raw_input['field_news_post_date_value'] = $view->exposed_input['field_news_post_date_value'];
      }
    }
  }
}