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
Comment #1
hyperlogos commentedAnyone 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.)
Comment #2
hyperlogos commentedOkay, 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:
here is my new view, which is working STILL
The following is the code from the "promotion" content type that matches the first view:
The following is the content type "gaming_schedule" which matches the second view
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...
Comment #3
karens commentedJust 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.
Comment #4
hyperlogos commentedSeems 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 :)
Comment #5
(not verified) commented