My apologies if this is in the wrong section. I upgraded to Date 6.x-2.4 today, along with the core update to Drupal (6.14). I have a view which filters on a CCD date field. The filter now evaluates incorrectly. It worked fine yesterday, and this update is the only thing I've done since, so I wondered if it was a Date issue.

The filter compares two CCK date fields, linked with OR, to the current date, and returns any nodes for which that CCK date field is greater than 'now'. There are many nodes in the database for which this should evaluate to true, and I have confirmed this be getting rid of the filter and taking a look at the date that displays through the $content variable. Setting the granularity in the view filter made no difference to whether anything showed up. The two dates being compared have different granularities set in their CCK settings, but previously this was no problem (I'm testing for day, and both have at least day in their CCK settings).

The view's SQL is below. I wondered if it was the STR_TO_DATE that was the problem, due to that "T" being appended despite the granularity settings in the view filter. Anyway - again, apologies if this is in the wrong place. It just cropped up today immediately after updating to 6.x-2.4. Any help is very much appreciated!

Chris

SELECT DISTINCT(node.nid) AS nid, node.created AS node_created, term_data.weight AS term_data_weight, node_data_field_art_news_sub_deadline.field_art_news_sub_deadline_value AS node_data_field_art_news_sub_deadline_field_art_news_sub_deadline_value FROM node node  LEFT JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN content_field_art_news_dates node_data_field_art_news_dates ON node.vid = node_data_field_art_news_dates.vid LEFT JOIN content_type_art_news node_data_field_art_news_sub_deadline ON node.vid = node_data_field_art_news_sub_deadline.vid WHERE ((term_data.vid in ('2')) AND (node.status <> 0)) AND (

//The area in question...

(DATE_FORMAT(STR_TO_DATE(node_data_field_art_news_dates.field_art_news_dates_value2, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2009-09-17') 
OR 
(DATE_FORMAT(STR_TO_DATE(node_data_field_art_news_dates.field_art_news_sub_deadline_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2009-09-17')

) ORDER BY node_created DESC, term_data_weight ASC, node_data_field_art_news_sub_deadline_field_art_news_sub_deadline_value DESC

Comments

yakker’s picture

yakker commented
Title: Upgrade to Date 6.x-2.4 broke all date-filters in Vie » Date 6.x-2.4 maybe broke CCK date Views2 filter

I just went back to version 2.3, and my date filters are working fine again. But looking at the SQL for the query, it is identical, so I'm guessing the query isn't the issue. Anyone else experiencing this?

arlinsandbulte’s picture

arlinsandbulte commented

Might be related to, or a duplicate of this: #385688: Views filters & arguments not working when using a date relationship.
Not real sure if it is a duplicate, so I will just leave status alone for now.

oggsmith’s picture

oggsmith commented

Edit: - my post duplicates/relates to #580178: Views: SQL Error "Unknown column" when using fields from different content-types in a filter
Edit: - Original posters issue may be different

Same/Similar issue here - views break with Date 6.x-2.4, work fine when reverting to 2.3
The SQL from the view does change:

(DATE_FORMAT(STR_TO_DATE(node_data_field_vc3.field_vc3_value, '%Y-%m-%dT%T')

in the working version changes to

(DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_vc3_value, '%Y-%m-%dT%T')

in the broken version.

field_vc3 being the Field name.

oggsmith’s picture

oggsmith commented

Edit: - my post duplicates/relates to #580178: Views: SQL Error "Unknown column" when using fields from different content-types in a filter
Edit: - Original posters issue may be different

Changing line 413 of includes/date_api_filter_handler.inc

From:

$query_field['field']['fullname'] = $this->table_alias. '.' .$field['field_name'];

to:

 $query_field['field']['fullname'] = $query_field['field']['table_name']. '.' .$field['field_name'];

Fixes my issue.

yakker’s picture

yakker commented

Thanks oggsmith - looks like it's probably the same issue for me, but I haven't gone in to check. Will update as soon as I do. In the meantime, anyone on the project want to comment on whether this needs a patch?

sinmanteca’s picture

sinmanteca commented

Thanks, oggsmith - this worked for me, too.

pauldawg’s picture

pauldawg commented

oggsmith, #4 solved a huge issue for me as well: #592796: Date Filters not working in Views when multiple date fields are used. Thank you! Hoping to get a reviewed patch into the next build.

yakker’s picture

yakker commented

Fix works for us too - thanks again, oggsmith!

pauldawg’s picture

pauldawg commented

Update: the same fix in #4 which worked on my local installation does not work on my Development server. Not sure what the difference is, or what the error was (Internal Server Error, 500). I am on a shared hosting package so it will take me 24 hours before I can get my server logs enabled, but I thought I'd put this out there in case anyone else has experienced the same?

joostvdl’s picture

joostvdl commented
Status: Active » Needs review

Patch #4 works

Can somebody add this to the development tree?

seren10pity’s picture

seren10pity commented
jgoodwill01’s picture

jgoodwill01 commented

I don't believe this fix is working for me.

I have two CCK DATE fields if I only filter by one of the date fields I get results but if I filter for both date fields and use the "OR" option I get no results. Is there any way to fix this?

Here is my query:

SELECT node.nid AS nid,
node.title AS node_title,
node_data_field_node_images.field_node_images_fid AS node_data_field_node_images_field_node_images_fid,
node_data_field_node_images.field_node_images_list AS node_data_field_node_images_field_node_images_list,
node_data_field_node_images.field_node_images_data AS node_data_field_node_images_field_node_images_data,
node.type AS node_type,
node.vid AS node_vid,
node_data_field_branch.field_branch_value AS node_data_field_branch_field_branch_value,
node_data_field_time.field_time_value AS node_data_field_time_field_time_value,
node_data_field_time.field_time_value2 AS node_data_field_time_field_time_value2,
node_data_field_time.field_time_rrule AS node_data_field_time_field_time_rrule,
node_data_field_time.delta AS node_data_field_time_delta,
node_revisions.teaser AS node_revisions_teaser,
node_revisions.format AS node_revisions_format
FROM node node
LEFT JOIN content_field_time node_data_field_time ON node.vid = node_data_field_time.vid
LEFT JOIN content_field_class_date node_data_field_class_date ON node.vid = node_data_field_class_date.vid
LEFT JOIN content_field_node_images node_data_field_node_images ON node.vid = node_data_field_node_images.vid
LEFT JOIN content_field_branch node_data_field_branch ON node.vid = node_data_field_branch.vid
LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
WHERE ((node.type in ('calendar_program', 'computer_class', 'storytime')) AND (node.status <> 0))
AND ((DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_time.field_time_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-18000)), '%Y-%m-%d') >= '2009-12-07' AND DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_time.field_time_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-18000)), '%Y-%m-%d') <= '2009-12-28') OR (DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_time.field_class_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-18000)), '%Y-%m-%d') >= '2009-12-07' AND DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_time.field_class_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-18000)), '%Y-%m-%d') <= '2009-12-28'))

jrefano’s picture

jrefano commented

comment deleted, user error

geerlingguy’s picture

geerlingguy commented

Subscribe - still having this problem with views 2.8... except my query is only showing items for this month, rather than just this day and greater (as I have selected).

Patch idea in #4 didn't fix it for me :(

glassbyd’s picture

glassbyd commented

Subscribe: I have the same problem as geerlingguy

geerlingguy’s picture

geerlingguy commented
Version: 6.x-2.4 » 6.x-2.x-dev
Status: Needs review » Needs work

Also tried on 6.x-2.x-dev, and still not working.

petria’s picture

petria commented

Message #4 seems to fix this for me. Subscribing.

Melissamcewen’s picture

Melissamcewen commented

Message #4 works for me as well. Thank you!

tyr’s picture

tyr commented
Category: support » bug

fix from #4 worked for me, too! Thx

YK85’s picture

YK85 commented

subscribing

geerlingguy’s picture

geerlingguy commented
Status: Needs work » Active

I fixed this on my site: it turns out that, since we were using the built-in 'Calendar' view supplied by the Calendar module, the Date argument was being applied to our modified 'Upcoming Events' block (seen on the home page of archstl.org).

We removed the Date: Date (node) argument from the Upcoming Block display (only), and set the Date: Date (node) filter to show event nodes greater than or equal to today. Now the view's working great!

(cross posted from http://archstldev.com/node/469#comment-557)

As an aside, it might not be the best idea to modify views includes unless you're willing to maintain that patch on your site in the future.

Also, I'm setting this issue to 'active,' as there is currently no patch in the queue.

karens’s picture

karens commented
Status: Active » Fixed

Probably same problem as #580178: Views: SQL Error "Unknown column" when using fields from different content-types in a filter. I reverted that change.

Status: Fixed » Closed (fixed)

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