I have a content type with a date field set to month/year; I am creating these types to be displayed in a block and a page view for an entire month. At the beginning of the next month, the next node needs to be displayed in the view. I am displaying only one result (only one should match anyway.)

If I use just the published and node type criteria all is well. But when I add EITHER the month or the year (or both) into the view, then I get a SQL error. Here's what it looks like with both:

user warning: Column 'field_promotion_period_value' in where clause is ambiguous query: SELECT node.nid, node_data_field_logo_image.field_logo_image_fid AS node_data_field_logo_image_field_logo_image_fid, node_data_field_logo_image.field_logo_image_title AS node_data_field_logo_image_field_logo_image_title, node_data_field_logo_image.field_logo_image_alt AS node_data_field_logo_image_field_logo_image_alt, node.nid AS node_nid FROM node node LEFT JOIN content_type_promotion node_data_field_promotion_period ON node.vid = node_data_field_promotion_period.vid LEFT JOIN content_type_promotion node_data_field_logo_image ON node.vid = node_data_field_logo_image.vid WHERE (node.status = '1') AND (node.type IN ('promotion')) AND (EXTRACT(YEAR FROM( REPLACE(node_data_field_promotion_period.field_promotion_period_value,'T',' ')))=field_promotion_period_value) AND (EXTRACT(MONTH FROM( REPLACE(node_data_field_promotion_period.field_promotion_period_value,'T',' ')))=field_promotion_period_value) LIMIT 0, 1 in /var/www/includes/database.mysql.inc on line 172.

I am using a text/jscalendar widget. I am GMT-8 but the field does not use a timezone option because it only has month/year. No multiple. Yes required. It's a date field.

Here's some other info from the system info module.

Web Server
Type Apache
Version 2.0.55
PHP Interface Module
Modules
mod_rewrite Loaded

PHP
Version 5.1.2

Database
Type MySQL
Version 5.0.22-Debian_0ubuntu6.06.2-log
Charset utf8
Collation utf8_general_ci
Create Temporary Tables Allowed
Lock Tables Allowed

Comments

hyperlogos’s picture

Anyone have any ideas on this? I've had to go back to Date 5.x-1.2 to get this working properly (and it does work there, unlike in 5.x-1.3 with the date api module.)

hyperlogos’s picture

Okay, this is truly bizarre. I went back to 1.2 and my view started working.

Then I created another content type using the same field definition, and cloned the original view to make the new one. Changed only the content type in the new view. It works for the new content type. But now I view my original view and it has stopped working again! And I made NO changes to THAT view, or the corresponding content type.

Here is my original view:

  $view = new stdClass();
  $view->name = 'promo_current';
  $view->description = 'Current Promotion';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'Current Promotion';
  $view->page_header = '';
  $view->page_header_format = '1';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = '<p>No promotion found.</p>';
  $view->page_empty_format = '1';
  $view->page_type = 'list';
  $view->url = 'view/promo/current';
  $view->use_pager = FALSE;
  $view->nodes_per_page = '1';
  $view->block = TRUE;
  $view->block_title = 'Current Promotion';
  $view->block_header = '';
  $view->block_header_format = '1';
  $view->block_footer = '';
  $view->block_footer_format = '1';
  $view->block_empty = '';
  $view->block_empty_format = '1';
  $view->block_type = 'list';
  $view->nodes_per_block = '1';
  $view->block_more = FALSE;
  $view->block_use_page_header = FALSE;
  $view->block_use_page_footer = FALSE;
  $view->block_use_page_empty = FALSE;
  $view->sort = array (
    array (
      'tablename' => 'node',
      'field' => 'changed',
      'sortorder' => 'DESC',
      'options' => 'normal',
    ),
  );
  $view->argument = array (
  );
  $view->field = array (
    array (
      'tablename' => 'node_data_field_logo_image',
      'field' => 'field_logo_image_fid',
      'label' => '',
      'handler' => 'content_views_field_handler_ungroup',
      'options' => 'default',
    ),
    array (
      'tablename' => 'node',
      'field' => 'body',
      'label' => '',
      'handler' => 'views_handler_field_body',
    ),
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'status',
      'operator' => '=',
      'options' => '',
      'value' => '1',
    ),
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => 'promotion',
),
    ),
    array (
      'tablename' => 'node_data_field_promotion_period',
      'field' => 'field_promotion_period_value_year',
      'operator' => '=',
      'options' => '',
      'value' => 'now',
    ),
    array (
      'tablename' => 'node_data_field_promotion_period',
      'field' => 'field_promotion_period_value_month',
      'operator' => '=',
      'options' => '',
      'value' => 'now',
    ),
  );
  $view->exposed_filter = array (
  );
  $view->requires = array(node, node_data_field_logo_image, node_data_field_promotion_period);
  $views[$view->name] = $view;

here is my new view, which is working STILL

  $view = new stdClass();
  $view->name = 'schedule_current';
  $view->description = 'Current Schedule';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'Current Schedule';
  $view->page_header = '';
  $view->page_header_format = '1';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = '<p>No promotion found.</p>';
  $view->page_empty_format = '1';
  $view->page_type = 'list';
  $view->url = 'view/schedule/current';
  $view->use_pager = FALSE;
  $view->nodes_per_page = '1';
  $view->block = TRUE;
  $view->block_title = 'Current Schedule';
  $view->block_header = '';
  $view->block_header_format = '1';
  $view->block_footer = '';
  $view->block_footer_format = '1';
  $view->block_empty = '';
  $view->block_empty_format = '1';
  $view->block_type = 'list';
  $view->nodes_per_block = '1';
  $view->block_more = FALSE;
  $view->block_use_page_header = FALSE;
  $view->block_use_page_footer = FALSE;
  $view->block_use_page_empty = FALSE;
  $view->sort = array (
    array (
      'tablename' => 'node',
      'field' => 'changed',
      'sortorder' => 'DESC',
      'options' => 'normal',
    ),
  );
  $view->argument = array (
  );
  $view->field = array (
    array (
      'tablename' => 'node_data_field_logo_image',
      'field' => 'field_logo_image_fid',
      'label' => '',
      'handler' => 'content_views_field_handler_ungroup',
      'options' => 'default',
    ),
    array (
      'tablename' => 'node',
      'field' => 'body',
      'label' => '',
      'handler' => 'views_handler_field_body',
    ),
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'status',
      'operator' => '=',
      'options' => '',
      'value' => '1',
    ),
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => 'gaming_schedule',
),
    ),
    array (
      'tablename' => 'node_data_field_promotion_period',
      'field' => 'field_promotion_period_value_year',
      'operator' => '=',
      'options' => '',
      'value' => 'now',
    ),
    array (
      'tablename' => 'node_data_field_promotion_period',
      'field' => 'field_promotion_period_value_month',
      'operator' => '=',
      'options' => '',
      'value' => 'now',
    ),
  );
  $view->exposed_filter = array (
  );
  $view->requires = array(node, node_data_field_logo_image, node_data_field_promotion_period);
  $views[$view->name] = $view;

The following is the code from the "promotion" content type that matches the first view:

$content[type]  = array (
  'name' => 'Promotion',
  'type' => 'promotion',
  'description' => 'Promotions produce gaming activity in the Casino. They have terms, schedules, and information.',
  'title_label' => 'Name',
  'body_label' => 'Promo Body',
  'min_word_count' => '0',
  'help' => '',
  'node_options' => 
  array (
    'status' => true,
    'promote' => false,
    'sticky' => false,
    'revision' => false,
  ),
  'comment' => '0',
  'upload' => '0',
  'fivestar' => 0,
  'fivestar_position' => 'above',
  'image_attach' => '0',
  'scheduler' => 1,
  'old_type' => 'promotion',
  'orig_type' => '',
  'module' => 'node',
  'custom' => '1',
  'modified' => '1',
  'locked' => '0',
  'links_related_types' => '0',
  'node_images_position' => 'hide',
  'node_images_gallery_link' => '1',
  'node_images_teaser_images' => '2',
  'node_images_body_images' => '',
  'node_images_teaser_format' => 'thumbs',
  'node_images_body_format' => 'thumbs',
  'send_type' => 'promotion',
  'news_promotion_fulltext' => '',
  'news_promotion_linktext' => 'send <em>news</em>',
  'news_promotion_subject' => '<em>News</em> from RRRC Intranet Test',
  'news_promotion_message' => '',
  'news_promotion_template' => '%message
%body
',
  'news_promotion' => 0,
  'news_promotion_pernode' => '',
  'send_promotion_fulltext' => '',
  'send_promotion_linktext' => 'send to friend',
  'send_promotion_subject' => '<em>Send to Friend</em> from RRRC Intranet Test',
  'send_promotion_message' => '',
  'send_promotion_template' => '%message
%body
',
  'send_promotion' => 0,
  'send_promotion_pernode' => '',
);
$content[groups]  = array (
  0 => 
  array (
    'label' => 'Times and Dates',
    'settings' => 
    array (
      'collapsible' => 0,
      'collapsed' => 0,
      'multiple' => 0,
    ),
    'description' => '',
    'weight' => '-2',
    'group_name' => 'group_times_and_dates',
  ),
  1 => 
  array (
    'label' => 'Logo',
    'settings' => 
    array (
      'collapsible' => 0,
      'collapsed' => 0,
      'multiple' => 0,
    ),
    'description' => 'Add the promotion\'s logo image here. Images should be 300x300 pixels. A smaller thumbnail type image will automatically be produced as needed.',
    'weight' => '-1',
    'group_name' => 'group_logo',
  ),
);
$content[fields]  = array (
  0 => 
  array (
    'widget_type' => 'date_js',
    'label' => 'Month & Year',
    'weight' => '-7',
    'description' => 'The month and year to which this promotion applies.',
    'group' => 'group_times_and_dates',
    'prefix' => '',
    'suffix' => '',
    'required' => '1',
    'multiple' => '0',
    'granularity' => 
    array (
      'Y' => 'Y',
      'M' => 'M',
    ),
    'todate' => '',
    'input_format' => 'site-wide',
    'input_format_custom' => '',
    'output_format_date' => 'j. F Y',
    'output_format_zone' => '',
    'output_format_custom' => 'F Y',
    'output_format_date_long' => 'l, F j, Y - H:i',
    'output_format_zone_long' => '',
    'output_format_custom_long' => '',
    'output_format_date_medium' => 'D, m/d/Y - H:i',
    'output_format_zone_medium' => '',
    'output_format_custom_medium' => '',
    'output_format_date_short' => 'm/d/Y - H:i',
    'output_format_zone_short' => '',
    'output_format_custom_short' => '',
    'tz_handling' => 'none',
    'field_timezone' => 'GMT',
    'field_name' => 'field_promotion_period',
    'field_type' => 'date',
    'module' => 'date',
  ),
  1 => 
  array (
    'widget_type' => 'image',
    'label' => 'Logo Image',
    'weight' => '0',
    'max_resolution' => '300x300',
    'image_path' => 'images/promos',
    'custom_alt' => 1,
    'custom_title' => 1,
    'description' => '',
    'group' => 'group_logo',
    'prefix' => '',
    'suffix' => '',
    'required' => '0',
    'multiple' => '0',
    'field_name' => 'field_logo_image',
    'field_type' => 'image',
    'module' => 'imagefield',
  ),
);

The following is the content type "gaming_schedule" which matches the second view

$content[type]  = array (
  'name' => 'Gaming Schedule',
  'type' => 'gaming_schedule',
  'description' => 'A gaming schedule for some department of the casino.',
  'title_label' => 'Title',
  'body_label' => 'Body',
  'min_word_count' => '0',
  'help' => '',
  'node_options' => 
  array (
    'status' => true,
    'promote' => false,
    'sticky' => false,
    'revision' => false,
  ),
  'comment' => '0',
  'upload' => '0',
  'fivestar' => 0,
  'fivestar_position' => 'above',
  'image_attach' => '0',
  'scheduler' => 1,
  'old_type' => 'gaming_schedule',
  'orig_type' => '',
  'module' => 'node',
  'custom' => '1',
  'modified' => '1',
  'locked' => '0',
  'links_related_types' => '0',
  'node_images_position' => 'hide',
  'node_images_gallery_link' => '0',
  'node_images_teaser_images' => '2',
  'node_images_body_images' => '',
  'node_images_teaser_format' => 'thumbs',
  'node_images_body_format' => 'thumbs',
  'send_type' => 'gaming_schedule',
  'news_gaming_schedule_fulltext' => '',
  'news_gaming_schedule_linktext' => 'send <em>news</em>',
  'news_gaming_schedule_subject' => '<em>News</em> from RRRC Intranet Test',
  'news_gaming_schedule_message' => '',
  'news_gaming_schedule_template' => '%message
%body
',
  'news_gaming_schedule' => 0,
  'news_gaming_schedule_pernode' => '',
  'send_gaming_schedule_fulltext' => '',
  'send_gaming_schedule_linktext' => 'send to friend',
  'send_gaming_schedule_subject' => '<em>Send to Friend</em> from RRRC Intranet Test',
  'send_gaming_schedule_message' => '',
  'send_gaming_schedule_template' => '%message
%body
',
  'send_gaming_schedule' => 0,
  'send_gaming_schedule_pernode' => '',
);
$content[fields]  = array (
  0 => 
  array (
    'widget_type' => 'date_js',
    'label' => 'Month & Year',
    'weight' => '-7',
    'description' => 'The month and year to which this promotion applies.',
    'group' => false,
    'prefix' => '',
    'suffix' => '',
    'required' => '1',
    'multiple' => '0',
    'granularity' => 
    array (
      'Y' => 'Y',
      'M' => 'M',
    ),
    'todate' => '',
    'input_format' => 'site-wide',
    'input_format_custom' => '',
    'output_format_date' => 'j. F Y',
    'output_format_zone' => '',
    'output_format_custom' => 'F Y',
    'output_format_date_long' => 'l, F j, Y - H:i',
    'output_format_zone_long' => '',
    'output_format_custom_long' => '',
    'output_format_date_medium' => 'D, m/d/Y - H:i',
    'output_format_zone_medium' => '',
    'output_format_custom_medium' => '',
    'output_format_date_short' => 'm/d/Y - H:i',
    'output_format_zone_short' => '',
    'output_format_custom_short' => '',
    'tz_handling' => 'none',
    'field_timezone' => 'GMT',
    'field_name' => 'field_promotion_period',
    'field_type' => 'date',
    'module' => 'date',
  ),
);

Here is the really sketchy part. After I delete the new content type which refers to the same field I'm having problems with in the 5.x-1.3 version, the original view and content type work correctly.

So now I create a new content type with a NEW date field with the same settings. And my original content type and corresponding view are still working correctly.

So in 1.3 filtering on a month/year field doesn't work at all. Whereas in 1.2 it works fine unless you want to use the same field definition in multiple input types, which stops the first one being created from working...

karens’s picture

Status: Active » Fixed

Just committed a fix to filters and cannot replicate the problem any more so I think it is working. You can re-open if you still see the problem with the very latest code.

hyperlogos’s picture

Seems to be working, thank you, you are my heroine. This is pretty much the last issue before I can go live... besides getting off my duff and finishing up the prototype site :)

Anonymous’s picture

Status: Fixed » Closed (fixed)